help with store procedure - getting results from 3 queries

26 May 2005 4:09 PM

Mike

Hi,
I have a table user with the following field.
id,code,xdate

I am trying to write a stored procedure that would return the results as the
following 3 queries

a. select count(id) from user where code like 'TR%'
b. select count(id) from user where code like 'TR%' and xdate < getdate()
c. select count(id) from user where code like 'XP%' and xdate < getdate()

Basically the new stored procedure should give me the results of a,b & c in
the most efficient way. I would really appreciate your help.
Thanks

26 May 2005 4:21 PM

Alejandro Mesa

Mike,

What about table definition, including indexes?

Try:

create procedure p1
@a int ouput,
@b int output,
@c int output
as
set nocount on

select @a = count(*), @b = count(case when xdate < getdate() then 1 end)
from user
where code like 'TR%'

select @c = count(*)
from user
where code like 'XP%' and xdate < getdate()
go

AMB

Show quoteHide quote

"Mike" wrote:

> Hi,
> I have a table user with the following field.
> id,code,xdate
>
> I am trying to write a stored procedure that would return the results as the
> following 3 queries
>
> a. select count(id) from user where code like 'TR%'
> b. select count(id) from user where code like 'TR%' and xdate < getdate()
> c. select count(id) from user where code like 'XP%' and xdate < getdate()
>
> Basically the new stored procedure should give me the results of a,b & c in
> the most efficient way. I would really appreciate your help.
> Thanks

26 May 2005 5:44 PM

Mike

I am getting errors-
must declare a
must declare b

Show quoteHide quote

"Alejandro Mesa" wrote:

> Mike,
>
> What about table definition, including indexes?
>
> Try:
>
> create procedure p1
> @a int ouput,
> @b int output,
> @c int output
> as
> set nocount on
>
> select @a = count(*), @b = count(case when xdate < getdate() then 1 end)
> from user
> where code like 'TR%'
>
> select @c = count(*)
> from user
> where code like 'XP%' and xdate < getdate()
> go
>
>
> AMB
>
> "Mike" wrote:
>
> > Hi,
> > I have a table user with the following field.
> > id,code,xdate
> >
> > I am trying to write a stored procedure that would return the results as the
> > following 3 queries
> >
> > a. select count(id) from user where code like 'TR%'
> > b. select count(id) from user where code like 'TR%' and xdate < getdate()
> > c. select count(id) from user where code like 'XP%' and xdate < getdate()
> >
> > Basically the new stored procedure should give me the results of a,b & c in
> > the most efficient way. I would really appreciate your help.
> > Thanks

26 May 2005 4:29 PM

Carl Imthurn

Couple of things:
1) do you want the stored procedure to return one recordset or three?
If one, you'll need to use the UNION keyword between the SELECT statements.

2) user is a reserved word; you'll need to enclose it in brackets
... from [user] ...

3) same thing for id (I think) ... count([id]) ...

Mike wrote:

Show quoteHide quote

> Hi,
> I have a table user with the following field.
> id,code,xdate
>
> I am trying to write a stored procedure that would return the results as the
> following 3 queries
>
> a. select count(id) from user where code like 'TR%'
> b. select count(id) from user where code like 'TR%' and xdate < getdate()
> c. select count(id) from user where code like 'XP%' and xdate < getdate()
>
> Basically the new stored procedure should give me the results of a,b & c in
> the most efficient way. I would really appreciate your help.
> Thanks

26 May 2005 4:46 PM

Mike

I need only 1 recordset

Show quoteHide quote

"Carl Imthurn" wrote:

> Couple of things:
> 1) do you want the stored procedure to return one recordset or three?
> If one, you'll need to use the UNION keyword between the SELECT statements.
>
> 2) user is a reserved word; you'll need to enclose it in brackets
> ... from [user] ...
>
> 3) same thing for id (I think) ... count([id]) ...
>
> Mike wrote:
>
> > Hi,
> > I have a table user with the following field.
> > id,code,xdate
> >
> > I am trying to write a stored procedure that would return the results as the
> > following 3 queries
> >
> > a. select count(id) from user where code like 'TR%'
> > b. select count(id) from user where code like 'TR%' and xdate < getdate()
> > c. select count(id) from user where code like 'XP%' and xdate < getdate()
> >
> > Basically the new stored procedure should give me the results of a,b & c in
> > the most efficient way. I would really appreciate your help.
> > Thanks
>
>

26 May 2005 4:52 PM

gopi

Mike,

Would this help ?

select count(id)
from User
where   (code like 'TR%')
OR      ((code like 'TR%') and (xdate < getdate()))
OR      ((code like 'XP%') and (xdate < getdate()))

Gopi
Show quoteHide quote

"Mike" <M***@discussions.microsoft.com> wrote in message
news:25AD94C9-6C06-4675-B158-6B47B32EBCD1@microsoft.com...
>I need only 1 recordset
>
> "Carl Imthurn" wrote:
>
>> Couple of things:
>> 1) do you want the stored procedure to return one recordset or three?
>> If one, you'll need to use the UNION keyword between the SELECT
>> statements.
>>
>> 2) user is a reserved word; you'll need to enclose it in brackets
>> ... from [user] ...
>>
>> 3) same thing for id (I think) ... count([id]) ...
>>
>> Mike wrote:
>>
>> > Hi,
>> > I have a table user with the following field.
>> > id,code,xdate
>> >
>> > I am trying to write a stored procedure that would return the results
>> > as the
>> > following 3 queries
>> >
>> > a. select count(id) from user where code like 'TR%'
>> > b. select count(id) from user where code like 'TR%' and xdate <
>> > getdate()
>> > c. select count(id) from user where code like 'XP%' and xdate <
>> > getdate()
>> >
>> > Basically the new stored procedure should give me the results of a,b &
>> > c in
>> > the most efficient way. I would really appreciate your help.
>> > Thanks
>>
>>