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.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.