Error while passing Table Name as a Parameter in Stored Procs

Question

Error while passing Table Name as a Parameter in Stored Procs

Suraj-479335

Old Hand

Points: 336
More actions
November 26, 2008 at 2:30 pm

#196646

Hello,
I want to be able to pass a table name as a parameter in a Stored Procedure. But I get the following error message.
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TableOutput".
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TableInput".
-- This code while running returns error.
create proc InsertRecords
(
@TableInput varchar(50),
@TableOutput varchar(50),
@EmployeeIDStart int,
@EmployeeIDEnd int
)
as
declare @sql nvarchar(2000)
set@sql = 'insert @TableOutput (EmployeeID, FirstName, LastName, JobTitle) '
set@sql = @sql + 'select EmployeeID, FirstName, LastName, JobTitle '
set@sql = @sql + 'from @TableInput '
set@sql = @sql + 'where EmployeeID between @EmployeeIDStart and @EmployeeIDEnd'
exec (@sql)
-- I used below code to execute the above Stored Proc 'InsertRecords'
exec InsertRecords'HumanResources.vEmployeeNames',
'HumanResources.vEmployeeTableStructure',
1, 100

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You need to concatenate the variables into the string (beware SQL injection). They way your dynamic SQL is currently written, SQL's looking for two table variables, called @TableInput and @TableOutput and two scalar variables @EmployeeIDStart and @EmployeeIDEnd within the scope of the dynamic SQL. The table variables don't exist at all and the scalar variables are out of scope within the dynamic string.

Try this (but ensure that you're checking for injection attempts)

declare @sql nvarchar(2000)

set @sql = 'insert ' + @TableOutput + ' (EmployeeID, FirstName, LastName, JobTitle) '

set @sql = @sql + 'select EmployeeID, FirstName, LastName, JobTitle '

set @sql = @sql + 'from ' + @TableInput

set @sql = @sql + ' where EmployeeID between ' + CAST(@EmployeeIDStart AS VARCHAR(10)) + ' and ' + CAST(@EmployeeIDEnd AS VARCHAR(10))

exec (@sql)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Also read www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Suraj-479335 Old Hand Points: 336 More actions · Answer 3

Suraj-479335

Old Hand

Points: 336

December 5, 2008 at 1:00 am

#908086

thank you for your suggestion.