Parameters collection 1

[bra1niac]

Can anyone tell me if there is a way to pass an array of parameters into a sproc?

I can envision passing a string containing named value pairs, assembling a query in the sproc and executing the assembled string. But I'm afraid doing so will result in a loss of performance as the sproc will not cache correctly because that string would be different each time.

Basically, I need a way of passing in an unknown number of parameters and have the ability to handle that collection once it's in the sproc.

Our business requires that this take place in the database. I don't really have the ability to manage my results from code. In other words, I need a clean datasource, it can't be manipulated after the results are returned.

"It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[hmckillop]

Have you tried OPENXML, and passing the data in XML.
It would require a small bit of formatting on the application side but would then be able to be queried directly in Stored proc.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLDenis]

Step 1 create pivot table in your DB, do this only once
you probably can populate with a lot less than 1000

CREATE TABLE NumberIDPivot (NumberIDID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberIDPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END

-------------------------------------
Step 2
this how you use it

declare @Values varchar(50)
select @Values ='abc,def,fff,ggg,hhh,ttt,yyy'

select substring(',' + @Values + ',',
NumberID + 1,
charindex(',', ',' + @Values + ',', NumberID + 1) - NumberID -1)as Value
into #GroupNumTable
from NumberPivot with (nolock)
where NumberID <= len(',' + @Values + ',') - 1
and substring(',' + @Values + ',', NumberID, 1) = ','

select * from #GroupNumTable

you can join the #GroupNumTable on the other tables that you need in your proc

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[bra1niac]

Thanks for the reply, I'm working on implementing now...

&quot;It's easier to ask forgiveness than it is to get permission.&quot; - Rear Admiral Dr. Grace Hopper

 

[SQLBill]

CREATE TABLE NumberIDPivot (NumberIDID INT PRIMARY KEY)

I suspect SQLDenis meant: (NumberID INT PRIMARY KEY) - one ID not two (IDID).

-SQLBill


Posting advice: FAQ481-4875