CODEWITHSUNDEEP
sql-server-2008
happysmile
happysmile

Reputation: 7777

Dynamic Query not working along with an variable

declare  @SQL  varchar(2000)
declare @Total int 

SET @SQL = 'Select'+@Total+'= SUM(isnull(Points,0)) as Pointsvalue  From  Offer'

exec(@SQL)  

select @Total

I am trying to do like this but always the result shows me as empty. But when I run the query directly it gives an result of 120

Where I am going wrong? Please let me know

Thanks

prince

Upvotes: 0

Views: 281

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

What you were trying to do is (I think)

SET @SQL = 'Select @Total = SUM(isnull(Points,0)) as Pointsvalue  From  Offer'

However that will tell you off because @Total is not in scope (The query in an Exec has it's own)

There again you don't need to do the exec if you want the above...

So I'm a bit confused on what you were trying to achieve

A tip

When you write a query using exec(String). Comment out the exec, and replace it with Print @SQL, see waht it's going to try and do.

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

If you want to run this as a dynamic query, try like this:

declare @SQL nvarchar(2000), @Total int 

SET @SQL = N'Select @Total = SUM(isnull(Points,0)) as Pointsvalue  From  Offer'
exec sp_executesql @SQL, N'@Total int output', @Total = @Total output

select @Total

(see sp_executesql (Transact-SQL))

However, it looks like you don't have a really dynamic query there.
So, as msmucker0527 pointed out you could simply run:

declare @Total int 

Select @Total = SUM(isnull(Points,0)) as Pointsvalue  From  Offer

select @Total

Upvotes: 5

Related Questions