Is there a way to tell if a user has checked Select All for a multi-valued
parameter? I'd like my query to be different if they have.I recall from a previous post that this was not possible.
here is the link to the post.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?&query=tell+if+user+selected+all&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=e86a166d-61fa-44d8-867c-fc4f29781b5b&mid=e86a166d-61fa-44d8-867c-fc4f29781b5b
"Michelle" wrote:
> Is there a way to tell if a user has checked Select All for a multi-valued
> parameter? I'd like my query to be different if they have.
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Friday, March 30, 2012
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
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
Multi-Value Parameter
I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:
Code Snippet
SE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]
(@.Region int = Null)
AS
BEGIN
SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,
Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,
ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,
Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,
Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,
Forecast.SalesPerson_Purchaser_Code
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN
RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN
(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget
) AS Forecast INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON
Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON
Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON
Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND
Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND
Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code
WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)
END
what am i doing wrong?You can concatenate the multi values in a comma delimited string and pass the string to the stored procedure. you can create a function in SS to split the string and return a table.
Expression used to populate the parameter
=Join(Parameters! <ParameterName> .Value,", ")
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
MultiValue Parameter
Hello,
I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".
Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.
Thank you,
Itzhak
The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)
-- Robert
|||
Thank you, this will solve my issue.
I put the following expression and it works well !!
=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))
Thanks,
Itzhak
Wednesday, March 28, 2012
Multiuser Databinding in SQL Server 2000?
Hi
I am developing one multi user application in windows environment using VC++ .NET with SQL Server 2000. How Can I create dataset for multi user data updating in my SQL Server 2000. Where I can get help and sample code?
Thanks
Jose
Jose,The DataSet is an off-line data cache. The DataAdapter is used to submit the pending changes stored in the DataSet. In the DataAdapter's updating logic, you can specify your own concurrency options to control whether you want to use a "last in wins" approach with only the primary key column(s) in the WHERE clause, or use other columns in the WHERE clause to ensure you don't overwrite changes made by another user.
I hope this information proves helpful.
David Sceppa
ADO.NET Program Manager
Microsoft|||Hi
Thankyou for the nice explanation. One more question how can I write custom primary key value. for example (50710000001) like this. 5 is for year, 07 is for month, 10 is for place, and remaining is unique automatic generating number. How can I write program in SQL Server 2000.
Regards,
Jose
Monday, March 26, 2012
Multi-Select in Query Possible?
Hi NG,
I want to make a query where I want to let the user select turnovers
within a month, w
, day of w aso. and combinated, eg.
give me alle turnovers from month = December and Day of W = Monday
or just all turnovers from w 50
I want to do this within a stored procedure, with the parameters @.year,
@.month, @.w, @.day, @.region. If the value of the parameter is 0, it is
not important.
So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
Mondays in December
How can I do a Select with this on the Turnover table?
SELECT sum(Amount)
FROM _Turnover
WHERE Year = @.year
AND ...
doesnt work, because it would result Year = 0, thats incorrect.
Thank you very much
Rudiwell, you could go with the following but exec plan probably wouldnt be
efficient:
SELECT sum(Amount)
FROM _Turnover
WHERE
(Year = @.year OR @.year = 0) AND .
(Month = @.month OR @.month = 0)
and so on...
MC
<rudolf.ball@.asfinag.at> wrote in message
news:1133957507.584643.151700@.f14g2000cwb.googlegroups.com...
> Hi NG,
> I want to make a query where I want to let the user select turnovers
> within a month, w, day of w aso. and combinated, eg.
> give me alle turnovers from month = December and Day of W = Monday
> or just all turnovers from w 50
> I want to do this within a stored procedure, with the parameters @.year,
> @.month, @.w, @.day, @.region. If the value of the parameter is 0, it is
> not important.
> So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
> Mondays in December
> How can I do a Select with this on the Turnover table?
> SELECT sum(Amount)
> FROM _Turnover
> WHERE Year = @.year
> AND ...
> doesnt work, because it would result Year = 0, thats incorrect.
> Thank you very much
> Rudi
>|||Read this article
http://www.sommarskog.se/dyn-search.html
<rudolf.ball@.asfinag.at> wrote in message
news:1133957507.584643.151700@.f14g2000cwb.googlegroups.com...
> Hi NG,
> I want to make a query where I want to let the user select turnovers
> within a month, w, day of w aso. and combinated, eg.
> give me alle turnovers from month = December and Day of W = Monday
> or just all turnovers from w 50
> I want to do this within a stored procedure, with the parameters @.year,
> @.month, @.w, @.day, @.region. If the value of the parameter is 0, it is
> not important.
> So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
> Mondays in December
> How can I do a Select with this on the Turnover table?
> SELECT sum(Amount)
> FROM _Turnover
> WHERE Year = @.year
> AND ...
> doesnt work, because it would result Year = 0, thats incorrect.
> Thank you very much
> Rudi
>sql
I want to make a query where I want to let the user select turnovers
within a month, w
, day of w aso. and combinated, eg.give me alle turnovers from month = December and Day of W
= Mondayor just all turnovers from w
50I want to do this within a stored procedure, with the parameters @.year,
@.month, @.w
, @.day, @.region. If the value of the parameter is 0, it isnot important.
So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
Mondays in December
How can I do a Select with this on the Turnover table?
SELECT sum(Amount)
FROM _Turnover
WHERE Year = @.year
AND ...
doesnt work, because it would result Year = 0, thats incorrect.
Thank you very much
Rudiwell, you could go with the following but exec plan probably wouldnt be
efficient:
SELECT sum(Amount)
FROM _Turnover
WHERE
(Year = @.year OR @.year = 0) AND .
(Month = @.month OR @.month = 0)
and so on...
MC
<rudolf.ball@.asfinag.at> wrote in message
news:1133957507.584643.151700@.f14g2000cwb.googlegroups.com...
> Hi NG,
> I want to make a query where I want to let the user select turnovers
> within a month, w
, day of w aso. and combinated, eg.> give me alle turnovers from month = December and Day of W
= Monday> or just all turnovers from w
50> I want to do this within a stored procedure, with the parameters @.year,
> @.month, @.w
, @.day, @.region. If the value of the parameter is 0, it is> not important.
> So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
> Mondays in December
> How can I do a Select with this on the Turnover table?
> SELECT sum(Amount)
> FROM _Turnover
> WHERE Year = @.year
> AND ...
> doesnt work, because it would result Year = 0, thats incorrect.
> Thank you very much
> Rudi
>|||Read this article
http://www.sommarskog.se/dyn-search.html
<rudolf.ball@.asfinag.at> wrote in message
news:1133957507.584643.151700@.f14g2000cwb.googlegroups.com...
> Hi NG,
> I want to make a query where I want to let the user select turnovers
> within a month, w
, day of w aso. and combinated, eg.> give me alle turnovers from month = December and Day of W
= Monday> or just all turnovers from w
50> I want to do this within a stored procedure, with the parameters @.year,
> @.month, @.w
, @.day, @.region. If the value of the parameter is 0, it is> not important.
> So an execute could be EXEC sp_GetTurnover 0, 12, 0, 1, 0 for all
> Mondays in December
> How can I do a Select with this on the Turnover table?
> SELECT sum(Amount)
> FROM _Turnover
> WHERE Year = @.year
> AND ...
> doesnt work, because it would result Year = 0, thats incorrect.
> Thank you very much
> Rudi
>sql
Friday, March 23, 2012
Multipurpose Foreign Key
I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).
Question 1: Is this poor DB design?
Question2: If this is okay DB design (or, if I can't change this DB),
how do I perform a join?
I started writing a sproc (see "Beginnings of sproc," below), which
will definitely work, once I get it set up, but when I do these sorts
of things, I later come to find that there was a straight SQL way to
do it. So, is there a straight SQL way to do this, building joins with
CASEs, or something like that?
Thanks,
Jamie
## Structure (simplified) ##
[USER]
userID
unitID
role -- values can be 'B' or 'C' referring to [base] or [command] tbl
[BASE]
ID
NAME
[COMMAND]
ID
NAME
## Beginnings of a sproc ##
GO
USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'getSessionInfo' AND type = 'P')
DROP PROCEDURE getSessionInfo
GO
USE myDB
GO
CREATE PROCEDURE getSessionInfo
@.userID varchar(50),
@.password varchar(50)
AS
DECLARE @.myUnitType varchar(2);
SELECT @.myUnitType = unitType
FROM
[user]
WHERE userID = @.userID
AND [password] = @.password
... blah blah blahJamie Jackson (wasteNOSPAMbasket@.bigfoot.com) writes:
> I've got a field [user.unitID] that can be a foreign key on the table
> [Base] *OR* the table [Command] depending on the value of [User.role]
> (see "Structure," below).
> Question 1: Is this poor DB design?
It is certainly not the plain standard design. And the method has the
apparent advantage that you can use FOREIGN-KEY constraint to enforce
the integrity, but you need to rely on triggers.
But since I know very little of your business problem, I am hesitant
to label the design as outright bad, or even poor. What I can say, is
that had I had the problem, I would definitely have looked into a solution
that would have permitted me to use DRI, but that would definitely have
been a case of fitting the solution to the tool.
> Question2: If this is okay DB design (or, if I can't change this DB),
> how do I perform a join?
Depends a little on the output, but say you want user and name of
base or command:
SELECT u.name, u.role, rolename = coalece(b.name, c.name)
FROM users u
LEFT JOIN base b ON u.role = 'B'
AND u.unitid = b.unitid
LEFT JOIN command c ON u.role = 'C'
AND u.unitid = c.unitid
An alternative is to introduce a basecommand table, to gather common
information, but I don't know enough about your business problem to
say whether this is a good idea or not.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jamie Jackson wrote:
> I've got a field [user.unitID] that can be a foreign key on the table
> [Base] *OR* the table [Command] depending on the value of [User.role]
> (see "Structure," below).
> Question 1: Is this poor DB design?
<snip
Yes. A table represents something. In your case a User, a Base or a
Command. In your design you specify whether a User can have a relation
with a Base or not. If it can have a relation, then this means there is
a foreign key column (or a relation table) for this specific relation.
For Command you make the same decision.
So in your case, User should have one column with a foreign key
constraint to Base, and another column with a foreign key constraint to
Command. You make the relations optional by allowing NULL values in the
foreign key columns.
If this is an Object Oriented design (for example, when Command extends
from Base), then you are in trouble, because OO and RDBMS don't map very
well.
Hope this helps,
Gert-Jan|||Thanks, Erland, that SQL was a nice tutorial on filtered joins and the
coalesce function, neither of which I've ever used.
Thanks,
Jamie
On Wed, 16 Jul 2003 21:54:51 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
>Jamie Jackson (wasteNOSPAMbasket@.bigfoot.com) writes:
>> I've got a field [user.unitID] that can be a foreign key on the table
>> [Base] *OR* the table [Command] depending on the value of [User.role]
>> (see "Structure," below).
>>
>> Question 1: Is this poor DB design?
>It is certainly not the plain standard design. And the method has the
>apparent advantage that you can use FOREIGN-KEY constraint to enforce
>the integrity, but you need to rely on triggers.
>But since I know very little of your business problem, I am hesitant
>to label the design as outright bad, or even poor. What I can say, is
>that had I had the problem, I would definitely have looked into a solution
>that would have permitted me to use DRI, but that would definitely have
>been a case of fitting the solution to the tool.
>> Question2: If this is okay DB design (or, if I can't change this DB),
>> how do I perform a join?
>Depends a little on the output, but say you want user and name of
>base or command:
>
> SELECT u.name, u.role, rolename = coalece(b.name, c.name)
> FROM users u
> LEFT JOIN base b ON u.role = 'B'
> AND u.unitid = b.unitid
> LEFT JOIN command c ON u.role = 'C'
> AND u.unitid = c.unitid
>An alternative is to introduce a basecommand table, to gather common
>information, but I don't know enough about your business problem to
>say whether this is a good idea or not.
Multipul Selections in a Parameter field
I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done
From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf you are using 2005 why do you need a ctrl key for selection, instead you
can use the multiple check box to pick multiple values.
Amarnath
"Duvon Harper" wrote:
> I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done?
> From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf you are using 2005 why do you need a ctrl key for selection, instead you
can use the multiple check box to pick multiple values.
Amarnath
"Duvon Harper" wrote:
> I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done?
> From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
Multiple/Duplicate SQL Server Worker Processes
I don't even know where to begin looking... I have a page that loads multiple web user controls...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How?
What do I do to check either my code is creating the connection object properly. Thanks!
Larry
I have discovered, when I created my connection object for ExecuteNonQuery(), I forgot to Close() the connection.
Since I didn't close the connection object when I was finished... anda few new instances of the connection object was created, it created anew connection object.
In other functions, I was using the DataAdapter which opened and closedthe connection for you, so I took that feature for granted andcompletely forgot to close the connection object when doing aExecuteNonQuery().
I get a doht for the day...
Thanks for taking your time in reading...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How?
What do I do to check either my code is creating the connection object properly. Thanks!
Larry
I have discovered, when I created my connection object for ExecuteNonQuery(), I forgot to Close() the connection.
Since I didn't close the connection object when I was finished... anda few new instances of the connection object was created, it created anew connection object.
In other functions, I was using the DataAdapter which opened and closedthe connection for you, so I took that feature for granted andcompletely forgot to close the connection object when doing aExecuteNonQuery().
I get a doht for the day...
Thanks for taking your time in reading...
Wednesday, March 21, 2012
multiple where statement in a SP
Hello, a small question. I have a search page on the site where the user can search for other users, they can fill in age, gender etc
The question is, how should i extract all that information from the database, i am using SP in MS SQL 2005, C#.
For each of the options is there a possibility to press "not specified", how do i build up a query in the sql server?
Should i just
IF (@.Age1 <> '')
BEGIN
SET @.SearchString = @.SearchString + 'AND (profile_publicinfo.age = ' + @.Age1 + ')'
END
and then continue like this?
so it would look something like this:
SELECT username, gender, signupdate .... FROM profile_publicinfo FULL OUTER JOIN ..... WHERE (@.SearchString)
any ideas?
I highly recomomend Erland Sommarskog's article:Dynamic Search Conditions in T-SQL.|||Thanks, Erland Sommarskog's article solved my problems.sqlMultiple 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
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 values in a parameter
Hi
I have an input parameter 'AccountNumber' in one of my
reports. I would like to make it possible for the user to
put in multiple accountnumbers in this parameter.
I was on a reporting server courser and we did this by
creating a function that accepts the list of
accountnumbers and (comma delimited) and then transforms
it into a table with eacj accountnumber as a row in the
table. The report will then read from the table as a data
driven report.
For the life of me I cannot get this right though. Could
someone please send me the code for the function to do
this and how to code the report to look at the table.
Thanks,
TheresaTake a look at
http://www.gotdotnet.com/Community/Resources/Default.aspx?AFXPath=/Resource%5b@.ResourceId='2E882C0A-8D2B-4EAD-81BE-8E66C0941A18'%5d.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa" <theresa@.discussions.microsoft.com> wrote in message
news:20bd01c4a6e0$e409f740$a601280a@.phx.gbl...
> Hi
> I have an input parameter 'AccountNumber' in one of my
> reports. I would like to make it possible for the user to
> put in multiple accountnumbers in this parameter.
> I was on a reporting server courser and we did this by
> creating a function that accepts the list of
> accountnumbers and (comma delimited) and then transforms
> it into a table with eacj accountnumber as a row in the
> table. The report will then read from the table as a data
> driven report.
> For the life of me I cannot get this right though. Could
> someone please send me the code for the function to do
> this and how to code the report to look at the table.
> Thanks,
> Theresa
I have an input parameter 'AccountNumber' in one of my
reports. I would like to make it possible for the user to
put in multiple accountnumbers in this parameter.
I was on a reporting server courser and we did this by
creating a function that accepts the list of
accountnumbers and (comma delimited) and then transforms
it into a table with eacj accountnumber as a row in the
table. The report will then read from the table as a data
driven report.
For the life of me I cannot get this right though. Could
someone please send me the code for the function to do
this and how to code the report to look at the table.
Thanks,
TheresaTake a look at
http://www.gotdotnet.com/Community/Resources/Default.aspx?AFXPath=/Resource%5b@.ResourceId='2E882C0A-8D2B-4EAD-81BE-8E66C0941A18'%5d.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa" <theresa@.discussions.microsoft.com> wrote in message
news:20bd01c4a6e0$e409f740$a601280a@.phx.gbl...
> Hi
> I have an input parameter 'AccountNumber' in one of my
> reports. I would like to make it possible for the user to
> put in multiple accountnumbers in this parameter.
> I was on a reporting server courser and we did this by
> creating a function that accepts the list of
> accountnumbers and (comma delimited) and then transforms
> it into a table with eacj accountnumber as a row in the
> table. The report will then read from the table as a data
> driven report.
> For the life of me I cannot get this right though. Could
> someone please send me the code for the function to do
> this and how to code the report to look at the table.
> Thanks,
> Theresa
Monday, March 19, 2012
Multiple users logging into a database
I'm developing an application in ASP.Net that uses SQLServer. The database person in the team wants to have each user have a seperate login to the database so he can tell who has done what changes in the database. Is this a good practice? Or are their performance\security issues with this model?For system performance it is better to use one account (to use one connection string) and connection pooling. My solution is as follows. Create your own users and log-in system. You can save current username or id to Session. Then create a table system_events to your database. Everytime user makes something you can insert a new row that describes user action. Also, and it is maybe even better, you can use plain text logs for this.|||We implemented this feature and what we did was write a stored procedure to check the old value against the new values being updated in the database and then it writes only the changes to a seperate table designed for tracking changes. We then wrote a page to display change history by date range in a D-Grid. It works very well and there has been no noticable performance hit since implementation.
SPROC to update my database
Hope this helps.|||Thank you so much. That is what I needed.
CREATE PROCEDURE AddChangeHistory
(
@.propertyID INT,
@.userName VARCHAR(75), --Assigned in session when a user logins
@.tableName VARCHAR(75),
@.fieldName VARCHAR(80),
@.fieldChangeVal varchar(200)
)AS
DECLARE @.fieldInitVal varchar(200)
BEGIN TRAN
CREATE TABLE #tempVal
(
fieldInitVal VARCHAR(200)
)INSERT INTO #tempVal exec('SELECT TOP 1 ' + @.fieldName + ' FROM ' + @.tableName + ' WHERE propertyID=' + @.propertyID + '')
SELECT @.fieldInitVal = (SELECT top 1 * FROM #tempVal)
DROP TABLE #tempVal
IF(LOWER(@.fieldInitVal) <> LOWER(@.fieldChangeVal))
BEGIN
INSERT INTO ChangeHistory(propertyID, userName, tableName, fieldName, fieldInitVal, fieldChangeVal)
VALUES (@.propertyID, @.userName, @.tableName, @.fieldName, @.fieldInitVal, @.fieldChangeVal)
ENDIF(@.@.ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
GO
SPROC to update my database
CREATE PROCEDURE dbo.oPK_UpdateAdminHours
(
@.Role AS varchar(100),
@.UserName AS varchar(100),
@.vcManager AS varchar(50),
@.vcAssistant1 AS varchar(50),
@.vcAssistant2 AS varchar(50),
@.vcLeasingManager AS varchar(50),
@.vcMarketingDirector AS varchar(50),
@.vcLeasing1 AS varchar(50),
@.vcLeasing2 AS varchar(50),
@.vcLeasing3 AS varchar(50),
@.vcLeasing4 AS varchar(50),
@.vcLeasing5 AS varchar(50),
@.bMon_Fri AS bit,
@.cDisplaySummer_Winter AS char(1),
@.vcOfficeHoursSummerMon AS varchar(25),
@.vcOfficeHoursWinterMon AS varchar(25),
@.vcOfficeHoursSummerTue AS varchar(25),
@.vcOfficeHoursWinterTue AS varchar(25),
@.vcOfficeHoursSummerWed AS varchar(25),
@.vcOfficeHoursWinterWed AS varchar(25),
@.vcOfficeHoursSummerThur AS varchar(25),
@.vcOfficeHoursWinterThur AS varchar(25),
@.vcOfficeHoursSummerFri AS varchar(25),
@.vcOfficeHoursWinterFri AS varchar(25),
@.vcOfficeHoursSummerSat AS varchar(25),
@.vcOfficeHoursWinterSat AS varchar(25),
@.vcOfficeHoursSummerSun AS varchar(25),
@.vcOfficeHoursWinterSun AS varchar(25),
@.vcTimeZone AS varchar(50),
@.PropertyID AS INT
)
AS
--Calling the AddChangeHistory SPROC to check the values before updating the Database
exec AddChangeHistory @.propertyID, @.userName, 'vcManager',@.vcManager
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant1',@.vcAssistant1
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant2',@.vcAssistant2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasingManager',@.vcLeasingManager
exec AddChangeHistory @.propertyID, @.userName, 'vcMarketingDirector',@.vcMarketingDirector
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing1',@.vcLeasing1
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing2',@.vcLeasing2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing3',@.vcLeasing3
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing4',@.vcLeasing4
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing5',@.vcLeasing5
exec AddChangeHistory @.propertyID, @.userName, 'bMon_Fri',@.bMon_Fri
exec AddChangeHistory @.propertyID, @.userName, 'cDisplaySummer_Winter',@.cDisplaySummer_Winter
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerMon',@.vcOfficeHoursSummerMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterMon',@.vcOfficeHoursWinterMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerTue',@.vcOfficeHoursSummerTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterTue',@.vcOfficeHoursWinterTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerWed',@.vcOfficeHoursSummerWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterWed',@.vcOfficeHoursWinterWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerThur',@.vcOfficeHoursSummerThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterThur',@.vcOfficeHoursWinterThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerFri',@.vcOfficeHoursSummerFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterFri',@.vcOfficeHoursWinterFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSat',@.vcOfficeHoursSummerSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSat',@.vcOfficeHoursWinterSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSun',@.vcOfficeHoursSummerSun
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSun',@.vcOfficeHoursWinterSun
exec AddChangeHistory @.propertyID, @.userName, 'vcTimeZone ',@.vcTimeZone--Put your Update code here (removed for space)
Hope this helps.|||Thank you so much. That is what I needed.
Friday, March 9, 2012
Multiple Table Combination
I would like to create a query that would generate the output table, but I'm not sure where to start.
[USER TABLE]
userID fName lName
1 Joe Smith
2 Mike Brown
[CATEGORY TABLE]
categoryID title
1 category1
2 category2
3 category3
[USERCATEGORy TABLE]
userID categoryID
1 1
1 3
2 2
2 3
[OUTPUT TABLE]
(comma seperate rows
fName lName category1 category2 category3
Joe,Smith,x, ,x
Mike,Brown, ,x,xThis is a classic cross-tab query. There is a generic solution, and there are database engine specific solutions. The generic solution is a lot more work, and it is vulnerable to some kinds of data errors.
Can you give us a bit more background so we can help you out?
-PatP|||The rdbms is ms sql 2000. I was debating wether to post it here or in that category. It will lead to an output as an excel file, that is why I want the x's in the category, but for now i'm just interesting the sql to generate this. If a sql solution is not advisable I welcome pointers in t-sql that might help me with this.|||Try doing a search for cross-tab in the SQL Server forum. I've answered this type of question at least a dozen times there, you should be able to find a decent hit or three!
-PatP|||Books On Line has an excellent explanation and example of Crosstab queries. Look it up.
[USER TABLE]
userID fName lName
1 Joe Smith
2 Mike Brown
[CATEGORY TABLE]
categoryID title
1 category1
2 category2
3 category3
[USERCATEGORy TABLE]
userID categoryID
1 1
1 3
2 2
2 3
[OUTPUT TABLE]
(comma seperate rows
fName lName category1 category2 category3
Joe,Smith,x, ,x
Mike,Brown, ,x,xThis is a classic cross-tab query. There is a generic solution, and there are database engine specific solutions. The generic solution is a lot more work, and it is vulnerable to some kinds of data errors.
Can you give us a bit more background so we can help you out?
-PatP|||The rdbms is ms sql 2000. I was debating wether to post it here or in that category. It will lead to an output as an excel file, that is why I want the x's in the category, but for now i'm just interesting the sql to generate this. If a sql solution is not advisable I welcome pointers in t-sql that might help me with this.|||Try doing a search for cross-tab in the SQL Server forum. I've answered this type of question at least a dozen times there, you should be able to find a decent hit or three!
-PatP|||Books On Line has an excellent explanation and example of Crosstab queries. Look it up.
Saturday, February 25, 2012
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...
>
>
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...
>
>
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
>
>
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
>
>
Subscribe to:
Posts (Atom)