sql stored procedure question

W

wsyeager

Well-known member
Joined
Apr 10, 2003
Messages
140
Location
Weston, FL
Im trying to insert data into a table that was created dynamiclly. Data will be inserted into several of these types of tables. I tried to substitute the tablename as a parameter, but got the following error when I tried to execute the query:

"Invalid object name @CDRTableName.

Here is my stored procedure. Notice I included the periods for brevity:

Code: 
ALTER PROCEDURE [dbo].[InsertCDRSearchResults]
(
 @BatchName varchar(50),
 .
 .
 .
 @CDRTableName varchar(20)
)
AS
INSERT INTO [@CDRTableName] ([BatchName], ..., [CDR_Version]) VALUES (@BatchName, ..., @CDR_Version);

If I obviously hard code the parameter @CDRTableName, it works just fine.

How can I successfully construct the stored procedure in order to use the correct table name?
 
Last edited by a moderator:
btw, the "hard coded" stored procedure (the one with the table name specified in it) is used with the ado.net 2.0 bulk insert capability (by setting the UpdateBatchSize to a specified amount other than 1). The dataset associated with the dataadapter, then does all the inserts at one shot (rather than calling the sproc numerous times 1 by 1 for each record).

I just need to find a way for the sproc to recognized the dynamic table name Im passing down to it for the insert.
 
Ive used dyanamic sql before to execute a sproc, but the above sproc will get executed thousands of times with one shot (see above). The dynamic option, I believe is not possible, because all the values (for the insert), will change on every iteration of executing the sproc.
 
Back
Top