Ident_current and Dynamic SQL
-
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
-
Have you tried to set a variable equalt to @@scope_identity function within your dynamic SQL? The variable should be available after the dynamic sql is performed?
Remember, it is best to avoid dynamic SQL whenever possible for performance and security.
If the phone doesn't ring...It's me.
-
Whenever you use exec, anything used or generated is only available during the lifetime of that exec as it generates a new spid to work in. It cant be accessed afterwards.
You need to use sp_executesql with return values - e.g:-
DECLARE @Exists varchar(255), @sql nvarchar(4000)
SET @sql = N'SET @Exists = ''Yes'''
EXEC sp_executesql @sql, N'@Exists varchar(50) OUTPUT', @Exists OUTPUT
PRINT @Exists
Sorry for the short answer, have a search under sp_executesql or follow some of Frank Kalis's postings for some excellent examples of using this sp. - It was my hometime 5 mins ago and i'm rushing.....
Have fun
Steve
We need men who can dream of things that never were.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply