Executing a stored procedure with parameter

Question

Consider the following stored procedure:

CREATE PROCEDURE Test (@Table1 NVARCHAR (100), @Table2 NVARCHAR(100))
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(500);

    SELECT @sSQL = N'SELECT * FROM ' + @Table1+' '+ @Table2
    + 'where '+@Table1+'.id = '+@Table2+'.id_dept';

    EXEC sp_executesql @sSQL

END

I have tried to execute it using the following syntax (but I get an error):

exec  Test @table1='dept', @table2='emp'

Edit:

Using the Gordon Linoff's answer:

alter PROCEDURE Test (
    @Table1 NVARCHAR (100), @Table2 NVARCHAR(100))
    AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(500);

   SELECT @sSQL = N'SELECT * FROM ' + @Table1 + ' JOIN '+ @Table2
+ ' ON '+ @Table1+'.id = '+ @Table2+ '.id_dept';

EXEC sp_executesql @sSQL;


END

and then the following throws an error:

exec  Test @table1='dept', @table2='emp';

Here is the error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

Your query syntax doesn't make sense. Your `FROM` clause is missing the `JOIN` that you need for two tables. — Gordon Linoff, May 14 '17 at 12:06
Until telling us the error text , try add comma into `@Table1+' , '+ @Table2` instead of `@Table1+' '+ @Table2`. — ahmed abdelqader, May 14 '17 at 12:11
I'm trying to consider it, but all I can see are the SQL injection attacks. — alroc, May 14 '17 at 12:18

Gordon Linoff · Answer 1 · 2017-05-14T12:15:05.963

1

Presumably, you want something like this:

DECLARE @table1 VARCHAR(255);
DECLARE @table2 VARCHAR(255);

SELECT @table1 = 'dept', @table2 = 'emp';

SELECT @sSQL = N'SELECT * FROM ' + @Table1 + ' JOIN '+ @Table2
+ 'ON '+ @Table1+'.id_dept = '+ @Table2+ '.id_dept';

EXEC sp_executesql @sSQL;

You don't really need the parameter definitions in the code. I'm only putting them in, because you appear to want to over-ride the values being passed into the stored procedure.

You cannot use parameters for table (or column or schema or database or function or operator names). You can, however, use proper, explicit JOIN syntax.

edited May 14 '17 at 12:15

answered May 14 '17 at 12:08

Gordon Linoff

1,242,037
58
646
786

1

Should `QUOTENAME()` be used on those table names when constructing the query for safety? – alroc May 14 '17 at 12:19
Thanks. Now I get `The maximum nesting level of stored procedures, functions, triggers, or views is exceeded (limit 32).`(translation) – user7233170 May 14 '17 at 12:41
here is exception in french: `Msg 217, Niveau 16, État 1, Procédure sp_executesql, Ligne 1 Le niveau maximal d'imbrication des procédures stockées, des fonctions, des déclencheurs ou des vues est dépassé (limite 32). ` – user7233170 May 14 '17 at 12:44

score 1 · Answer 2 · answered May 14 '17 at 12:30

1

Try :

        DECLARE @sSQL nvarchar(500);

        SET @sSQL = N'SELECT * FROM ' + @Table1+' JOIN '+ @Table2
        + ' ON '+@Table1+'.id_dept = '+@Table2+'.id_dept';

        EXEC sp_executesql @sSQL;

    END

answered May 14 '17 at 12:30

Ilyes

14,640
4
29
55

score 1 · Accepted Answer · edited May 23 '17 at 12:03

I suggest you to:

use QUOTENAME with table names (it will help if you got some special characters in table name, like spaces etc. and should help with SQL injections)
aliases (a and b in sample below)
add GO to the end of your stored proc if you want to avoid error (See this answer for details):

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

So your stored proc batch should look like this:

CREATE PROCEDURE Test (
    @Table1 NVARCHAR(100),
    @Table2 NVARCHAR(100),
)
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(4000);

    SET @sSQL = N'SELECT * ' +
    'FROM ' + QUOTENAME(@Table1) + ' as a ' +
    'INNER JOIN ' + QUOTENAME(@Table2) + ' as b ' +
        'ON a.id_dept = b.id_dept';

    EXEC sp_executesql @sSQL

END
GO

And, maybe, you need to pass column names too.

score 0 · Answer 4 · answered Mar 18 '20 at 16:09

What I would try:

   alter PROCEDURE Test (
   @Table1 NVARCHAR (100), 
   @Table2 NVARCHAR (100))
   AS
BEGIN

   DECLARE @TableOne NVARCHAR(100) = @Table1
   DECLARE @TableTwo NVARCHAR(100) = @Table2
   SET NOCOUNT ON;
   DECLARE @sSQL nvarchar(500);

   SELECT @sSQL = N'SELECT * FROM ' + @TableOne + ' JOIN '+ @TableTwo
   + ' ON '+ @TableOne+'.id = '+ @TableTwo+ '.id_dept';

   EXEC sp_executesql @sSQL;

END

And Then of Course:

exec  Test @table1='dept', @table2='emp';

What I know is that it is always a better idea in such situation to make some difference in the names when you are passing data.

Executing a stored procedure with parameter

4 Answers4