how to get result of a dynamic query in a proc

Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
dynamically determined. What are natural ways to receive the value of
cnt and continue to process accordingly within a proc?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Charles,
See if this helps..
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/?id=262499
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Charles Yu" <anonymous@.devdex.com> wrote in message
news:uEDA8V%23SDHA.1572@.TK2MSFTNGP12.phx.gbl...
> Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
> dynamically determined. What are natural ways to receive the value of
> cnt and continue to process accordingly within a proc?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to get distinct columns using COALESCE

Hi guys, can you please help me to solve this problem. I have to get distinct row from offering column of xyz table.

I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.

SELECTDISTINCT @.Staging_Off=COALESCE(@.Staging_Off+',','')+ Offering

FROM xyz

WHERE xyz.OfferingNOTIN

(SELECTDISTINCT Offering.Offering

FROM OfferingJoin xyz

ON Offering.Offering= xyz.Offering

AND Offering.SourceSystem= @.SourceSystem

)

That's probably because your nested query (SELECT DISTINCT) is not working on the same row as your outer query. You must make sure your inner query join with a value of the outer query, otherwise they won't be related. Something like this:

SELECT DISTINCT @.Staging_Off=COALESCE(@.Staging_Off +',','')+ OfferingFROM xyzAS X1WHERE X1.OfferingNOT IN(SELECT DISTINCT O.OfferingFROM OfferingAS O, xyzAS X2WHERE O.Offering = X2.OfferingAND X2.SomeId = X1.SomeIdAND O.SourceSystem= @.SourceSystem)

Here I assume that there are some kind of unique id in xyz table that can be used

I think this query could be rewritten in a more clean manner, but I can't do it from the tip of my head. Would need source data and do some trial and erroring ;-) Good luck!