December 24, 2008 at 4:25 am
In a stored procedure I want to set the table identity of a column on/off dynamically.
Example
- SET IDENTITY_INSERT @TABLE_NAME OFF
where @TABLE_NAME is a parameter to a stored procedure.
If someone can hlep me to achieve this - it will be a great help
December 24, 2008 at 2:05 pm
One way of doing this is to use dynamic sql in your stored procedure.
The following code may help you to start with.
DECLARE @Table_Name varchar(40)
DECLARE @sql nvarchar(max)
SET @Table_Name = 'Person.Address' -- Replace with your table Name
SET @sql = 'SET'+' '+'IDENTITY_INSERT'+' '+@TABLE_NAME+' '+'OFF'
EXEC sp_executesql @sql
Remember for one session there can be only one table with indentity_insert set to ON.
Regards,
Sam.
December 24, 2008 at 2:30 pm
If you need to set identity_insert on and off dynamically it means that you don’t know which table you are going to work with and you have to use dynamic SQL for the insert statement also. Working with dynamic SQL has its own issues. Erland Sommarskog wrote a great paper on working with Dynamic SQL. You can find it at http://www.sommarskog.se/dynamic_sql.html. If you do work with dynamic SQL it might be beneficial to read this article.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply