Apostrophes in field

If I am using a variable to retrieve a field value and it has an apostrophe in the field, I am getting an error

DECLARE @.C_CenterName nvarchar(255)

...

FETCH NEXT FROM curJCD INTO @.C_CenterName,@.Address,@.City,@.State,@.Zip

...

SET @.SQL='INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers]) VALUES(' + CAST(@.NextClientID AS NVARCHAR(10)) + ',' + @.C_CenterName + ',' + @.Address + ',' + @.City + ',' + @.State + ',' + @.Zip + ',1,1);'

@.C_CenterName sometimes has apostrophe

ERROR BELOW:

Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark before the character string

How do I get around that if the fields that have the apostrophe are random?

Jason

Hi Jason

You can use the QUOTENAME function, like so:

SET @.SQL='INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers]) VALUES(' + CAST(@.NextClientID AS NVARCHAR(10)) + ',' + QUOTENAME(@.C_CenterName, '''') + ',' + QUOTENAME(@.Address, '''') + ',' + QUOTENAME(@.City, '''') + ',' + QUOTENAME(@.State, '''') + ',' + QUOTENAME(@.Zip, '''') + ',1,1);'

This example assumes that your variables are not already delimited with single quotes. I should have spotted this when you posted your previous question.

Again, here's a link that might help:

http://msdn2.microsoft.com/en-us/library/ms176114.aspx

Chris

|||

Just taking a step back for a moment, I'm not sure why you're using dynamic SQL at all in this code.

You could, in fact, should, just use:

INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers])

VALUES(@.NextClientID, @.C_CenterName, @.Address, @.City, @.State, @.Zip,1,1)

Chris

|||

THX. I think i got side tracked by my other problem but this worked great.

Jason