stored procedure syntax problem

[kingz2000]

Hi,

with this following small code, I have the problem of having a string apostrophe in my SQL Statement,aswell as the usual apostrophe for the string @str. I tried using speech marks in my SQL statement, but for some reason it sees my desired string to be filled in the columns as a column name.

CREATE procedure dbo.proc_102003
as
declare @str as varchar(5000)
begin
set @str ='INSERT INTO PBR_Tab_Premium_TT ( cls_date, rec_type )

SELECT 'XDE' AS cls_date
, 'UPR' AS rec_type'

exec(@str)
end
GO


Can anyone tell me how to solve this problem?

Thanks in advance ,

Kingsley

 

[RiverGuy]

Your stored procedure is basically saying "go try to execute this literal string against the database engine. I'm not telling you what it does, just go try to execute it." Your stored procedure should really say "go perform this specific operation against the database engine." Only use dynamic SQL as a last resort.

So, to solve your problem in this case, change your stored procedure to the following.

CREATE procedure dbo.proc_102003
as
declare @str as varchar(5000)
begin
INSERT INTO PBR_Tab_Premium_TT ( cls_date, rec_type )

SELECT     'XDE' AS cls_date
,        'UPR' AS rec_type


end
GO

 

[IanWaterman]

If Riverguy's solution does not work, replace
'XDE' and 'UPR' with vars and then declare them with the values 'XDE' and 'UPR'

Ian

 

[RiverGuy]

Well, the actual root of the error being thrown has to do with the single quotes. In T-SQL, you define a string by the contents wrapped inside single quotes. In this case, you're trying to define strings inside of another string. XDE is a string, UPR is a string, and the entire statement is a string. But SQL Server doesn't see it that way. To property define your strings-within-a-string, you need to double the single quotes around XDE and UPR. But, again, forgoing the dynamic SQL is the best plan of action.