Using exec in my sp

Question

Post reply

Using exec in my sp

aortaboota

Grasshopper

Points: 12
More actions
November 1, 2007 at 2:02 am

#178917

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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johannes Fourie Default port Points: 1470 More actions · Answer 1

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

aortaboota Grasshopper Points: 12 More actions · Answer 2

Thanks, this is not exactly what i needed but it got me going in the rite direction, and it works now. Thanks again