How can we have 'optional' parameters?

Is there anyway in SQL2005RS to have optional parameters?
Eg supposing we have a report where we want the user to be able to
select a category, but if the user makes no selection we see ALL
catagories. Is this possible.
The sql dataset is something like
select col1
from table
where category=@.category
.....is it possible to make the where clause optional? Ie if category
is NOT selected then dont do the where at all? It also seems like the
SQL2k5RS client MAKES the user select a category?
Al.Use this query instead:
select col1
from table
where category in (@.category)
and make the parameter multi-value. Your drop down will have checkboxes next
to each value, plus a selection at the top that says "All".
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"scubaal" <al@.blakes.net> wrote in message
news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.|||The other option is to have a selection called 'All'. Then in your query do
this (I use this trick a lot).
select col1 from table where (category=@.category or @.category = 'All')
This gives you an example of a query to create your category list for your
parameter.
select category as label, category as value from categorytable union
select 'All' as lable, 'All' as value
order by label
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23LobPxnpIHA.5916@.TK2MSFTNGP04.phx.gbl...
> Use this query instead:
> select col1
> from table
> where category in (@.category)
> and make the parameter multi-value. Your drop down will have checkboxes
> next to each value, plus a selection at the top that says "All".
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "scubaal" <al@.blakes.net> wrote in message
> news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
>> Is there anyway in SQL2005RS to have optional parameters?
>> Eg supposing we have a report where we want the user to be able to
>> select a category, but if the user makes no selection we see ALL
>> catagories. Is this possible.
>> The sql dataset is something like
>> select col1
>> from table
>> where category=@.category
>> .....is it possible to make the where clause optional? Ie if category
>> is NOT selected then dont do the where at all? It also seems like the
>> SQL2k5RS client MAKES the user select a category?
>> Al.
>|||My preferred variation (although the others are quite good):
select col1
from table
where category LIKE @.category
You can then define a drop down list within report parameters to
display 'All' to the user, while submitting '%' as the real parameter
value.
On Apr 24, 7:39 pm, scubaal <a...@.blakes.net> wrote:
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.|||Another option is to set the report parameter to 'Allows Nulls', and the in
you sql use:
select col1
from table
where
(category=@.category
OR
@.category is null)
-- Original Message --
From: "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Sent: Friday, April 25, 2008 10:23 AM
Subject: Re: How can we have 'optional' parameters?
> The other option is to have a selection called 'All'. Then in your query
> do this (I use this trick a lot).
> select col1 from table where (category=@.category or @.category = 'All')
> This gives you an example of a query to create your category list for your
> parameter.
> select category as label, category as value from categorytable union
> select 'All' as lable, 'All' as value
> order by label
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
> news:%23LobPxnpIHA.5916@.TK2MSFTNGP04.phx.gbl...
>> Use this query instead:
>> select col1
>> from table
>> where category in (@.category)
>> and make the parameter multi-value. Your drop down will have checkboxes
>> next to each value, plus a selection at the top that says "All".
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "scubaal" <al@.blakes.net> wrote in message
>> news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
>> Is there anyway in SQL2005RS to have optional parameters?
>> Eg supposing we have a report where we want the user to be able to
>> select a category, but if the user makes no selection we see ALL
>> catagories. Is this possible.
>> The sql dataset is something like
>> select col1
>> from table
>> where category=@.category
>> .....is it possible to make the where clause optional? Ie if category
>> is NOT selected then dont do the where at all? It also seems like the
>> SQL2k5RS client MAKES the user select a category?
>> Al.
>
"scubaal" <al@.blakes.net> wrote in message
news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.

how can sqldataobject error be traped

My database has two tables that are joined by a relation (Item category and the item). My relation will not permit the deletion of item categories if there are item referencing it.

How can that error be trapped to then show a message such as. Cannot delete item category that has item associated with it?

This is a asp.net web form with VB

this is the error I get

Server Error in '/TWGSalesInfo' Application.

---

The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseVendors_PurchaseItems". The conflict occurred in database "CustSegmentation", table "dbo.PurchaseVendors", column 'VendorPurchaseItemID'.
The statement has been terminated.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseVendors_PurchaseItems". The conflict occurred in database "CustSegmentation", table "dbo.PurchaseVendors", column 'VendorPurchaseItemID'.
The statement has been terminated.

Source Error:

Line 1904: }Line 1905: try {Line 1906: int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();Line 1907: return returnValue;Line 1908: }

Thanks

Dj

Simply add a try/catch block

try

{

int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();

return returnValue

}

catch(SqlException)

{

/// throw your custom exception here

throw new MyCustomDeleteException("Unable to delete record...");

}

and in the upper level catch only exceptions of your custom type

how can i use COUNT in my stored procedure?

hello,

i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums

How can i use the COUNT function in my stored procedure to take only first n Categories?

I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...

here is my stored procedure (i use it for two nested Repeaters)

ALTER PROCEDURESubCategoriiInCategorii

CategoryIDint)

AS

SELECTCategoryID,Name, DescriptionFROMCategoriesWHERECategoryID = @.CategoryID

ORDER BYName

SELECTp.SubCategoryID, p.Name,p.CategoryIDFROMSubCategorii p

ORDER BYp.Name

i hope you understand what i mean, thank you

Hi,

Have a look at this threadhttp://forums.asp.net/t/1138145.aspx it will tell you how to get N first rows or in otherwords N top rows.

Hope it will help you out.

Thanks and best regards,

|||

if i use the stored procedures from there i get error from the nested repeater...

i tryied this, but i get an error: invalid column name RowNumber ...why the alias don't work?

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategorii

WHEREDepartamentID = @.DepartamentIDANDRowNumber = 3

|||

Hi,

Actually I don't exactly get what are you trying to do in the above stored procedure.

Thanks and best regards,

|||

if i execute the stored procedure without the "AND" part, in the RowNumber i have values from 1 to 9 (counts how many CategoriyID's i have)

i think it work, try to execute the procedure

thank you

i just resolved it, here is the stored procedure

SELECT*FROM(

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategoriiWHEREDepartamentID = @.DepartamentID

)ASMyTable

WHERERowNumber <=5

i think is good for what i need, thank you again for help

How can I use arrays in parameters.

Following code is just select category 1 but for example What can I do if I
want to select 1 and 2?
declare @.CategoryID as int
set @.CategoryID = 1
SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)Hi
You would need to use dynamic SQL to have multiple values in a IN clause
when using a variable number of values.
Regards
Mike
"Murat BUDAK" wrote:

> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>
>|||http://www.sommarskog.se/arrays-in-sql.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Murat BUDAK" <mbudak@.islem.com> wrote in message
news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:96082D73-8AD1-4597-A0E6-55D9F16E4386@.microsoft.com...
> You would need to use dynamic SQL to have multiple values in a IN clause
> when using a variable number of values.
Mike,
You should read Erland's article that I posted the link to. You do not need
dynamic SQL for this.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Impresive. Thanks for this article :o)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Og0LJquAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Murat BUDAK" <mbudak@.islem.com> wrote in message
> news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> I
>