Click here to Skip to main content
15,070,511 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
how to receive table variable in stored procedure as argument

SQL
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
Posted
Comments
Santhosh Kumar Jayaraman 28-Jul-12 6:11am
   
whats the issue you are facing?

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:
SQL
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!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900