SP that must return parameter and Rec.Set

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)

SP that must return parameter and Rec.Set

Author

Topic

I-eye
Starting Member

1 Post

Posted - 2002-06-18 : 04:10:20

Hi all,

a collegue of mine directed me to this forum for the following problem.

I'm developing a web-application wherein I want to use the following stored procedure:

CREATE PROCEDURE cv.sp_get_subtypes
(
@typeid int,
@typename varchar(30) OUTPUT
)
AS
DECLARE @sTmp varchar(30)

SELECT @sTmp = [Name]
FROM refType
WHERE ID = @typeid

SET @typename = @sTmp

SELECT * FROM refSubType
WHERE [TypeID] = @typeid
GO

I use the following code to execute the stored procedure:

oCm.CommandText = "cv.sp_get_subtypes"
oCm.CommandType = adCmdStoredProc

oCm.Parameters.Append oCm.CreateParameter("@typeid", adInteger, adParamInput, Len(Request.Form("typeid")), Request.Form("typeid"))
oCm.Parameters.Append oCm.CreateParameter("@typename", adVarChar, adParamOutput, 30)

oRs.Open oCm.Execute

sType = oCm.Parameters("@typename")

When I want to display the variable sType it's not filled.

What am I doing wrong?

Nazim
A custom title

1408 Posts

Posted - 2002-06-18 : 04:18:16

I-eye its not a good practice starting your stored procedure name with sp . it adds up the the cpu cycles(by searching in master db first). start your sp always with set nocount on it reduces the unnecessary network traffic.

coming to your actual question. you should have posted your table schema and what you are trying to do.

Anywayz, you can reduce ur sp to

CREATE PROCEDURE cv.sp_get_subtypes
(
@typeid int
)
AS
SET NOCOUNT ON

SELECT typename,
FROM refType r
inner join
refSubType t
on r.typeid=t.typeid
on r.ID =t.typie
where r.id= @typeid
GO

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 06/18/2002 04:19:02

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-06-18 : 04:19:52

You can not have both recordset and output parameter simultaneously.
To access the output parameter first close the recordset, then access it.

Ramesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources