Can I send array of parameter to store procedure?

I have User table, it has UserId uniqueidentifier, Name varchar and IsActive bit.

I want to create store procedure to set IsActive to false for many user, for example, if I want to deactive 2 user开发者_如何学Pythons, I want to send Guid of those users to store procedure (prefer as array). I want to know how can I do it?

P.S. I'm working on Microsoft SQL Azure

Along the same lines than Elian, take a look at XML parameters. Generally speaking you should have a cleaner/safer implementation using xml than parsing a list of strings. Click here for a code example

Here is a solution I used a while ago and that was working fine.

Send the list of guid you want to deactive merged into a comma separated string to the sp. Then in the sp, you first convert this string into a table thanks to a table-valued function.

Here is a sample with bigint, but you can easily modify it so that it works with guid

Step 1 : the table-valued function

CREATE FUNCTION [dbo].[BigIntListToTable] (
   @list VARCHAR(max)
)
RETURNS 
   @tbl TABLE 
   (
      nval BIGINT NOT NULL
   ) AS
BEGIN
   DECLARE @nPos INT
   DECLARE @nNextPos INT
   DECLARE @nLen INT
   SELECT @nPos = 0, @nNextPos = 1
   WHILE @nNextPos > 0
   BEGIN
      SELECT @nNextPos = CHARINDEX(',', @list, @nPos + 1)
      SELECT @nLen = CASE WHEN @nNextPos > 0
                          THEN @nNextPos
                          ELSE LEN(@list) + 1
                     END - @nPos - 1
      INSERT @tbl (nval)
         VALUES (CONVERT(BIGINT, SUBSTRING(@list, @nPos + 1, @nLen)))
      SELECT @nPos = @nNextPos
   END
  RETURN
END

Step 2 : the stored proc

CREATE PROCEDURE [dbo].[spMySP]
   @IdList VARCHAR(max)
AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0
   UPDATE dbo.YourTable
   SET isActive = 0
   FROM dbo.YourTable
   INNER JOIN dbo.BigIntListToTable(@IdList) l 
   ON dbo.YourTable.id = l.nval
END