Sproc Output Parameters - SQL Server Forums

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)

Sproc Output Parameters

Author

Topic

BillyWhizz
Starting Member

6 Posts

Posted - 2002-02-07 : 05:54:57

I know how to output individual values as output parameters from a sproc, but I want to do it for all the values of a select statement. It will be easier if I show you ...

SELECT MI.serviceDescription,
xxx,
xxx,
FROM tblMSISDN_Instance MI
....
WHERE MI.MSISDN_InstanceID = @ID

I want to pass all the values from the select out as output parameters, cos I know there can only ever be one row for each, and using output parameters is more efficient than using a recordset in ASP. So, being anal, I'd like to know how to do this!
Thanks
Billy

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-07 : 06:03:42

Good idea - RecordSets are best avoided if at all possible.
You just need to declare as many output parameters as you want and then select them out..

*********
CREATE PROCEDURE select_details

@table_id int,
@title varchar(250) OUTPUT,
@short_description varchar(1000) OUTPUT,
@full_description varchar(4000) OUTPUT,
@dates varchar(250) OUTPUT,
@image varchar(250) OUTPUT,
@url varchar(250) OUTPUT

AS

SELECT
@title = title,
@short_description = short_description,
@full_description = full_description,
@dates = dates,
@image = image,
@url = url

FROM
table
WHERE
table_id = @table_id
**********

.. then use the ASP (or ASP.NET ;) ) Command Object to fire the stored procedure and capture the output parameters..

***********
Set objComm = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = objConn
objComm.CommandText = "select_details"
objComm.CommandType = adCmdStoredProc

objComm.Parameters.Append objComm.CreateParameter("@table_id", adInteger, adParamInput, 10, Request.QueryString("id"))

objComm.Parameters.Append objComm.CreateParameter("@title", adVarChar, adParamOutput, 250)

objComm.Parameters.Append objComm.CreateParameter("@short_description", adVarChar, adParamOutput, 1000)

objComm.Parameters.Append objComm.CreateParameter("@full_description", adVarChar, adParamOutput, 4000)

objComm.Parameters.Append objComm.CreateParameter("@dates", adVarChar, adParamOutput, 250)

objComm.Parameters.Append objComm.CreateParameter("@image", adVarChar, adParamOutput, 250)

objComm.Parameters.Append objComm.CreateParameter("@url", adVarChar, adParamOutput, 250)

objComm.Execute
************

Jack

BillyWhizz
Starting Member

6 Posts

Posted - 2002-02-07 : 06:38:18

Thanks, but I had already tried that and failed, it is not allowed. I get the following;

Error 141: A select statement that assigns a value to a variable must not be combined with data-retrieval operations

Any other ideas?

Billy

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-07 : 07:19:00

Are you sure you have the syntax of the select statement right?
It should be
SELECT @variable1 = columnname1, @variable2 = columnname2 FROM table

rather than
SELECT @variable1 = columnname1, columname2 FROM table

BillyWhizz
Starting Member

6 Posts

Posted - 2002-02-07 : 08:04:18

genius - thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources