passing tableName as a variable in stored prcoedure

Question

passing tableName as a variable in stored prcoedure

anjaliagarwal5

Ten Centuries

Points: 1267
More actions
April 3, 2017 at 9:18 am

#401888

Hi All,
I am passing table name as a variable in my stored prcoedure. Below is the stored procedure
Create procedure dbo.sp_Test
(
   @tableName   varchar(100),
   @facilityID varchar(100)
)
AS
DEclare @SQL nvarchar(max)
SET @SQL = ' INSERT INTO [' + @tableName + ']' +
  '(
   facilityId)
  Values
  (
   @facilityID )';
print @SQL;
     exec sp_executesql @SQL
When I execute the stored procedure, I keep getting an error saying
INSERT INTO [tbltest1](
   facilityId)
  Values
  (
   @facilityID )
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@facilityID".
I think it is not recognizing the facilityID that I am passing as a parameter. Do I need to re declare all the parameters that I am passing in the stored procedure. I am passing at least 50 parameters.

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You've got a PRINT @SQL line in your code: please will you post what it prints?

Also, please read about SQL injection, because you're wide open to it, and you could find yourself being bitten very hard.

John

Thom A SSC Guru Points: 99130 More actions · Answer 2

You're not escaping your Dynamic SQL when you get to your variable. Thus the SQL you're executing is expecting a variable @facilityID,. which you haven't declared.

Try replacing the relevant part with:

SET @SQL = ' INSERT INTO [' + @tableName + ']' + '(facilityId) VALUES (' + @facilityID + ');';

Although, as John said, consider injection here.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

anjaliagarwal5 - Monday, April 3, 2017 9:18 AM
Hi All,
I am passing table name as a variable in my stored prcoedure. Below is the stored procedure
Create procedure dbo.sp_Test
(
   @tableName   varchar(100),
   @facilityID varchar(100)
)
AS
DEclare @SQL nvarchar(max)
SET @SQL = ' INSERT INTO [' + @tableName + ']' +
  '(
   facilityId)
  Values
  (
   @facilityID )';
print @SQL;
     exec sp_executesql @SQL
When I execute the stored procedure, I keep getting an error saying
INSERT INTO [tbltest1](
   facilityId)
  Values
  (
   @facilityID )
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@facilityID".
I think it is not recognizing the facilityID that I am passing as a parameter. Do I need to re declare all the parameters that I am passing in the stored procedure. I am passing at least 50 parameters.

Inside your procedure, you're calling another procedure (sp_executesql). That procedure needs to get all the parameters needed.
You should also prevent sql injection by validating that your table name is a valid table. This is the way I do it.
DECLARE @tableName sysname; DECLARE @SQL nvarchar(max)

SELECT @SQL = ' INSERT INTO ' + QUOTENAME(name) + '( facilityId) Values ( @facilityID )' FROM sys.tables WHERE name = @tableName;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

anjaliagarwal5 Ten Centuries Points: 1267 More actions · Answer 4

Thom A - Monday, April 3, 2017 9:28 AM
You're not escaping your Dynamic SQL when you get to your variable. Thus the SQL you're executing is expecting a variable @facilityID,. which you haven't declared.
Try replacing the relevant part with:
SET @SQL = ' INSERT INTO [' + @tableName + ']' + '(facilityId) VALUES (' + @facilityID + ');';
Although, as John said, consider injection here.

Is there any workaround for this without writing a dynamic query? I have to pass the table name as a variable.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

anjaliagarwal5 - Monday, April 3, 2017 9:40 AM
Is there any workaround for this without writing a dynamic query? I have to pass the table name as a variable.

Not really. The fact that you're needing to do this at all suggest to me that your database may be poorly designed - perhaps not normalised. It wouldn't surprise me to see lots of table names with numbers in, something like Facilities001, Facilities002 and so on. Am I right?

John

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

anjaliagarwal5 - Monday, April 3, 2017 9:40 AM
Thom A - Monday, April 3, 2017 9:28 AM
You're not escaping your Dynamic SQL when you get to your variable. Thus the SQL you're executing is expecting a variable @facilityID,. which you haven't declared.
Try replacing the relevant part with:
SET @SQL = ' INSERT INTO [' + @tableName + ']' + '(facilityId) VALUES (' + @facilityID + ');';
Although, as John said, consider injection here.
Is there any workaround for this without writing a dynamic query? I have to pass the table name as a variable.

No, but there's an option to prevent SQL Injection when using the method I posted and the complete set of parameters for sp_executesql.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

anjaliagarwal5 Ten Centuries Points: 1267 More actions · Answer 7

John Mitchell-245523 - Monday, April 3, 2017 9:46 AM
anjaliagarwal5 - Monday, April 3, 2017 9:40 AM
Is there any workaround for this without writing a dynamic query? I have to pass the table name as a variable.
Not really. The fact that you're needing to do this at all suggest to me that your database may be poorly designed - perhaps not normalised. It wouldn't surprise me to see lots of table names with numbers in, something like Facilities001, Facilities002 and so on. Am I right?
John

yes, you are. I hate to write this dynamic query.

Thom A SSC Guru Points: 99130 More actions · Answer 8

anjaliagarwal5 - Monday, April 3, 2017 9:47 AM
John Mitchell-245523 - Monday, April 3, 2017 9:46 AM
anjaliagarwal5 - Monday, April 3, 2017 9:40 AM
Is there any workaround for this without writing a dynamic query? I have to pass the table name as a variable.
Not really. The fact that you're needing to do this at all suggest to me that your database may be poorly designed - perhaps not normalised. It wouldn't surprise me to see lots of table names with numbers in, something like Facilities001, Facilities002 and so on. Am I right?
John
yes, you are. I hate to write this dynamic query.

You really don't have much choice, if you have a database design like that and your table could be dynamic, then you SQL will need to be as well.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Just realize that Dynamic SQL is a tool. There are times it is needed and appropriate and other times when it isn't. Learning how to write good Dynamic SQL is a good thing, even if you use it sparingly.