Using a var. in a SP to return more than one value

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

Transact-SQL (2000)

Using a var. in a SP to return more than one value

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-20 : 05:26:01

I have a Stored Procedure that uses the ‘Select’ to assign the found value to a variable. How can I do it so that the SP can return so many values as it finds?. In my current SP always returns the variable ‘@Certificates’ with one value on it.


USE market
GO
CREATE PROCEDURE Sel_Certificates
@Region smallint, @Certificates varchar(50) output
As

Select @Certificates = Certificate_name
From All_Certificates
Where Region_num = @Region

I need to use the variable ‘@Certificates’ in my application, but what happens if for a specified region_num the table contain more than one ‘Certificate_name’ value?. How can I do it so that the SP can return the other ‘Certificate_name’ values in other variables?

Thank you,
Cesar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-20 : 07:02:59

pu the select result into a #temp table, then build a csv string from results and output the csv string.
or save the select result into a global ##temp table and use them anywhere...

it's up to you!

Go with the flow & have fun! Else fight the flow :)

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-20 : 07:20:14

Hi,
I don' t understand how can I do this. Is not there a more easy way to do it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-20 : 07:54:49

well if you now the max number of possible Certificate_name for Region then you could just make that many output variables
and set each one to one value.

altough if you're using .net there is a neat thing you can do.
fill your dataset with data adapter
DataAdapter.Fill(ds);

and if you have two selects in your sproc there will be two DataTables in your DataSet with data from both selects.
so you could use the 2nd table (that has multiple Certificate_name) for whatever you want... :))

Go with the flow & have fun! Else fight the flow :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-20 : 10:43:02

????

just have your stored proc return what you want:


Select @Certificates = Certificate_name
From All_Certificates
Where Region_num = @Region

that will return a recordset of all the certificate names. no need to assign/return a variable. stored procs can return entire recordsets of data, that's what they were designed to do.

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-20 : 11:15:16

In this case I need to use the variable ‘@Certificates’ in my application. This variable, indeed, contain a path to an image, and through an ASP.NET subroutine I pass this path (or more) to a javascript function so that these images are available in the browser memory. Thus, when the user makes an specific action the image or images appears in the web page.

If I use the typical 'Select field1, field2, ..' I don' t know how to pass these values from ASP.NET to the javascript function, instead, with variables it's easy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-20 : 13:05:48

in ADO.NET, you just open the recordset returned by the stored proc. then loop through the rows one at a time, and the field "Certificate_Name" will have what you need. just assign the value in that field to a variable (in VB or C# or whatever you are using) if you need to.

Does this make sense? if you want multiple values returned, this is the way to do it. that's what recordsets are designed for.

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-20 : 13:22:19

Ok I see.. I am going to test what you mean later.
Thank you,
Cesar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources