Turning multiple output parameters into an array

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Turning multiple output parameters into an array

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-03 : 07:47:48

Yves writes "Hi,

I have a SP with a bunch of out params that I use to compute various stats and it works well. Only problem is that I have about 50 of those params that I have to take care of im my app level code (e.g. in ASP.NET add a couple of lines per out param to just create space to hold a value). I would like to be able to read values from my app as an array, so the quetion is how do I wrap an arbitrary number of out params in some sort of array in a stored procedure ? Ideally, I would like the array to be self-descriptive, so I thought the stored proc could output a hash table or whatever loopable container where I could stuff at least the parameter name and its value, but ideally I would like to be able to stuff the type too, so I am looking for either name/value pairs or name/value/SQLType triplets. Any way to do that in a SP?

I am using SQL Server 2000 under Win 2000.

Thanx"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-03 : 07:53:56

Ummmm, how about....

A TABLE?

CREATE PROCEDURE ReturnParams AS
SET NOCOUNT ON
CREATE TABLE #Output(name varchar(50) not null, value varchar(50) not null)
INSERT INTO #Ouput(name, value) VALUES('Stat1', '125')
INSERT INTO #Ouput(name, value) VALUES('Stat2', '01/01/2004')
INSERT INTO #Ouput(name, value) VALUES('Stat3', '11.2')
...insert as many parameters as needed
SELECT * FROM #Output
DROP TABLE #Output

You'd have to have your application handle data conversion, unless all of your parameters are of one data type. In that case, change the value column to be that type, instead of varchar.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-03 : 13:56:17

Have only done ASP 3.0 lately and if you do a SELECT instead of having all the output-params then you could do something like this:

Set RS = Conn.Execute("EXEC ReturnParams")
IF NOT RS.EOF THEN 
  MyArray = RS.GetRows
  FOR i = 0 TO Ubound(MyArray, 2)
    Response.Write("<BR>")
    FOR j = 0 TO Ubound(MyArray, 1)
      Response.Write(TRIM(MyArray(j, i)) & " ")
    NEXT
  NEXT
ELSE
  Response.Write("No records found")
END IF

Maybe you'll be able to find something closely related in asp.net...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources