sql stored procedure parameter problem

[PRITHA]

Hello,

I have used stored procedure with parameters.Now I want to use the parameter in the select statement.Following is my code in the procedure.

////////////////////////////////////
CREATE PROCEDURE locationproduct
@tlocation varchar(10),
@tproduct varchar(10)
@TableName varchar(10)
AS

declare @firstquarter int;

SET @firstquarter =
( SELECT SUM(DemandCol) AS firstquarter FROM DemandTable
WHERE location = @tlocation AND product= @tproduct )

insert into abc values (@tlocation,@first);


In the above instead of DemandTable I want to use TableName .
How do I do That?

Thanks,
Prithaa

[deepuv04]

Hello,

I have used stored procedure with parameters.Now I want to use the parameter in the select statement.Following is my code in the procedure.

////////////////////////////////////
CREATE PROCEDURE locationproduct
@tlocation varchar(10),
@tproduct varchar(10)
@TableName varchar(10)
AS

declare @firstquarter int;

SET @firstquarter =
( SELECT SUM(DemandCol) AS firstquarter FROM DemandTable
WHERE location = @tlocation AND product= @tproduct )

insert into abc values (@tlocation,@first);


In the above instead of DemandTable I want to use TableName .
How do I do That?

Thanks,
Prithaa

First build the select statement as a string and then execute the string


CREATE PROCEDURE locationproduct
@tlocation varchar(10),
@tproduct varchar(10)
@TableName varchar(10)
AS

declare @firstquarter int;

declare @str varchar(1000)

set @str = 'SELECT SUM(DemandCol) AS firstquarter FROM '+@TableName+' WHERE location = ' +
convert(varchar(10),@tlocation) + ' AND ' + convert(varchar(10),@tproduct)

SET @firstquarter = Exec (@str)



insert into abc values (@tlocation,@first);

[PRITHA]

Many many Thanks deep.