How to write Batch SQL query in procedure

Question

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

Answer 1

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.