Procedure Not Returning Openable Recordset

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)

Procedure Not Returning Openable Recordset

Author

Topic

Crowandazi
Starting Member

9 Posts

Posted - 2005-08-02 : 19:22:06

I'm having an issue where a stored procedure I am calling in one of my .asp pages is not returning an openable recordset. This is an abbreviated example of the code calling the procedure and recordset:

Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = DataConn
.CommandText = "spAddNewMember"
.Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 50, sName)
.CommandType = adCmdStoredProc
End With
Set rs = oCmd.Execute
Set oCmd = Nothing
MemberID = rs("MemberID")
set rs = nothing

And here's the procedure:

CREATE PROCEDURE spAddNewMember
@Name varchar(50)
AS
INSERT INTO Member(Name)
VALUES (@Name)
SELECT @@IDENTITY AS 'MemberID'
Set Nocount off
GO

In the example above, my page errors out when it tries to assign the variable "MemberID" to the contents of the recordset the procedure was supposed to return, presumably because the recordset is unopenable.

According to this topic (read Kevin Snow's response at the end):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16354

...the issue is related to changes in ODBC methodology. I believe this is true, because the procedure above works on my old server and not the new one. However, I couldn't figure out the parameters to make this work (I'm not a SQL expert by any means), and since this was an older post I thought I'd open the issue again for anyone who can illuminate me.

Thanks!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-08-02 : 20:18:38

It's much better if you return the ID as an output parameter:

CREATE PROCEDURE spAddNewMember
     @Name varchar(50),
     @MemberID int OUT
AS
INSERT INTO Member(Name)
VALUES (@Name)

SELECT @MemberID = @@IDENTITY
GO

Also, if you're using ADO 2.5 or above, you don't need to explicitly declare your stored procedure parameters:

Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
    .ActiveConnection = DataConn
    .CommandText = "spAddNewMember"
    .CommandType = adCmdStoredProc
    .Parameters.("@Name").Value = sName
    .Execute
    MemberID = .Parameters("@MemberID").Value
End With

It will perform better because SQL Server doesn't need to create a cursor and ADO doesn't have to deal with one.

HTH,

Tim

Crowandazi
Starting Member

9 Posts

Posted - 2005-08-02 : 21:19:47

So the variable is assigned within the command, then? I don't even need to refer to a recordset? If so, that seems much easier and more obvious. Much thanks!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-08-02 : 21:21:22

Yep. As a general rule, only return recordsets when you absolutely need them. Much better for all concerned if you use output parameters.

Tim

Crowandazi
Starting Member

9 Posts

Posted - 2005-08-06 : 06:56:41

Sorry, I thought that would work, but it's still not quite right. It is giving me the message:

Procedure 'spAddNewMamber' expects parameter '@MemberID', which was not supplied.

...which, since it is a returned value, I didn't think it needed. Thoughts?

Crowandazi
Starting Member

9 Posts

Posted - 2005-08-06 : 07:07:56

Actually, It appears that I do not have ADO 2.5 or above, because it IS expecting explicitly defined parameters, so do you have any suggestions for items that might work with earlier ADO versions?

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-06 : 07:39:55

by the way, you should use

SELECT @@SCOPE_IDENTITY AS 'MemberID'

instead of

SELECT @@IDENTITY AS 'MemberID'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources