Passing table name as parameter to stored procedure with other additional parameters

Question

Hi!

I'm trying to write a stored procedure, that expects a table name as parameter, and also some other parameters that will be used as parameters in WHERE or in VALUES. However, I can't get this to work. If I only do a

SELECT * FROM @table_name, it's fine, I can get that to work, however, I can't find a way to get a query like

SELECT * FROM @table_name WHERE PackageID = @packID to work.

In the first case, I use the following code:

ALTER PROCEDURE [dbo].[probaParamLek] 

@table_name varchar(50) 

AS

BEGIN

SET NOCOUNT ON;

SET @table_name = RTRIM(@table_name)

DECLARE @cmd AS NVARCHAR(max)

SET @cmd = N'SELECT * FROM ' + @table_name

EXEC sp_executesql @cmd

END

This above stored procedure works. When I try to achieve the other stored procedure as I mentioned above, it just simply doesn't work, and the error I get says nothing:

ALTER PROCEDURE [dbo].[probaParamLek] 

@table_name varchar(50) ,

@packID varchar(100)

AS

BEGIN

SET NOCOUNT ON;

SET @table_name = RTRIM(@table_name)

SET @packID = RTRIM(@packID)

DECLARE @cmd AS NVARCHAR(max)

SET @cmd = N'SELECT * FROM ' + @table_name + 'WHERE PackageID = ' + @packID

EXEC sp_executesql @cmd

END

Is there a way to get the second stored procedure to work somehow? I'd also need this with UPDATE and INSERT commands. I need to get this to work, because I have a lot of the same table, but with different table names, and I don't want to create a stored procedure for each one of those tables, because if I can get this to work, it would be a lot easier. Also, with this string concatenation technique as I tried above, does the SQL server check the parameters, or am I risking SQL injection? If yes, then what can I do about it?

Thanks in advance for the help!

Zoli

Answer 1

It looks like you need a space before the WHERE.  The dynamic sql is running the table name and the WHERE clause together.

 

SET @cmd = N'SELECT * FROM ' + @table_name + ' WHERE PackageID = ' + @packID

Answer 2

Thank you for your answer, but unfortunately that didn't help either. (True, that was a mistake I made, but it still doesn't work this way.)

Here is the error message I get when I try to execute it:

Invalid column name 'something'

where 'something' is the value of the @packID parameter I put in it when I try to execute it

Answer 3

It sounds like the packageid may need to be in quotes.  Try this.

 

SET @cmd = N'SELECT * FROM ' + @table_name + ' WHERE PackageID = ''' + @packID + ''''

Answer 4

Just as a side note, but you should always make sure such procedures are not gonna be used as a SQL-injection attack vector. Object names are properly composed with a schema, so you should accept a schema parameter as well. Best practices recommend to pass in separate parts of the object name and use QUOTENAME to compose the name. varchar(50) is not an appropiate type for an object name, use the proper sysname type. Place a space in front of WHERE. And use again QUOTENAME with the single quote ' as the optional quote_character parameter to properly escape the @packID parameter, again to prevent a sql-injection attack vector:

ALTER PROCEDURE [dbo].[probaParamLek]
@schema sysname,

@table_name sysname,

@packID varchar(100)

AS

BEGIN

SET NOCOUNT ON;

SET @table_name = RTRIM(@table_name)

SET @packID = RTRIM(@packID)

DECLARE @cmd AS NVARCHAR(max)

SET @cmd = N'SELECT * FROM ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' WHERE PackageID = ' + QUOTENAME(@packID, '''')

EXEC sp_executesql @cmd

END

Even better in your case would be to pass in the @packID as a parameter to the sp_executesql:

ALTER PROCEDURE [dbo].[probaParamLek]
@schema sysname,

@table_name sysname,

@packID varchar(100)

AS

BEGIN

SET NOCOUNT ON;

SET @table_name = RTRIM(@table_name)

SET @packID = RTRIM(@packID)

DECLARE @cmd AS NVARCHAR(max)

SET @cmd = N'SELECT * FROM ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' WHERE PackageID = @packID';

EXEC sp_executesql @cmd, N'@packID varchar(100)', @packdID;

END

Answer 5

Thanks a lot for both replies!

The '''' was the problem, that's why it wasn't working, thanks again!

Also, thank you for the SQL Injection prevention, that also helps me a lot and I'll do it that way!

However I'm not sure about this schema parameter you mentioned. What input do I have to put there?

Answer 6

Sz_Zoli wrote:

Thanks a lot for both replies! However I'm not sure about this schema parameter you mentioned. What input do I have to put there?

If the table is in 'dbo' schema, then dbo. Otherwise you won't be able to pass in names of tables in other schemas.

Answer 7

Ahh okay, thank you! I understand now!

Answer 8

 im trying to create a stored prcedure that you pass
the table name end get the next autonumber from that atable ?

some help please where did i go wrong

example

CREATE PROCEDURE dbo.GetNextID
 (@TableName Varchar(30),
 @NextID integer OUTPUT)

AS
 SELECT @NextID = MAX(RecordID) + 1 FROM @TableName
 
 RETURN @NextID

Answer 9

try this
------------
CREATE

 PROC [dbo].[GetTablesDetails]

(

@tblName

nvarchar(100)

)

AS

BEGIN

DECLARE

@SQL nvarchar(100)

SET

@SQL = 'SELECT * FROM ' + @tblName

EXEC

(@SQL)

END

Answer 10

The risk of SQL injection has already been mentioned in this thread but I think it needs to be emphasized.  Try this:

 

EXEC dbo.GetTablesDetails 'dbo.Foo;DROP TABLE dbo.Foo;

 

See http://www.sommarskog.se/dynamic_sql.html for a thorough discussion on dynamic SQL considerations.

--
Hope this helps.

 

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"A H AN" wrote in message news:dc968c9c-3596-476 1-9395-213459e5d48e...

try this
------------
CREATE

 PROC [dbo].[GetTablesDetails]

(

@tblName

nvarchar(100)

 

AS

BEGIN

DECLARE

@SQL nvarchar(100)

SET

@SQL = 'SELECT * FROM ' + @tblName

EXEC

(@SQL)

END

)

Answer 11

Dan, you might want to redo the link.

Answer 12

Thanks, Brian.  I fixed the link to http://www.sommarskog.se/dynamic_sql.html

Answer 13

Glad i could be of service.

And while i have you attention... :)

Is there a link for comments RSS on your blog?

Answer 14

And while i have you attention... :)

Is there a link for comments RSS on your blog?

Unfortunately, I don't currently have a comments RSS option available.  I'm constrained by my blog Subtext version but will certainly include comments when available. 

--
Hope this helps.

 

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang /

Answer 15

Thanx.

Answer 16

Thanks Dan excellent tutorial about risks of SQL Injection and how to avoid it... here http://www.sommarskog.se/dynamic_sql.html

I am currently  working on a Windows desktop application but I want my stored procedures to be future proof in case in the future me or some other developer needs to build a web interface. For now I'm going to use sp_executesql and also format the sql string properly so that "Since the SQL String does not include any user input, there is no opening for  injection" [as you mentioned aptly in your article].

This is what I"m thinking of doing:

1) I only need to get start and end dates of a report directly from a user - so instead of giving blank text fields for input, I"m using combo boxes for Day/month/year - that way the user IS FORCED to select an expected input format - he simply can't manipulate the SQL string.

2) I had a need to get table names as parameters, but now after reading your article I'm not using the table Name as a parameter directly and have instead already created static views with the table names I need  ahead of time (there aren't too for my needs) and one of these static SQL views is called from my dynamic SQL stored procedure based on something a user clicks on - and not based on what he/she inputs in a text box. That way they can't enter weird SQL into my application.