Friday, March 9, 2012

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A
|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.
|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>
|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>
|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors
you need to look at the result set as a whole when you are processing and
not a single record ar a time.
|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>
|||I juz think Uri is trying to make a point.. And what would that be?
|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

No comments:

Post a Comment