Multi-Value Parameter Problem

I'm creating a report that selects data from one table. I'm trying to
add a parameter that will allow the user to select the data based on a
selection from one column (SpindleName). I've added this column in the
report parameters, checked the multi-value box, set the available
values to "from query" to the dataset & field, set the default values
to "from query" to the same dataset & field. When I execute the query,
I get asked for the SpindleName, but the box contains no values other
than "NULL" and "BLANK". If I type the spindle name into the box, I
get the results. Why isn't the list being populated with the values
from the spindlename column ?
Here is the query:
SELECT StationName, SpindleName, PartId, TimeStamp
FROM dbo.Readings
WHERE SpindleName IN (@.SpindleName)
I'm using SQL 2005 Express & VS 2005
Any advice would be appreciated.
Thank-you
JeffWhat is the source query for the parameter?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||I may be wrong but it seems to me there is some confusion here. Several
things:
1. Unless you have cascading parameters (the list to select from parameter B
is dependent on the selection from parameter A) then you should have a query
that returns the value you want to select from.
2. Default means a single value. If you point to a dataset with multiple
values I don't know what it does except that is incorrect.
So, for instance by my take on this you want to this.
Have two datasets. One dataset that returns the list of SpindleName. The
other that retrieves the data you desire.
Select distinct SpindleName from Readings
Set both your label and value for the SpindleName parameter to the field
SpindleName from the above query.
Then your second query you have below will return what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JMac" <jsvica1@.gmail.com> wrote in message
news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
> I'm creating a report that selects data from one table. I'm trying to
> add a parameter that will allow the user to select the data based on a
> selection from one column (SpindleName). I've added this column in the
> report parameters, checked the multi-value box, set the available
> values to "from query" to the dataset & field, set the default values
> to "from query" to the same dataset & field. When I execute the query,
> I get asked for the SpindleName, but the box contains no values other
> than "NULL" and "BLANK". If I type the spindle name into the box, I
> get the results. Why isn't the list being populated with the values
> from the spindlename column ?
> Here is the query:
> SELECT StationName, SpindleName, PartId, TimeStamp
> FROM dbo.Readings
> WHERE SpindleName IN (@.SpindleName)
> I'm using SQL 2005 Express & VS 2005
> Any advice would be appreciated.
> Thank-you
> Jeff|||For your point #2, if your parameter's default value points to a dataset
("From Query") with multiple values, they will show up with all of them
selected in the drop down list. This is desirable in some cases.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23P5CZRGrIHA.4376@.TK2MSFTNGP06.phx.gbl...
>I may be wrong but it seems to me there is some confusion here. Several
>things:
> 1. Unless you have cascading parameters (the list to select from parameter
> B is dependent on the selection from parameter A) then you should have a
> query that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvica1@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>> I'm creating a report that selects data from one table. I'm trying to
>> add a parameter that will allow the user to select the data based on a
>> selection from one column (SpindleName). I've added this column in the
>> report parameters, checked the multi-value box, set the available
>> values to "from query" to the dataset & field, set the default values
>> to "from query" to the same dataset & field. When I execute the query,
>> I get asked for the SpindleName, but the box contains no values other
>> than "NULL" and "BLANK". If I type the spindle name into the box, I
>> get the results. Why isn't the list being populated with the values
>> from the spindlename column ?
>> Here is the query:
>> SELECT StationName, SpindleName, PartId, TimeStamp
>> FROM dbo.Readings
>> WHERE SpindleName IN (@.SpindleName)
>> I'm using SQL 2005 Express & VS 2005
>> Any advice would be appreciated.
>> Thank-you
>> Jeff
>|||On May 2, 10:53=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I may be wrong but it seems to me there is some confusion here. Several
> things:
> 1. Unless you have cascading parameters (the list to select from parameter= B
> is dependent on the selection from parameter A) then you should have a que=ry
> that returns the value you want to select from.
> 2. Default means a single value. If you point to a dataset with multiple
> values I don't know what it does except that is incorrect.
> So, for instance by my take on this you want to this.
> Have two datasets. One dataset that returns the list of SpindleName. The
> other that retrieves the data you desire.
> Select distinct SpindleName from Readings
> Set both your label and value for the SpindleName parameter to the field
> SpindleName from the above query.
> Then your second query you have below will return what you want.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JMac" <jsvi...@.gmail.com> wrote in message
> news:d6e84304-e8a6-489f-92df-17a10b7771d6@.25g2000hsx.googlegroups.com...
>
> > I'm creating a report that selects data from one table. I'm trying to
> > add a parameter that will allow the user to select the data based on a
> > selection from one column (SpindleName). I've added this column in the
> > report parameters, checked the multi-value box, set the available
> > values to "from query" to the dataset & field, set the default values
> > to "from query" to the same dataset & field. When I execute the query,
> > I get asked for the SpindleName, but the box contains no values other
> > than "NULL" and "BLANK". If I type the spindle name into the box, I
> > get the results. Why isn't the list being populated with the values
> > from the spindlename column ?
> > Here is the query:
> > SELECT StationName, SpindleName, PartId, TimeStamp
> > FROM dbo.Readings
> > WHERE SpindleName IN (@.SpindleName)
> > I'm using SQL 2005 Express & VS 2005
> > Any advice would be appreciated.
> > Thank-you
> > Jeff- Hide quoted text -
> - Show quoted text -
Thanks Bruce - it worked perfectly!
Regards
Jeff

Multiple Values to select all values or deselect all values

I have 3 multi select report parameters on my report. Let's say a
user selects only a few of the values in each of the report parameter
drop downs but know they want to select all of the values with a click
of option button.
Then what I would like to do is have an option button that will allow
the user to select all of the option values or deselect all of the
values for all pararmeters by just selecting True or False from the
option button. Is there a way of doing this in Reporting Services?
Thanks for any help proved!On Jul 20, 11:01 am, trevorfuller1...@.gmail.com wrote:
> I have 3 multi select report parameters on my report. Let's say a
> user selects only a few of the values in each of the report parameter
> drop downs but know they want to select all of the values with a click
> of option button.
> Then what I would like to do is have an option button that will allow
> the user to select all of the option values or deselect all of the
> values for all pararmeters by just selecting True or False from the
> option button. Is there a way of doing this in Reporting Services?
> Thanks for any help proved!
There is not something built in to SSRS already; however, you could
create the functionality. You could have a parameter that if its value
is selected as True, you could send an 'All' type value back to the
stored procedure/query that is sourcing the report and then the stored
procedure could determine what 'All' means and select everything. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Multiple set result from a stored procedure as the data source

My stored procedure generates two results:
1) declares a variable executes a view to obtain date driven data which has
a begin and end
2) selects the data associated with date driven info
When RS executes the stored procedure I only get the first set results (the
date information).
Can RS handle multiple results from a stored procedure? If so is there any
information to read.
I can change my stored procedure with a couple joins but I am curious how RS
works (I'm a newbee).
SincerelyRS only works with the first resultset.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
(the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
RS
> works (I'm a newbee).
> Sincerely|||Sorry...RS doesn't support multiple result sets.
Adrian M.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
> has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
> (the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
> RS
> works (I'm a newbee).
> Sincerely|||Thanks for the info
"Adrian M." wrote:
> Sorry...RS doesn't support multiple result sets.
> Adrian M.
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> > My stored procedure generates two results:
> > 1) declares a variable executes a view to obtain date driven data which
> > has
> > a begin and end
> > 2) selects the data associated with date driven info
> >
> > When RS executes the stored procedure I only get the first set results
> > (the
> > date information).
> >
> > Can RS handle multiple results from a stored procedure? If so is there any
> > information to read.
> >
> > I can change my stored procedure with a couple joins but I am curious how
> > RS
> > works (I'm a newbee).
> >
> > Sincerely
>
>

Multiple selects on same column, same table, one query

I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?

For example, how would combine these two selects into one query that will
list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY locationYou can use subqueries like this:

select location,
actioncount1 = (select count(actions)
from mytable T1
where T1.location = mytable.location
),
actioncount2 = (select count(actions)
from mytable T2
where mycondition is null
and T2.location = mytable.location
)
from mytable
group by location

Shervin

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> I want to get a column count several times in one query using different
> filters but can't work out how to do it - can anyone point me in the right
> direction?
> For example, how would combine these two selects into one query that will
> list the total and filtered actions:
> SELECT COUNT(actions) as actioncount, location
> FROM mytable
> GROUP BY location
> SELECT COUNT(actions) as actioncount, location
> FROM mytable
> WHERE mycondition IS NULL
> GROUP BY location|||SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
actioncount1,
COUNT(actions) AS actioncount2,
location
FROM mytable
GROUP BY location

--
David Portas
----
Please reply only to the newsgroup|||Nice! :-)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.giganews.com...
> SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
> actioncount1,
> COUNT(actions) AS actioncount2,
> location
> FROM mytable
> GROUP BY location
> --
> David Portas
> ----
> Please reply only to the newsgroup|||Thanks Shervin,
Works exactly as I need, I am indebted!
Cheers,
Jack
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:vn6fu8bgg3qt64@.corp.supernews.com...
> You can use subqueries like this:
> select location,
> actioncount1 = (select count(actions)
> from mytable T1
> where T1.location = mytable.location
> ),
> actioncount2 = (select count(actions)
> from mytable T2
> where mycondition is null
> and T2.location = mytable.location
> )
> from mytable
> group by location
> Shervin
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> > I want to get a column count several times in one query using different
> > filters but can't work out how to do it - can anyone point me in the
right
> > direction?
> > For example, how would combine these two selects into one query that
will
> > list the total and filtered actions:
> > SELECT COUNT(actions) as actioncount, location
> > FROM mytable
> > GROUP BY location
> > SELECT COUNT(actions) as actioncount, location
> > FROM mytable
> > WHERE mycondition IS NULL
> > GROUP BY location|||You're welcome my friend, but take a look at David's code. I prefer his way
:-) Subqueries are not the most efficient way to do thing in most cases. But
they are so easy to use. They are for lazy guys like me ;-)

Shervin

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:vpHcb.266$%f1.56@.newsfep1-gui.server.ntli.net...
> Thanks Shervin,
> Works exactly as I need, I am indebted!
> Cheers,
> Jack
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:vn6fu8bgg3qt64@.corp.supernews.com...
> > You can use subqueries like this:
> > select location,
> > actioncount1 = (select count(actions)
> > from mytable T1
> > where T1.location = mytable.location
> > ),
> > actioncount2 = (select count(actions)
> > from mytable T2
> > where mycondition is null
> > and T2.location = mytable.location
> > )
> > from mytable
> > group by location
> > Shervin
> > "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> > news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> > > I want to get a column count several times in one query using
different
> > > filters but can't work out how to do it - can anyone point me in the
> right
> > > direction?
> > > > For example, how would combine these two selects into one query that
> will
> > > list the total and filtered actions:
> > > > SELECT COUNT(actions) as actioncount, location
> > > FROM mytable
> > > GROUP BY location
> > > > SELECT COUNT(actions) as actioncount, location
> > > FROM mytable
> > > WHERE mycondition IS NULL
> > > GROUP BY location
> >|||Wow!
Cheers,
Jack

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.giganews.com...
> SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
> actioncount1,
> COUNT(actions) AS actioncount2,
> location
> FROM mytable
> GROUP BY location
> --
> David Portas
> ----
> Please reply only to the newsgroup

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regards
try this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go
|||Ok, I have this sp (attached). Still can't make it work.
Thanks
Regards
"vuht2000" <vuht2000@.yahoo.com> wrote in message
news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
> try this:
> create proc myproc
> param int null
> as
> declare @.sql varchar(100)
> if param is null
> set @.sql = '...' -- your first select
> else
> set @.sql = '...' -- your second select
> exec (@.sql)
> go
>
begin 666 SQLQuery.sql
M__YS`&4`= `@.`$$`3@.!3`$D`7P!.`%4`3 !,`%,`( !/`$X`#0`*`',`90!T
M`" `40!5`$\`5 !%`$0`7P!)`$0`10!.`%0`20!&`$D`10!2`" `3P!.``T`
M"@.!G`&\`#0`*``T`"@.!!`$P`5 !%`%(`( !0`%(`3P!#`$4`1 !5`%(`10`@.
M`%L`9 !B`&\`70`N`%L`0P!L`&D`90!N`'0`<P!3`&4`; !E`&,`= !#`&\`
M;0!M`&$`;@.!D`%T`#0`*`$ `0P!O`&T`< !A`&X`>0!4`'D`< !E`" `=@.!A
M`'(`8P!H`&$`<@.`H`#4`, `I`"P`#0`*`$ `4P!T`&$`= !U`',`( !V`&$`
M<@.!C`&@.`80!R`"@.`-0`P`"D`+ `-``H`0 !"`'4`<P!I`&X`90!S`',`1 !I
M`'8`:0!S`&D`;P!N`" `=@.!A`'(`8P!H`&$`<@.`H`#4`, `I`" `3@.!5`$P`
M3 `-``H`00!3``T`"@.!3`$4`5 `@.`$X`3P!#`$\`50!.`%0`( !/`$X`.P`-
M``H`1 !%`$,`3 !!`%(`10`@.`$ `<P!Q`&P`( !V`&$`<@.!C`&@.`80!R`"@.`
M,0`P`# `*0`-``H`20!&`" `0 !"`'4`<P!I`&X`90!S`',`1 !I`'8`:0!S
M`&D`;P!N`" `:0!S`" `3@.!5`$P`3 `-``H`( `@.`%,`10!4`" `0 !S`'$`
M; `@.`#T`( `G`%,`10!,`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y
M`"P`( !0`&$`<@.!E`&X`= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`
M<P!S`"P`( !#`&\`=0!N`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#
M`&\`=0!N`'0`<@.!Y`"P`( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`
M; `L`" `5P!E`&(`+ `@.`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L
M`" `20!N`'8`;P!I`&,`90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`
M8P!E`$,`;P!U`&X`= !Y`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O
M`&0`90`L`" `20!N`'8`;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`
M<P!I`&X`90!S`',`5 !Y`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A
M`'0`=0!S`$0`80!T`&4`+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`
M90!$`&$`= !E`"P`( !#`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O
M`&,`:P!E`&0`+ `@.`$(`; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`
M= `L`" `0@.!L`&\`8P!K`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D
M`%(`90!A`',`;P!N`"P`( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `
M4P!T`&$`9@.!F`$<`<@.!A`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E
M`#(`+ `@.`%,`= !A`&8`9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`
M<@.!A`&0`90`T`"P`( !#`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N
M`&$`;@.!C`&D`80!L`$,`;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`
M; !2`&4`<0!U`&D`<@.!M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.
M`&4`90!D`&4`9 `L`" `00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`
M90!.`&\`+ `@.`$,`;P!M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O
M`&X`3@.!O`"P`( !3`'4`< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`
M<@.!E`$0`90!B`'0`;P!R`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M
M`',`+ `@.`%,`= !A`'0`90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`
M=@.!E`&X`= !.`&\`= !E`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.
M`$T`80!P`% `80!T`&@.`+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`
M<@.!T`"P`( !$`&$`= !E`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O
M`&0`:0!F`&D`90!D`"P`( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`
M:0!F`&D`90!D`$(`>0`L`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.
M``T`"@.`@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`1@.!2`$\`30`@.`&0`
M8@.!O`"X`0P!L`&D`90!N`'0`<P`@.`"<`( `F`" `#0`*`" `( `@.`" `( `@.
M`" `( `@.`" `( `@.`" `)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`
M5 !Y`' `90`@.`&P`:0!K`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.
M`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`@.``T`"@.`@.`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`* !3`'0`80!T`'4`<P`@.
M`&P`:0!K`&4`( ! `%,`= !A`'0`=0!S`" `)@.`@.`"(`)0`B`"D`)P`-``H`
M10!,`%,`10`-``H`( `@.`%,`10!4`" `0 !S`'$`; `@.`#T`( `G`%,`10!,
M`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y`"P`( !0`&$`<@.!E`&X`
M= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`<P!S`"P`( !#`&\`=0!N
M`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#`&\`=0!N`'0`<@.!Y`"P`
M( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`; `L`" `5P!E`&(`+ `@.
M`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L`" `20!N`'8`;P!I`&,`
M90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`8P!E`$,`;P!U`&X`= !Y
M`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O`&0`90`L`" `20!N`'8`
M;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`<P!I`&X`90!S`',`5 !Y
M`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A`'0`=0!S`$0`80!T`&4`
M+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`90!$`&$`= !E`"P`( !#
M`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O`&,`:P!E`&0`+ `@.`$(`
M; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`= `L`" `0@.!L`&\`8P!K
M`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D`%(`90!A`',`;P!N`"P`
M( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `4P!T`&$`9@.!F`$<`<@.!A
M`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E`#(`+ `@.`%,`= !A`&8`
M9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`<@.!A`&0`90`T`"P`( !#
M`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N`&$`;@.!C`&D`80!L`$,`
M;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`; !2`&4`<0!U`&D`<@.!M
M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.`&4`90!D`&4`9 `L`" `
M00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`90!.`&\`+ `@.`$,`;P!M
M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O`&X`3@.!O`"P`( !3`'4`
M< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`<@.!E`$0`90!B`'0`;P!R
M`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M`',`+ `@.`%,`= !A`'0`
M90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`=@.!E`&X`= !.`&\`= !E
M`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.`$T`80!P`% `80!T`&@.`
M+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`<@.!T`"P`( !$`&$`= !E
M`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O`&0`:0!F`&D`90!D`"P`
M( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`:0!F`&D`90!D`$(`>0`L
M`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.`" `#0`*`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `)P!&`%(`3P!-`" `9 !B`&\`+@.!#`&P`:0!E
M`&X`= !S`" `)P`@.`"8`#0`*`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `
M)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`&P`:0!K
M`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`"8`( `B`"4`(@.`I`" `
M00!.`$0`( `G`" `)@.`-``H`( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.
M`" `( `@.`" `( `G`"@.`4P!T`&$`= !U`',`( !L`&D`:P!E`" `0 !3`'0`
M80!T`'4`<P`@.`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`-``H`"0`)
M`" `( `@.`" `( `@.`" `( `@.`" `( `G`"@.`20!$`" `20!.`" `* !3`&4`
M; !E`&,`= `@.`$,`;P!M`' `80!N`'D`20!$`" `9@.!R`&\`;0`@.`$,`;P!M
M`' `80!N`'D`0@.!U`',`:0!N`&4`<P!S`$0`:0!V`&D`<P!I`&\`; @.`@.`%<`
M2 !%`%(`10`@.`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O `&X`/0!
M`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O`&X`*0`I` "<`#0`*``T`
D"@.!%`%@.`10!#`" `* ! `',`<0!L`"D`#0`*`&<`;P`-``H`
`
end
|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
>
>

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>
>

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work :)
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>> try this:
>> create proc myproc
>> param int null
>> as
>> declare @.sql varchar(100)
>> if param is null
>> set @.sql = '...' -- your first select
>> else
>> set @.sql = '...' -- your second select
>> exec (@.sql)
>> go
>
>

Multiple Selects in a Stored Proc

I need to perform 2 selects in a stored procedure, but only return data from the second..

eg.

first select gets the proper table to search from a transaction summary table

select category from trans summary where transsummary.transid = @.transid

based on the category returned something like this

if category = 1
then tabletosearch = table1
end if
if category = 2
then tabletoseach = table 2
endif

select * from @.tabletosearch


Is this possible, or am I going about this the wrong way?...
As you probably can tell I am new at this.
Thanks folksTry something like...

select @.category = (select category from trans summary where transsummary.transid = @.transid)

if @.category = 1
then @.tabletosearch = table1
end if
if @.category = 2
then @.tabletoseach = table 2
endif

select * from @.tabletosearch|||DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||That worked great if I used SELECT * but as soon as I try to use only specific fields.....

Originally posted by achorozy

DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||I'm sorry if the example I showed was inline with your question, after all your question did have 'SELECT *'.

Since you don't tell us why it doesn't work when you list out the columns my only guess is that each table has different columns.

So why not just do this:

DECLARE @.category int

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SELECT col1, col2, col3 FROM table1

if (@.category = 2)
SELECT colA, colB, colC FROM table2
Sorry if I sound a bit off, but I've never asked a question and all of my 200+ posting has come from answering question, trying too anyways. So I get tired after awhile when someone says "It does work".
What doesn't work? What error did you get? Why doesn't it work?|||My apologies, but if it isnt obvious already, I am new to SQL and these newsgroups..again my apologies..

So far I am getting to about 7/8ths of the way thru want I need to do.
SO I will do, what I should have done the first time. give all the details

Here is what I want, I am able to get it to the first portion to work (select if...) so I won't go into more detail on that.

I need to be able to get data from 3 tables and only certain fields from each table

so

select IDCode.t1, IdDetail.t1, SalesRep.t1, SalesRep1Addr.t2, SalesRepEmail.t2, OfficeId.t2, OfficeAddr.t3, OfficeEmail.t3

S.. t1 contains a unique id from t2, and t2 contains a unique id from t3...

and obviously the the corresonding Sales Rep Detail & Office Detail...

Thanks so much for the help

Multiple return

Hi,
I have one stored procedure with multiple selects, like this:
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
Now it returns nothing, I suspected the count values.
I tried it with return... but then I only get one value.
How can I get the values as a table? With NumUsers and NumPages as
columnnames?
Thanks!hi,
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
SELECT @.NumUsers As NumUsers, @.NumPages As NumPages
"Arjen" <boah123@.hotmail.com> wrote in message
news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
> Hi,
> I have one stored procedure with multiple selects, like this:
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> Now it returns nothing, I suspected the count values.
> I tried it with return... but then I only get one value.
> How can I get the values as a table? With NumUsers and NumPages as
> columnnames?
> Thanks!
>|||Simple... ;-)
Thanks!
"arik" <arikf@.top4.com> schreef in bericht
news:%23qLX56YoFHA.2080@.TK2MSFTNGP14.phx.gbl...
> hi,
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> SELECT @.NumUsers As NumUsers, @.NumPages As NumPages
>
> "Arjen" <boah123@.hotmail.com> wrote in message
> news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
>|||Hi
It would be better to return them as output parameters as there will be less
overhead in doing so. See the topic "Returning Data Using OUTPUT Parameters"
in Books online.
John
"Arjen" wrote:

> Simple... ;-)
> Thanks!
>
>
> "arik" <arikf@.top4.com> schreef in bericht
> news:%23qLX56YoFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
>|||You can use OUTPUT parameters or a simple resultset.
CREATE PROCEDURE dbo.foo1
AS
BEGIN
DECLARE @.NumUsers INT, @.NumPages INT
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
SELECT NumUsers = @.NumUsers, NumPages = @.NumPages
END
GO
CREATE PROCEDURE dbo.foo2
AS
@.numPages INT OUTPUT,
@.numUsers INT OUTPUT
BEGIN
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
END
GO
Using the resultset is more code in the procedure (and more expensive
resource-wise) but simpler to deal with in the caller. Using Output
parameters makes the procedure itself smaller and lighter, but is a bit more
cumbersome to code from the caller.
A
"Arjen" <boah123@.hotmail.com> wrote in message
news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
> Hi,
> I have one stored procedure with multiple selects, like this:
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> Now it returns nothing, I suspected the count values.
> I tried it with return... but then I only get one value.
> How can I get the values as a table? With NumUsers and NumPages as
> columnnames?
> Thanks!
>