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
 Old Forums
 CLOSED - General SQL Server
 stored procedure and session variable

Author  Topic 

joemama
Posting Yak Master

113 Posts

Posted - 2004-12-16 : 13:30:41
the following stored procedure inserts and retrieves @@identity just fine..can anyone tell me how i can get it to output the @@identity into a session variable as well??

here is the code


CREATE PROCEDURE SP_insert_get_ID
(
@CustomID varchar(50),
@Gender varchar(50),
@RefDate varchar(50),
@PDate varchar(50),
@Status varchar(50),
@Interviewer varchar(50),
@bladcancer varchar(50),
@bladcancerdt varchar(250),
@kmember varchar(50),
@bladcancercontrol varchar(50),
@bladcancercontroldt varchar(50),
@outputid int
)

AS



INSERT INTO FOLLOWUP (CustomID,Gender,RefDate,ParticipantDateofDiag,Status,Interviewer,bladcancer,bladcancerdt,kmember,bladcancercontrol,bladcancercontroldt)
VALUES (@CustomID,@Gender,@RefDate,@PDate,@Status,@Interviewer,@bladcancer,@bladcancerdt,@kmember,@bladcancercontrol,@bladcancercontroldt)
select @@identity as ID
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 13:42:01
Best to use SCOPE_IDENTITY() instead of @@IDENTITY. Also, check out SQL Server Books Online for how to create stored procedures.

CREATE PROC SomeProc
(@var1 int, @var2 OUTPUT)
AS

INSERT INTO...
VALUES...

SET @var2 = SCOPE_IDENTITY()

RETURN
GO

Tara
Go to Top of Page

jhermiz

3564 Posts

Posted - 2004-12-16 : 13:42:24
Session("ID") = yourOutputParameter
Go to Top of Page

joemama
Posting Yak Master

113 Posts

Posted - 2004-12-16 : 18:21:47
is "Session("ID") = yourOutputParameter" in the stored proc? or on web page? i want it in stored proc



quote:
Originally posted by jhermiz

Session("ID") = yourOutputParameter

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 19:06:40
For stored procedure, please look at my post.

Tara
Go to Top of Page
   

- Advertisement -