April 17, 2008 at 7:37 am
Hi!
I run into a very simple issue, that I am not quite sure where to start troubleshoot. Hopefully you can provide some guidance.
In DB there is a following stored procedure that inserts a record into database and returns id:
ALTER PROCEDURE [Video].[addVideo]
@Title nvarchar(50),
@uri nvarchar(200)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID int
INSERT INTO Video.Videos
(Title, URI)
VALUES
(@Title, @uri)
SET @ID = SCOPE_IDENTITY()
SELECT @ID VideoID
END
When executing stored procedure in SQL Server Management studio I get two different results.
After EXEC ... line I see the returned identifier,
but in SELECT 'Return Value' ... the value is gone (0).
DECLARE@return_value int
EXEC@return_value = [Video].[addVideo]
@Title = N't',
@uri = N'e'
SELECT'Return Value' = @return_value
P.S.
Database is MS SQL Server 2005 Express
April 17, 2008 at 7:45 am
User RETURN
ALTER PROCEDURE [Video].[addVideo]
@Title nvarchar(50),
@uri nvarchar(200)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID int
INSERT INTO Video.Videos
(Title, URI)
VALUES
(@Title, @uri)
SET @ID = SCOPE_IDENTITY()
RETURN @ID
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 17, 2008 at 7:49 am
Jason,
Thanks a lot!
April 17, 2008 at 8:22 am
You could also set one of the parameters as OUTPUT and get it it that way.
ALTER PROCEDURE [Video].[addVideo]
@Title nvarchar(50),
@uri nvarchar(200),
@Id int OUTPUT
AS...
SET @Id = SCOPE_IDENTITY();...
Then you just call it slightly differently.
DECLARE @Id int
EXEC Video.addVideo 'some', 'thing', @ID OUTPUT
SELECT @Id
It's not better than what you're doing, just different. Plus, lots of people put the error codes, if any, into the return value as part of error trapping in code, so you might not want to rely on having that available for other purposes. Again, just an option.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2008 at 8:37 am
It would be good practice to use the return value for errors and output parameters for returning information successfully.
MS standard is to return 0 for success and anything else is some kind of error code.
However, either will work.
April 17, 2008 at 1:57 pm
This is very good that you underlined the best practices.
I have not been writing T-SQL for quite a while and did not have a time to go through some recommendations - I used options what I could remember.
Personally I consider following good practices is very important, since more experienced T-SQL procedure creator would not get the result of my stored procedure they could expect, right?
Thank you very much for the recommendation and I will follow this guideline!
Regards,
Edijs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply