February 1, 2010 at 2:39 pm
I have created a stored procedure as follows:
CREATE PROCEDURE [dbo].[InsertVersion]
-- Add the parameters for the stored procedure here
@Version_Name VARCHAR(50),
@Starting_FC_Month VARCHAR(3),
@Start_Date_Criteria VARCHAR(10),
@End_Date_Criteria VARCHAR(10),
@Created_By VARCHAR(100),
@Shared INT,
@Version_ID INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO tblVersions
(name, date_created, starting_fc_month, start_date_criteria, end_date_criteria, created_by, shared)
VALUES
(@Version_Name, GETDATE(), UPPER(@Starting_FC_Month), CAST(@Start_Date_Criteria AS DATETIME),
CAST(@End_Date_Criteria AS DATETIME), @Created_By, @Shared)
SET @Version_ID = @@IDENTITY
RETURN @Version_ID
END
When I test the stored procedure using the following it returns NULL instead of the generated id?
declare @id int
exec InsertVersion 'EE','JUL','2001-01-01','2001-02-01','dw134121',1,@id
select @id
February 1, 2010 at 2:44 pm
Try this:
exec InsertVersion 'EE','JUL','2001-01-01','2001-02-01','dw134121',1, @id OUTPUT
February 1, 2010 at 2:47 pm
As simple as that! Thanks for your help!
February 1, 2010 at 2:50 pm
David-155102 (2/1/2010)
As simple as that! Thanks for your help!
All I did was verify the EXECUTE statement syntax in Books Online (Shift {f1} in SSMS).
Glad I could help.
February 1, 2010 at 5:25 pm
I am assuming that there is an Identity Column in the tblVersion table 🙂
February 1, 2010 at 5:40 pm
Didn't think of this earlier, but you really shouldn't be using @@IDENTITY either. You should be using SCOPE_IDENTITY().
February 1, 2010 at 6:24 pm
Lynn Pettis (2/1/2010)
Didn't think of this earlier, but you really shouldn't be using @@IDENTITY either. You should be using SCOPE_IDENTITY().
Agreed to avoiding @@identity- although if you are in 2005 or later, I would use the OUTPUT clause instead. It seems to avoid ALL of the vulnerabilities with the three more "traditional" options. It's a bit more work to get there, but it seems to be bullet-proof.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 1, 2010 at 6:34 pm
Matt Miller (#4) (2/1/2010)
Lynn Pettis (2/1/2010)
Didn't think of this earlier, but you really shouldn't be using @@IDENTITY either. You should be using SCOPE_IDENTITY().Agreed to avoiding @@identity- although if you are in 2005 or later, I would use the OUTPUT clause instead. It seems to avoid ALL of the vulnerabilities with the three more "traditional" options. It's a bit more work to get there, but it seems to be bullet-proof.
And using temp tables or table variables (depends on your needs) you can actually capture the identity value for multiple rows inserted at once.
February 2, 2010 at 12:18 am
Hi Matt,
Can you give me an example please?
Thanks
February 2, 2010 at 1:00 am
Assuming the identity column in your table is called version_id, your SP will look like
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO tblVersions
(name, date_created, starting_fc_month, start_date_criteria, end_date_criteria, created_by, shared)
OUTPUT INSERTED.version_id
VALUES
(@Version_Name, GETDATE(), UPPER(@Starting_FC_Month), CAST(@Start_Date_Criteria AS DATETIME),
CAST(@End_Date_Criteria AS DATETIME), @Created_By, @Shared)
END
You can also define a table variable and do something like
OUTPUT col1, col2, col3 INTO @t
Check BOL for more information.
February 2, 2010 at 1:33 am
Thanks Willem, I'll try that.
February 2, 2010 at 4:22 am
Matt Miller (#4) (2/1/2010)
Lynn Pettis (2/1/2010)
Didn't think of this earlier, but you really shouldn't be using @@IDENTITY either. You should be using SCOPE_IDENTITY().Agreed to avoiding @@identity- although if you are in 2005 or later, I would use the OUTPUT clause instead. It seems to avoid ALL of the vulnerabilities with the three more "traditional" options. It's a bit more work to get there, but it seems to be bullet-proof.
Agreed. There was a bug with SCOPE_IDENTITY() and parallel plans. The OUTPUT clause does seem reliable - though more work, for sure.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply