Passing an array of strings to a Stored Procedure

Well, Imanaged to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROC [dbo].[gv_sp_UpdatePOs]

(

@.IDListvarchar(500),

@.ReorderLevelint,

@.ProductNamenvarchar(30)

)

AS

BEGIN

SETNOCOUNTON

EXEC('Update dbo.Products

SET ReorderLevel = ('+ @.ReorderLevel+') ,ProductName = ('''+ @.ProductName+''')

WHERE ProductID IN ('+ @.IDList+')')

END

-------

THis works fine inside Sql Server 2005 Query analyser.

But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.

Any help would be appreciated

And can someone convert it to support XML instead of list of strings. thanks.

Hello my friend,

It would be better to do the following. First, run the following SQL: -

CREATE FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(800)

SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN

END

Test this function via the following: -

SELECT Item FROM dbo.StringArrayIntoTable('red,blue,yellow', ',')
SELECT Item FROM dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

Now change your procedure to the following: -

ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@.IDList varchar(500),
@.ReorderLevel int,
@.ProductName nvarchar(30)
)
AS

BEGIN

SET NOCOUNT ON

UPDATE dbo.Products SET
ReorderLevel = @.ReorderLevel,
ProductName = @.ProductName

WHERE ProductID IN
(
SELECT Item FROM dbo.StringArrayIntoTable(@.IDList, ',')
)

END

Kind regards

Scotty

|||

USE [Northwind]GO/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROC [dbo].[gv_sp_UpdatePOs](@.IDListvarchar(500),@.ReorderLevelint,@.ProductNamenvarchar(30) )ASBEGINSET NOCOUNT ON EXEC('Update dbo.ProductsSET ReorderLevel = (' +CAST( @.ReorderLevelas varchar(20) ) +') ,ProductName = (''' + @.ProductName +''')WHERE ProductID IN (' + @.IDList +')')END

Hello,
Try this.|||

Scotty, nice trick thanks.

Hasan, thanks. It is working now with casting.