Output variables - 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

Transact-SQL (2000)

Output variables

Author

Topic

benko
Starting Member

24 Posts

Posted - 2004-10-20 : 12:19:23

At the end of my stored proc i wanna return the total number of records so i go:

SELECT @TotalRecords = COUNT(*) FROM <<tablename>>

My question is how to i reference that on my webpage.

a_shyam41
Starting Member

9 Posts

Posted - 2004-10-21 : 02:55:34

You must add another SQL statement at the end of the stored procedure:
SELECT @TotalRecords AS TotalRecords
and refer to TotalRecords column from the recordset returned

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 05:56:04

Either in a RecordSet as a_shyam41 says, or using an OUTPUT parameter to your Sproc:


CREATE MySProc
    @MyPK      int,
    @MyName    varchar(100) OUTPUT
AS
SELECT @MyName = MyName
FROM   Mytable
WHERE  MyPK = @MyPK
GO

DECLARE @strName varchar(100)
EXEC MySProc
    @MyPK = 123,,
    @MyName = @strName OUTPUT
SELECT @strName as [Name]

Kristen

morleyhill
Starting Member

19 Posts

Posted - 2004-10-21 : 06:09:59

I disagree with a_shyam41, I don't think returning a recordset is the solution to this problem, especially as the title is 'Output Variables'. Creating a recordset has considerable overhead compared to returning a parameter and there is no need when all you want is a single value.

Presumably your stored procedure is something like-
CREATE PROCEDURE [dbo].[MyProc]
(@TotalRecords int = null output)
AS
SELECT @TotalRecords = COUNT(*) FROM <<tablename>>
RETURN

So, first make sure you've declared the @TotalRecords parameter using the 'output' keyword. I've also given the parameter a default value of 'Null' so that the calling code doesn't have to provide an initial value.

Assuming you're using 'ASP' to generate your webpage, then you would use something like this-
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "MyProc"
cmd.CommandType = adCmdStoredProc
' Create an output parameter.
cmd.Parameters.Append cmd.CreateParameter("@TotalRecords", adInteger, adParamOutput)
cmd.Execute
%>
The record count is <%Response.Write cmd.Parameters("@TotalRecords") %>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources