使用参数执行存储过程

consider the following stocked proc:

考虑以下库存proc:

    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:

使用Gordon Linoff的回答:

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 give me an error:

然后下面给出一个错误:

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

Here is the error:

这是错误:

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

3 个解决方案

#1

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)
使用带有表名的QUOTENAME(如果表名中有一些特殊字符，比如空格等，这将对SQL注入有所帮助)
aliases (a and b in sample below)
别名(a和b在下面的样本中)
add GO to the end of your stored proc if you want to avoid error (See this answer for details):
如果您希望避免错误，请添加到存储proc的末尾(详细信息请参见此答案):

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

超过最大存储过程、函数、触发器或视图嵌套级别(限制32)

So your stored proc batch should look like this:

所以您存储的proc批应该是这样的:

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.

也许，您还需要传递列名。

#2

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.

您不能为表(或列或模式或数据库或函数或操作符名称)使用参数。但是，您可以使用适当的、显式的连接语法。

#3

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

#1