How to Execute a Query Stored in a variable in Stored Procedure

[sukeshchand]

I want to exicute a Query that stored in a variable. and the Query is used to get a given value to a variable. I tried to use exec command but that returns an error. i cant get an idea for how to do
the SQL commands are given bellow

This line is used to generate the required query and stored to a variable @mnvcSQL
select @mnvcSQL='Select @Prev=year' + convert(varchar,cast((@Field-1) as int))+ ' from tblFinancialDistribution where ProjectId=' + cast(@pintProjectId as Varchar)+ ' and [lineno]=' + cast(@mvcLineNoNetCashFlow as Varchar)

After Executing the above line the variable @mnvcSQL contain the the SQL Sentance like
Select @Prev=year4 from tblFinancialDistribution where ProjectId=42 and [lineno]=-29

I Want to exicute this statement and want the resulted value in @Prev

[iburyak]

I don't think you can return parameter from exec command.
But as I looked at your query closely I don’t see a need to concatenate this statement to be later executed.
Is it possible to use it as is and have @Prev returned its normal way?

[PHP]Select @Prev = year + (@Field - 1)
from tblFinancialDistribution where ProjectId = @pintProjectId
and [lineno]= @mvcLineNoNetCashFlow[/PHP]

[sukeshchand]

i am creating this query for stisfy my condition. the problem inside is this. i want to select a field thats name started with year. the year 1 to year 15 columns are in my table. some time i want year1 and some time i want year7. that is dynamic. the only solution is to concatinate the statement.
and the final query is stored in a variable. that query returns a value. i want to exicute the query and get the result in another variable.

[sukeshchand]

the above statement consider year as the column name and @field1 is the variable that must be added to the value of year. so pls check it, help me

[iburyak]

So I think you can start concatenation after you've got your variable result.


Example:
[PHP]Declare @Prev int, @SQL varchar(8000)

Select @Prev = year + (@Field - 1)
from tblFinancialDistribution where ProjectId = @pintProjectId
and [lineno]= @mvcLineNoNetCashFlow

Select @SQL = 'select ' + convert(varchar(10) + 'column_name from table_name'

Execute @SQL[/PHP]

[sukeshchand]

Execute @SQL Not working. The Exicute statement is used to exicute stored procedures not queries.
I solve the above problem by using temparary table (#Table). if any one have a different solution please replay

[iburyak]

I think I forgot to put ( )around @SQL


Try this example:

[PHP]
Declare @SQL varchar(4000)

Select @SQL = 'Select * from sysobjects'

EXEC (@SQL)[/PHP]

[sukeshchand]

i think you did't get my question...
Please Check this Query and tell me where is the mistake

declare @a int
declare @str varchar(1000)
declare @b numeric(18,3)
 
select @a=1
select @str ='select @b=year' + convert(varchar(1),@a) + ' from tblpandlsummary'
select @str
execute (@str)
select @b

[iburyak]

i think you did't get my question...
Please Check this Query and tell me where is the mistake

declare @a int
declare @str varchar(1000)
declare @b numeric(18,3)
 
select @a=1
select @str ='select @b=year' + convert(varchar(1),@a) + ' from tblpandlsummary'
select @str
execute (@str)
select @b

I think I did get your question.
You just don't understand how it works. You can't expose variable result outside execute statement. The only way I see it would work is:

[PHP]declare @a int
declare @str varchar(1000)


select @a=1
select @str ='declare @b numeric(18,3) select @b=year' + convert(varchar(1),@a) + ' from tblpandlsummary select @b'
select @str
execute (@str)[/PHP]

But I don't see a point. You can't use it after statement is executed.

[almaz]

i think you did't get my question...

OK, it's my turn to step into discussion :).
As you didn't provide a DDL for your original question, I'll show you a sample that will do a trick (note that @sql must be nvarchar, not varchar):

declare @sql nvarchar(2000)
select top 1 @sql = 'select top 1 @Value = ID from [' + object_name(id) + ']' 
from syscolumns 
where name = 'ID' and xtype = 56 -- int column
 
declare @MyValue int
exec sp_executesql @sql, N'@Value int output', @Value = @MyValue output
 
print @MyValue

[iburyak]

Cool.... :)

[sukeshchand]

Yesssssss... Thats the final solution....

[Mc1brew]

This worked great for me. Thanks!