Create procedure for inserting record into table : Table name dynamically as an Param

[Jigar Naik]

Hi I am trying to create a procedure which will insert record in to one table but for inserting record i need to specify table name dynamically which will come as input parameter to my procedure.

i have written the following procedure for the same

    
ALTER PROC insert_all(
@tableName VARCHAR(100),
@codeVARCHAR(50),
@descVARCHAR(50)
)
AS
SET @tableName = 'mo_module'
INSERT INTO @tableName (mo_code,mo_desc) VALUES(@code,@desc)

Error Stack Trace

    
Server: Msg 137, Level 15, State 2, Procedure insert_all, Line 8
Must declare the variable '@tableName'.

Can anybody help me out with this. thats the issue. i am declaring the tableName variable but its still showing the error.

I am using MS SQL Server 2000.

Thanks & Regards,
Jigar Naik

[Paul Sturrock]

You can use variables as object names I think. Try using sp_sqlexec with your dynamic statement instead.