SQL Server – How to handle single quote in sqlquery string?

How to handle single quote (‘) in @sqlquery string?

You have constructed a @sqlquery string having single quote (‘), when you execute it throws an error. Every thing looks fine in the query and it still throws an error. The most probable candidate for the error would be the improper use of single quote. How to debug it?

You need to check for whether you have given an extra single quote for every ‘single quote’ which should be part of the final query.

Example:

— We want to construct following Query in the Query string

UPDATE Allotuser SET Acc5=‘hello’ WHERE userID=‘10000’

Code to construct above query.

— The code to construct Query string and Execute it

— I have tried to use different variables to construct a query string

DECLARE @sqlquery VARCHAR(1000),

@accNo VARCHAR(10),

@accString VARCHAR(10),

@userID VARCHAR(10),

@acc INT

SET @userID=‘1000’

SET @acc=1545

SET @accNo=‘5’

SET @accString = ‘Acc’+@accNo

SET @sqlquery =

‘UPDATE Allotuser SET ‘ + QUOTENAME(@accString) + ‘=”’ + @acc + ”’ WHERE  userid = ”’ + @userID + ”’ ‘

EXEC(@sqlquery)

Good Luck!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: