How to write Batch SQL query in procedure

if i want to write a procedure like below, is there some other way that, to avoid using concatenate SQL statement, i am just afraid, if the input is too long, exceed the limit of max varchar, the code will have big problem.

Thanks

CREATE PROCEDURE UPDATE_ALL_STATUS
    @IDs varchar(MAX) = null,
    @status int = null
AS
BEGIN
    IF @IDs is null
    BEGIN
        RETURN
    END

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = 'UPDATE mytable SET status = ' + @status + ' WHERE id in (' + @IDs + ')'
    EXECUTE @SQL
END

Answers


Instead of dynamic SQL (which is also vulnerable to SQL Injection Attacks) and passing in a VARCHAR(MAX), consider using Table Valued Parameters:

-- Creates the TVP type - only needed once!
CREATE TYPE IntegerTableType AS TABLE 
( Identities INT );
GO

CREATE PROCEDURE UPDATE_ALL_STATUS
    @IDs IntegerTableType READONLY,
    @status int = null
AS
BEGIN

    UPDATE mytable 
    SET status = @status
    WHERE id IN
     (SELECT Identities FROM @IDs)

END

This MSDN article shows how to call these from your .NET code.


Need Your Help

ssh-agent with passwords without spawning too many processes

linux ssh

I use ssh-agent with password-protected keys on Linux. Every time I log into a certain machine, I do this:

How do you treat the deployment of configuration files on different systems in Subversion?

svn version-control configuration

Subversion is a great way to update our web applications on our servers. With a simple svn update all changed files get... well, changed.