sql stored procedure question

[wsyeager]

I'm 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:

 

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?

Edited June 23, 2006 by PlausiblyDamp

[wsyeager]

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 I'm passing down to it for the insert.

[wsyeager]

I've 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.