Create Procedure with quotes thru VB

[JeffCarlin]

We use a "update" app to update the db in a VB app. The update app applies db patches as needed, such as ALTER TABLE, CREATE TABLE, etc. We also use it to CREATE PROCEDURE. This all works well until our new procedure contains quotes to handle varying params, such as:

CREATE PROCEDURE tlcselection_findtemplatename

@Parm1 varchar(50),   -- Table Name
@Parm2 varchar(11),
@Parm3 varchar(2)

AS

DECLARE @stg varchar(500)

SET @stg = 

"SELECT " + @Parm1 + ".strTemplateName " +
"FROM " + @Parm1 + ", tblTLC_Header " +
"WHERE " + @Parm1 + ".lngTemplateID = tblTLC_Header.lngOrigTemplateID " +
"AND tblTLC_Header.strPatSSN = '" + @Parm2 + "' " +
"AND tblTLC_Header.strPatTreatNum = '" + @Parm3 + "'"

EXECUTE (@stg)

GO

This SP will work if we just create it in the Enterprise Manager, but if we try it thru VB, it won't get created. We just create a long string by concatenating a bunch of smaller strings. Each line you see above is a small string. Once we concatenate them and insert a CHR(13) after each line, we attempt to EXECUTE and that's where the failure occurs.

Thanks for any help you can give.

 

[TerryStover]

I generally put char(39) (single quote) into a local variable and concatenate it into the query string. You can also use quotename(string, char(39)). Quotename bit be a couple of times because it is limited to 128 characters.

 

[Zathras]

Take a look at thread68-436199 for a couple of ways to handle the quote character.

 

[tlbroadbent]

Does the creation of the SP fail or the execution of the SP after creating it? WHat is the actual error message you get? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[JeffCarlin]

It's the creation that fails. If I were to manually create the SP, it works fine. The error message is a syntax error. I'm pretty sure it has to do with the "plus" characters or the quotes. I've played around a lot, but can't seem to find the answer. The code shown above is the actual string that is being EXECuted by VB against the db. I captured it and used the same string to manually create the SP without problem.

 

[tlbroadbent]

You can simplify the code a bit by using aliases. You might also try eliminating the double quotes as they can cause problems if Quoted_Identifier is ON. Try the following.

CREATE PROCEDURE tlcselection_findtemplatename

@Parm1 varchar(50), -- Table Name
@Parm2 varchar(11),
@Parm3 varchar(2)

AS

DECLARE @stg varchar(500)

SET @stg =

'SELECT t.strTemplateName ' +
'FROM ' + @Parm1 + ' t JOIN tblTLC_Header h ' +
'ON t.lngTemplateID = h.lngOrigTemplateID ' +
'WHERE h.strPatSSN = ''' + @Parm2 + ''' ' +
'AND h.strPatTreatNum = ''' + @Parm3 + ''''

EXECUTE (@stg)

GO Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.