Help with Stored procedure - return value

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

General SQL Server Forums

New to SQL Server Programming

Help with Stored procedure - return value

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-04-23 : 11:42:11

I am trying to create a stored procedure that will return the Identity column of the item inserted.

my procedure looks like this:


ALTER PROCEDURE [dbo].[spInsertProgram2] 
    @txtProgramName varchar(MAX),
	@txtProgramDisplayName varchar(MAX),
	@txtAccreditation varchar(MAX)
	
AS 
BEGIN 
 SET NOCOUNT ON 
    DECLARE @newBookID INT
    INSERT INTO tblPrograms 
    ( 
        txtProgramName,
txtProgramDisplayName,
txtAccreditation

    ) 
    VALUES 
    ( 
            @txtProgramName,
	@txtProgramDisplayName,
	@txtAccreditation

    ) 
    SELECT @newBookID = @@IDENTITY 
    SELECT newBookID = @newBookID 
END

when I execute it within SQL i get two results:

where does Return Value come from? how do i get it to only return newBookID?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 11:46:11

There are 3 ways of getting information from a stored procedure.
- result set
- output variable
- return value

See here: http://www.sqlteam.com/article/stored-procedures-returning-data

No, you're never too old to Yak'n'Roll if you're too young to die.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 11:46:56

use SET instead of SELECT.

quote:
Originally posted by xrum

I am trying to create a stored procedure that will return the Identity column of the item inserted.

my procedure looks like this:

ALTER PROCEDURE [dbo].[spInsertProgram2]
@txtProgramName varchar(MAX),
@txtProgramDisplayName varchar(MAX),
@txtAccreditation varchar(MAX)

AS
BEGIN
SET NOCOUNT ON
DECLARE @newBookID INT
INSERT INTO tblPrograms
(
txtProgramName,
txtProgramDisplayName,
txtAccreditation

)
VALUES
(
@txtProgramName,
@txtProgramDisplayName,
@txtAccreditation

)
SET @newBookID = @@IDENTITY
SELECT newBookID = @newBookID
END

when I execute it within SQL i get two results:

where does Return Value come from? how do i get it to only return newBookID?

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-23 : 11:48:04

You're declaring a return value. So you get two results, one of your SP and one of the return value which you declared to run your SP

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 11:48:06

You did not need to declarating variable such as @newBookID
Just :

SELECT @@IDENTITY AS newBookID

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-04-23 : 11:56:46

i tried:

ALTER PROCEDURE [dbo].[spInsertProgram2]
@txtProgramName varchar(MAX),
@txtProgramDisplayName varchar(MAX),
@txtAccreditation varchar(MAX)

AS
BEGIN
SET NOCOUNT ON

INSERT INTO tblPrograms
(
txtProgramName,
txtProgramDisplayName,
txtAccreditation

)
VALUES
(
@txtProgramName,
@txtProgramDisplayName,
@txtAccreditation

)
SELECT @@IDENTITY AS newBookID
END

and

ALTER PROCEDURE [dbo].[spInsertProgram2]
@txtProgramName varchar(MAX),
@txtProgramDisplayName varchar(MAX),
@txtAccreditation varchar(MAX)

AS
BEGIN
SET NOCOUNT ON
DECLARE @newBookID INT
INSERT INTO tblPrograms
(
txtProgramName,
txtProgramDisplayName,
txtAccreditation

)
VALUES
(
@txtProgramName,
@txtProgramDisplayName,
@txtAccreditation

)
SET @newBookID = @@IDENTITY
SELECT newBookID = @newBookID
END

but i still get 2 value returned. one the right indentity, and one as a 0 (as above)

When i click on "Execute Procedure" all my parameters are input, not output. where is the second value coming from?

so confused :(

also, the query created by SQL looks like this:

DECLARE @return_value int

EXEC @return_value = [dbo].[spInsertProgram2]
@txtProgramName = N'asdf',
@txtProgramDisplayName = N'asdf',
@txtAccreditation = N'asdf'

SELECT 'Return Value' = @return_value

GO

why does it put in the @return_value in?? i'm not asking for it :(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 12:06:36

quote:
Originally posted by webfred

There are 3 ways of getting information from a stored procedure.
- result set
- output variable
- return value

See here: http://www.sqlteam.com/article/stored-procedures-returning-data

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources