SQL Escaping character for quotation mark

Question

I have the following procedure:

ALTER PROCEDURE [dbo].[UpdateAllClients] @ClientIDs varchar(max) AS BEGIN SET NOCOUNT ON DECLARE @vSQL varchar(max) SET @vSQL = 'UPDATE Clients SET LastOnline=GETDATE() WHERE ClientID IN (' + @ClientIDs + ')'; EXEC(@vSQL); END

The @ClientIDs contains an array of many Client ID's, I want to update another field in the Clients table, so I want the query to look like:

UPDATE Clients SET LastOnline=GETDATE(), Status='Open' WHERE ClientID IN (@ClientIDs)

The problem is that I can put a quotation mark inside the @vSQL variable.

Any clue how can I add the ", Status='Open'" to my query?

CodeCaster · Accepted Answer · 2012-10-01 09:49:23Z

4

Use two quotes:

SET @vSQL = 'UPDATE Clients SET LastOnline=GETDATE(), Status=''Open'' WHERE ClientID IN (' + @ClientIDs + ')';

answered Oct 1, 2012 at 9:49

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2012-10-01 09:54:38Z

2

I would suggest passing the client IDs as a table valued parameter, rather than a string. Then you can just write SQL, instead of dynamic SQL.

answered Oct 1, 2012 at 9:54

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

1 Comment

Lucero Over a year ago

+1 - yes that's exactly what should be done! It is so much more efficient and it doesn't suffer from any of dynamic SQL's typical issues (that is, apart from injection and formatting issues, it also doesn't have a size restriction of 8kb for the query text size).

Collectives™ on Stack Overflow

SQL Escaping character for quotation mark

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related