Passing a parameter to an SP and using in a SET command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Passing a parameter to an SP and using in a SET command

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-08 : 09:45:48

Rob writes "I'm trying to do a bulk insert (from a number of tables stored in Access) into tables that have an identity column as PK. So in SQL I have PK's of 1 through 10,000 let's say. I need to delete PK's of 7,000 to 8,000 in the SQL table and import from a table in Access that has corresponding PK's of 7,000 to 8,000.

If I set Identity_insert ON for the table in SQL, then the data comes in fine. THe problem is that I have a number of tables that I have to do this on, and the IDENTITY_INSERT is only valid on one table at a time.

So I thought I'd create an sp that received a tablename as a parameter. Over in Access in vba, I'd iterate through the tables that I need to do this on, and immediately before the INSERT, I'd execute the SP, and pass it the proper tablename and hopefully, everything would fly.

the sp I need to run is:

CREATE PROCEDURE [dbo].[rvsSetIdentityInsert] AS
SET IDENTITY_INSERT Addresses ON

where I vary the tablename.
The SP I'm TRYING to make is like:

CREATE PROCEDURE [dbo].[rvsSetIdentityInsertByTable_JUNK]
@Tablename varchar(50)
AS
SET IDENTITY_INSERT @Tablename ON
GO

but this syntax fails on the second @tablename.

ANY help would be appreciated as I am a novice at writing SP's, but very fluent in Access and VBA.

(Also, if this is a stupid way of handling this problem, please let me know)
Thanks in advance,
Rob"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-08 : 15:11:20

exec ('SET IDENTITY_INSERT ' + @Tablename + ' ON')

But it'll only operate for the batch (the exec statement or the sp) and will be off again when it completes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources