CODEWITHSUNDEEP
c#sqlsql-server
m0fo
m0fo

Reputation: 2189

SQL Escaping character for quotation mark

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?

Upvotes: 0

Views: 173

Answers (2)

podiluska
podiluska

Reputation: 51494

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.

Upvotes: 2

CodeCaster
CodeCaster

Reputation: 151588

Use two quotes:

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

Upvotes: 4

Related Questions