如何在存储过程中接收表变量作为参数

问题描述：

如何在存储过程中接收表变量作为参数

how to receive table variable in stored procedure as argument

CREATE FUNCTION AID
(
    -- Add the parameters for the function here
    @tblDeductee Table
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @DID int
    select top 1 @DID=DID from @tblDeductee
    RETURN @DID
END
GO

答

传入表名的唯一方法是通过动态sql-即，您使用参数来构建一个SQL字符串，然后执行该字符串.您不能按原样传递表名，SQL不能那样工作.

但是，我强烈建议您完全不要这样做-这是对SQL注入攻击的一种邀请.例如，如果我向您的代码请求一个名为"Employees; DROP TABLE Employees;-"的表，则您构造的SQL语句将选择记录，然后从数据库中删除该表.

为遇到的任何问题找到更好的解决方案-这根本不是一个好主意.

The only way to pass the table name in is via dynamic sql - i.e. you use the parameter to build an SQL string which you then execute. You can''t just pass the table name in as you are, SQL doesn''t work like that.

However, I strongly suggest that you don''t do it at all - it is an invitation to SQL injection attacks. For example, if I ask your code for a table called "Employees;DROP TABLE Employees;--" then your constructed SQL statement will select the records, then delete the table from your database.

Find a better solution to whatever problem you are having - this is not a good idea at all.

在这里，看看: ^ ]

示例示例:

Here, have a look: MSDN: Use Table-Valued Parameters (Database Engine)[^]

Sample example:

USE AdventureWorks2012;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO AdventureWorks2012.Production.Location
           (Name
           ,CostRate
           ,Availability
           ,ModifiedDate)
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT Name, 0.00
    FROM AdventureWorks2012.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

试试吧！

Try!

如何在存储过程中接收表变量作为参数

相关推荐