| Author |
Help - Simple Question
|
|
| Code Boy 2006-04-03, 11:23 am |
| 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.
| |
| Aaron Bertrand [SQL Server MVP] 2006-04-03, 11:23 am |
| >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
| |
| Tom Moreau 2006-04-03, 11:23 am |
| 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.
| |
| Uri Dimant 2006-04-03, 11:23 am |
| 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.
>
>
| |
| Code Boy 2006-04-03, 11:23 am |
| 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
>
| |
| Aaron Bertrand [SQL Server MVP] 2006-04-03, 11:23 am |
| 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)
| |
| Omnibuzz 2006-04-03, 11:23 am |
| 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.
| |
| Uri Dimant 2006-04-03, 11:23 am |
| 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?
>
>
>
>
| |
| Omnibuzz 2006-04-03, 11:23 am |
| I juz think Uri is trying to make a point.. And what would that be??
| |
| 05ponyGT 2006-04-03, 8:23 pm |
| 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@microso
ft.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.
|
|
|
|