如何在存储过程中接收表变量作为参数 [英] how to receive table variable in stored procedure as argument

问题描述

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

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!

这篇关于如何在存储过程中接收表变量作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！