October 8, 2010 at 12:54 am
somethg goes wrong on following code where i m trying to get identity value from sequence_emp table... as output of this procedure i m getting null only... insert cmd is not working... any help?
IF OBJECT_ID('sequence_emp') IS NOT NULL
DROP TABLE sequence_emp
GO
CREATE TABLE sequence_emp
(
sequenceid INT IDENTITY(1,1) NOT NULL
)
================================================================
IF OBJECT_ID('get_next_sequence') IS NOT NULL
DROP PROCEDURE get_next_sequence
GO
CREATE PROCEDURE get_next_sequence @pvc_seq_name NVARCHAR(40),@seqno INT OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN tran1
DECLARE @sql nvarchar(4000)
select @sql='INSERT INTO ' + @pvc_seq_name + ' DEFAULT VALUES'
exec sp_executesql @sql
SELECT @seqno = SCOPE_IDENTITY()
ROLLBACK
GO
DECLARE @TAB_NAME NVARCHAR(40)
declare @seqno int
SET @TAB_NAME = 'sequence_emp'
exec dbo.get_next_sequence @TAB_NAME,@seqno output
select @seqno
October 8, 2010 at 1:26 am
mail4sha (10/8/2010)
somethg goes wrong on following code where i m trying to get identity value from sequence_emp table... as output of this procedure i m getting null only... insert cmd is not working... any help?IF OBJECT_ID('sequence_emp') IS NOT NULL
DROP TABLE sequence_emp
GO
CREATE TABLE sequence_emp
(
sequenceid INT IDENTITY(1,1) NOT NULL
)
================================================================
IF OBJECT_ID('get_next_sequence') IS NOT NULL
DROP PROCEDURE get_next_sequence
GO
CREATE PROCEDURE get_next_sequence @pvc_seq_name NVARCHAR(40),@seqno INT OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN tran1
DECLARE @sql nvarchar(4000)
select @sql='INSERT INTO ' + @pvc_seq_name + ' DEFAULT VALUES'
exec sp_executesql @sql
SELECT @seqno = SCOPE_IDENTITY()
ROLLBACK
GO
DECLARE @TAB_NAME NVARCHAR(40)
declare @seqno int
SET @TAB_NAME = 'sequence_emp'
exec dbo.get_next_sequence @TAB_NAME,@seqno output
select @seqno
I dont think its possible to use SCOPE_IDENTITY() in this case. Because the Exec will not be in the same scope/session as the rest of the code. And since the new identity value isnt created in this Scope... you get NULL.
Closest i think is to use @@IDENTITY instead in this case. Its atleast limited to the session. But there are risks to that as well. So you will have to test to see if it holds up in your environment.
Best would offcourse be to not use the exec at all. If its a limited nr of tables that you can call this SP with then some
if @pvc_seq_name = 'sequence_emp' INSERT INTO sequence_emp DEFAULT VALUES
if @pvc_seq_name = 'some_other_table' INSERT INTO some_other_table DEFAULT VALUES
Would be better. Better yet would be not to have a generic SP at all (but then thats a personal opinion)
/T
October 8, 2010 at 1:46 am
Great!!!!... thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply