Re: Assign dynamic sql result to variable

From: Steve Kass (skass_at_drew.edu)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 02:22:37 -0400

Quentin,

  Is what you need to do more complicated than this?

update TableA set
  actionCount = (select count(*) from TableB where userid = @userid)
where userid = @userid

Steve Kass
Drew University

Quentin Ran wrote:

>I meant
>
>Am I stuck with
>
>declare @actionCount int
>exec(' select 1 from TableB where userid = ' + @userid) -- select 1 instead
>of select count(*)
>set @actionCount = @@rowcount
>
>?
>
>Quentin
>
>"Quentin Ran" <ab@who.com> wrote in message
>news:uhF5#4OdEHA.3308@TK2MSFTNGP11.phx.gbl...
>
>
>>Hi group,
>>
>>my brain is not working today. How do I get the following done:
>>
>>I have TableA (userid, actionCount), TableB (userid, action) with TableA
>>being parent of TableB.
>>
>>I want in a stored proc to do the following:
>>
>>declare @sql varchar(999)
>>declare @userid
>>set @userid = 111 -- this will be a parameter passed in to the proc
>>set @sql = ' select count(*) from TableB where userid = ' + @userid
>>
>>update TableA set actionCount = exec(@sql) where userid = @userid
>>
>>The last sentence is what I need to do: update a table setting the value
>>
>>
>of
>
>
>>a column to a value returned by a dynamic sql. It does not work in the
>>
>>
>way
>
>
>>written in the sentence. I remember seeing such query, but just can not
>>remember how it works. My further research shows that I even can not do
>>
>>declare @actionCount int
>>select @actionCount = exec(@sql)
>>or set @actionCount = exec(@sql)
>>
>>since the select or set requires an expression to the right of the equal
>>sign. Am I stuck with
>>
>>declare @actionCount int
>>exec(@sql)
>>set @actionCount = @@rowcount
>>
>>? Any suggestions welcome. TIA for your reading and responding to this.
>>
>>Quentin
>>
>>
>>
>>
>>
>>
>
>
>
>

Relevant Pages