Passing table Column name as parameter to a stored procedure

I want to run a Stored Procedure which takes Column name( table column names ) as input, use some aggregate function and return the desired output.

I have tried passing a single column name as input parameter and also the entire SQL statement as input parameter. but i am not able to capture and return the output value

I have also tried using Table data type but fail to capture the value. I dont want to use Temporary table.

The syntax i tried is some thing like this

Declare @.stmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.rtval = @.stmt

And also

Declare @.AcctCode Char(8)
Declare @.Ssql NVarchar(100)
Declare @.rtVal numeric (18,5)
Set @.AcctCode = 'An_Sales'

Set @.Ssql = 'Select ' +@.rtval + '=AVG(an_sales) into From T_Comp_Profile'
Exec SP_ExecuteSql @.Ssql
print @.rtval

Pls help me in this regard

Ramanbir Singhtry something like...

Declare @.mystmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.mystmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.stmt= @.mystmt|||Dear Rockslide

U have wriiten me with the following code

Declare @.mystmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.mystmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.stmt= @.mystmt

The stt "Exec sp_executesql @.mystmt" this returns a data set, so it is not going to be stored in a variable like u specified i.e
Exec sp_executesql @.stmt= @.mystmt

because when we print the value of @.stmt using "Print @.stmt" it returns nothing also we cant use a table data type here in place of @.stmt|||hi rjaj

I think I am a little confused.

sp_executesql takes (basically) 2 different parameters, see the syntax below.

sp_executesql [@.stmt =] stmt
[
{, [@.params =] N'@.parameter_name data_type [,...n]' }
{, [@.param1 =] 'value1' [,...n] }
]

When we say

exec sp_executesql @.stmt=@.mystmt

we are effectively saying

exec sp_executesql @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'

if we do nothing else with the returned results they will be outputed.

if you want the results to return to a parameter then you would need to do something like

select @.results = sp_executesql @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'

at a guess (the line above hasn't been tested, in theory I think it should work).|||Sounds familiar

http://www.dbforums.com/t970045.html|||create table #tbl ([output] int null)
insert #tbl Exec (@.stmt)|||Originally posted by ms_sql_dba
create table #tbl ([output] int null)
insert #tbl Exec (@.stmt)

Using temporary tables work
but i dont want to use a temporary table
Is there any other way for that