retrun identity from stored procedure to asp

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)

retrun identity from stored procedure to asp

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 03:51:46

I am trying to get an identity returned from my stored procedure

i am trying within my stored procedure

SET NOCOUNT ON
insert into users(firstname,lastname)values('xxx','yuyy')
SELECT @@IDENTITY as MyIdentity

RETURN @MyIdentity

SET NOCOUNT OFF

when i run the exec spinertcust from query analyzer i just get
The command(s) completed successfully.
and i don't see the id....
Asp is also not bringing the id.

what am i doing wrong?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 03:59:57

You need to declare @MyIdentity as an Output parameter and specify that it is an Output parameter when calling the sproc - look it up in BOL

Duane.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 04:06:56

i didn't post that part because there is much more to this sp but I did

What else could it be?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 04:09:14

Oh looking at it now I see SELECT @@IDENTITY as MyIdentity should be SELECT @@IDENTITY as @MyIdentity - you forgot the @ at the front :)

Duane.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 04:13:52

if i do that on this line i get an error

SELECT @@IDENTITY as @MyIdentity

incorrect syntax near @MyIdentity

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 04:14:57

select @MyIdentity = @@Identity

Duane.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 04:18:50

still when runnign the exec sp in query analyzer i get
The command(s) completed successfully.

I really want the value in asp but I assume I should also see it in query analyzer shouldn't i?
what else could I be doing wrong??

magesh
Starting Member

23 Posts

Posted - 2005-09-15 : 04:22:09

Hi all,

------
SET NOCOUNT ON
insert into users(firstname,lastname)values('xxx','yuyy')
SELECT @MyIdentity = @@IDENTITY

RETURN @MyIdentity

SET NOCOUNT OFF

------

hope this wil work

Magesh

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 04:23:20

post the code that you are using to call the sproc

it should be something like:
DECLARE @MyIdentity INT
EXEC spinertcust @MyIdentity OUTPUT

SELECT @MyIdentity

Duane.

magesh
Starting Member

23 Posts

Posted - 2005-09-15 : 04:26:33

Hi,

you can use @MyIdentity as output parameter
and u can use it to return value to ur asp appln.

to see in query analyser just print @MyIdentlty.

Magesh

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 04:28:24

magesh -- yours didn't work either...
code is as above... but with a few if statements before the sql changing variables which should make no difference.

i am calling it as exec spregister(.....all my parameters here)
is that the wrong way to call it?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 04:34:11

Why don't you post all the code you are using when you call the proc?

DECLARE @MyIdentity INT
EXEC spregister @Param1, @Param2, @Param3, @MyIdentity OUTPUT

SELECT @MyIdentity

That will return it to Query Analyzer

Duane.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 04:37:18

sot eh declare @MYIdentity should be moved to the sql and not the storedprocedure??

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 04:40:20

No it must be in Both! it is returned as a variable and so to see the value in the variable you have to select or print it.

Duane.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 04:44:57

so now I added it to teh parameters in teh sp as @MyIdentity2 int OUTPUT
and i am doing the exec as you said..

but I am getting an error

Must pass parameter number 32 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

I passed it as @MyIdentity2 OUTPUT
and in teh sp declared it with the input variables (before the as) and not after.

what value do I give it... I passed it as

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-15 : 04:49:07

OK, the only way for me to look at it properly is if you post the CREATE PROCEDURE code (up till the end of the parameters at least) and the code that you are using to call it. Other than that it is very difficult to see what is going on.

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 04:58:22

You need to have Output parameter in your stored procedure and using Command object in ASP you have to get that parameter value

Refer these
http://www.asp101.com/articles/wrox/asp30/26100903.asp
http://www.mindsdoor.net/aSP/DBAccess.inc.html

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 05:25:56

so if I get it working in query analyzer -- i can't just copy that statement to asp -- I have to actually use the command object -- or could I just execute the sql statement while opening a recordset?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 05:28:50

I think you need to use Command object in ASP to get the output value returned from stored Procedure

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 05:45:35

I finally got my statement working in sql --
it has 35 parameters adn would be a pain to reset up in sql server ---
why if say
set rs=objconn.execute(sql)
being the exact sql working in query analyzer, should it not work??

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 07:02:03

Why don't you just SELECT it in your SProc, and the it will be in the ResultSet in ASP


CREATE PROCEDURE MySproc
AS
SET NOCOUNT ON
DECLARE @MyIdentity int
insert into users(firstname,lastname)values('xxx','yuyy')
SELECT @MyIdentity = @@IDENTITY Scope_Identity()

SELECT @MyIdentity AS [MyIdentity]

RETURN

SET NOCOUNT OFF

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources