Passing Object names to Stored Procedures

[itsmarkdavies]

Can anyone tell me the correct syntax for passing a Table Name to a Stored Procedure that uses the Table Name to do an INSERT into that Table ?

Below is my poor effort at the SQL.

Thanks.

CREATE PROCEDURE InsertOrderLine

@OrderNo INT,
@ProductID VARCHAR(15),
@UnitCost MONEY = 0,
@Quantity FLOAT = 0,
@Discount MONEY = 0,
@Tax MONEY = 0,
@TABLENAME VARCHAR(200)

AS

DECLARE @SQL VARCHAR(1000)

SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo,
ProductID, UnitCost, Quantity, Discount, Tax) VALUES
(@OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity +
',' + @Discount + ',' + @Tax)'

GO

 

[Cagliostro]

You should put SET instead of SELECT John Fill

ivfmd@mail.md

 

[tlbroadbent]

You can use either Set or Select. What you need to do is correct the syntax for building the dynamic SQL statement and add Execute(@sql) or sp_executesql @sql to your SP.


SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo, ProductID, UnitCost, Quantity, Discount, Tax) VALUES (' + @OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity + ',' + @Discount + ',' + @Tax + ')'

exec sp_executesql @sql Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot