Re: Assign dynamic sql result to variable

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Quentin Ran (ab_at_who.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 09:05:09 -0500

Yes Steve, it is more complicated than that. The count determining
statement HAS to be a dynamic sql. I think Tea's suggestion is a viable
solution. Thanks for the response.

Quentin

"Steve Kass" <skass@drew.edu> wrote in message
news:eCHHpRTdEHA.3864@TK2MSFTNGP10.phx.gbl...
> 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

  • Re: Generate answer list for book of multiple-choice questions?
    ... > You will need a macro to make your answer table. ... > suggestion to hide the answer, and you use list styles to number the ... > Steve Hudson - Word Heretic ... >>> Dim Stem as string ...
    (microsoft.public.word.vba.general)
  • RE: Remove AutoGenerated DataGrid Column??
    ... If the only column you are certain of is the Primary Key then try Eliyahu's ... "Steve" wrote: ... >> Phillip Williams ... >>> Thanks for the suggestion Phillip. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Recording Date and Time--More Info Needed
    ... That post contains my response to your suggestion which I won't repeat ... "Steve Schapel" wrote: ... >> Earlier today I submitted the question, "In an Access form set up to allow ... This event triggers when the first ...
    (microsoft.public.access.gettingstarted)
  • Re: How to counting words in a string variable?
    ... sorry nad thanks for your suggestion. ... Steve Rindsberg, PPT MVP ... PPTools: www.pptools.com ...
    (microsoft.public.powerpoint)
  • Re: "Open Or Save" dialog on MPEG launch...?
    ... >> Hi Steve, ... >> Again, I appreciate your suggestion, but if I were missing a Codec, ... >> could Media Player launch properly when I click on Open. ... >Media Player may well recognise the file extender BUT there are various ...
    (microsoft.public.win2000.general)