August 28, 2001 at 10:41 am
Hello,
Could some one please tell how to get the current Identity value from a table using Dynamic sql.
My code is listed below.
declare @qry nvarchar(500)
declare @qry1 nvarchar(500)
declare @tabName nvarchar(50)
declare @x int
set @tabName='tblCenters'
set @qry = N'select @ident=ident_current('''+@tabName+''')'
set @qry1 = N'@ident int'
exec sp_executesql @qry, @qry1,@x
print @x
It compiles without any error, But I am Unable to get the current Identity value
Plese help me
Thanks in advance
Kiran
August 28, 2001 at 11:01 am
I'm assuming you would be passing in @tabName so, you will need to change this some however, a working variation is below.
declare @qry nvarchar(500)
declare @qry1 nvarchar(500)
declare @tabName nvarchar(50)
declare @x int
set @tabName='db_symbol'
set @qry = ('select ident_current('''+@tabName+''')')
--set @qry1 = ('@ident int')
exec sp_executesql @qry--, @qry1, @x
--print @x
Not sure what was going on with the other stuff that I commented out. If there are other uses for that we can work that back in.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2001 at 1:05 pm
Hello David,
It's Ok, but, suppose I want perform some computation on the returned value, I have to store the value in a variable. In my case, I want this value to insert records into child table, since the Identity field having Primary Key. So I need the curent Identity value in a variable
Many many thanx
Kiran
August 29, 2001 at 5:21 am
This should work
declare @qry nvarchar(500)
declare @qry1 nvarchar(500)
declare @tabName nvarchar(50)
declare @x int
set @tabName= 'tblCenters'
set @qry = ('select @ident = ident_current('''+@tabName+''')')
set @qry1 = ('@ident int output')
exec sp_executesql @qry , @qry1 , @x output
print @x
August 29, 2001 at 5:51 pm
Kiran,
Could you explain a bit about why you want to do this? You're not going to see much different in performance doing this in a proc vs just executing dynamic sql using ADO. If anything, I'd consider the ADO technique cleaner. Just curious.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply