November 1, 2007 at 2:02 am
Good day all...
I have to pass a table name as a variable to a stored proc, and as far as i know i have to use "set @Var = 'Statement' + @TablenameVar" then exec(@Var). Here is a bit of my sp...
create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)
---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------
as
declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName
------------------------------------------------------------------------------------------------------
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
The last param in this piece of code "(exec(DSNo)", is this doable? Is there a way to make this work?
Thanks
November 1, 2007 at 2:38 am
Do you want to list the output of exec(@DSNo) as column data in the last select statement?
Select(@Filename),(exec(@DSNo)), ................
That wont make sens because exec(@DSNo) may return multiple results.
If you want @Filename to be listed for each DataSupplierNo then you could create a temp table, insert the DataSupplierNo values in that table and then use it as part of your last SELECT statement.
For instance:
CREATE TABLE #SuplierNubers(DataSupplierNo VARCHAR(100))
Set @DSNo = 'INSERT #SuplierNubers select distinct DataSupplierNo from ' + @TableName
EXEC (@DSNo)
SELECT @Filename, DataSupplierNo FROM #SuplierNubers
November 1, 2007 at 2:55 am
Thanks, this is not exactly what i needed but it got me going in the rite direction, and it works now. Thanks again
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply