SQL Can I update a table via an array: 1

[Starlitnight]

Bascially I want to UPDATE a particular column



Essentially I want to do the following:



ALTER PROCEDURE WriteStockStatus

(@asin varchar(10), @asinStockStatus varchar(15))

AS

UPDATE OnSale

SET StockStatus=@asinStockStatus

WHERE ASIN=@asin



Ok, but, I don’t want to have to call this think 16 times to update 16 rows.

Is there a way I can pass the two variables as a table or two arrays or something?

 

[mrdenny]

Nope. Table variables are not accepted as input parameters for stored procedures until SQL Server 2008. You either need to call the procedure 16 times, or have the procedure which calls this procedure create a temp table, load the temp table with the values, then have this inner procedure use the temp table to get the values.

However doing this limits the scope which this procedure can be used for not that the temp table is required to be there to use the procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Starlitnight]

Thank you for your time and explaining this to me! Thank for giving the work-around for it through a temp table, I am grateful.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com