Help with Sproc and multi parameter

I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.

Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)OR (PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)OR (StatusISNULL)AND (ContactTypeID = @.ConTypeID)OR (ContactTypeIDISNULL)AND (Caller = @.Caller)OR (CallerISNULL)AND (SubjectLIKE @.Keyword)OR (SubjectISNULL)RETURN

You have the query correct. Your OR's and AND's are misplaced around the brackets.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASBEGINSET NOCOUNT ONSELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROMtblTasksWHERE(ClientID = @.ClientID)AND (Priority = @.PriorityOR @.PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.StatusOR @.StatusISNULL)AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)AND (Caller = @.CallerOR @.CallerISNULL)AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)SET NOCOUNT OFFEND

|||

I tried it and I'm still not getting any rows returned. I have even tried it with all parameters having a good entry.

Just to be sure;

I should be able to enter a clientID, an ActionDate range, a priority, and the other fields of the table could have any entry or null for the other parameters and get returned, YES?

|||I did not notice it but try setting the length for your parameters.|||

Besize the size, could the default values be the reason:

Like:

...

@.Statusnvarchar(50)=NULL,
@.ConTypeIDint=NULL,
@.Callernvarchar(50)=NULL,
@.Keywordnvarchar(50)=NULL

|||I set the size and still I must have an entry in every parameter. Am I missing something? I should be able to do this, right?|||

I really am stuck on this one. Can anyone offer any suggestions? Does anyone understand my problem with this?

|||

Hi

You could add default value to parameter as limno suggested.

If that doesn't work. You could try adding following code to your stored procedure and test in Sql Server Management Studio to trace each parameters.:

if (@.PriorityISNULL)begin print N'Priority IS NULL'endelsebegin print @.Priorityend--print other parameters in similar wayHope this helps.

|||

So here is what I have, listed below: I should be able to pass in a clientID and a valid date range and it should not matter what is in the other fields because I'm passing in NULL, right?

ALTER PROCEDURE dbo.eP_BindContactManagementAction(@.ClientIDint,@.Prioritysmallint=NULL,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar(50)=NULL,@.ConTypeIDint=NULL,@.Callernvarchar(50)=NULL,@.Keywordnvarchar(50)=NULL)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)AND (ContactTypeID = @.ConTypeID)AND (Caller = @.Caller)AND (SubjectLIKE @.Keyword)RETURN

I appreciate any help,|||

You WHERE clause has problem to handle NULL values: Please try this one fromndinakar

SELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,
ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROMtblTasks
WHERE(ClientID = @.ClientID)
AND (Priority = @.PriorityOR @.PriorityISNULL)
AND (ActionDateBETWEEN @.TStartAND @.TEnd)
AND (Status = @.StatusOR @.StatusISNULL)
AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)
AND (Caller = @.CallerOR @.CallerISNULL)
AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)

|||

Another question: could you show us your datasource control code too? if you are using SQLDatasource control, you may need to set this CancelSelectOnNullParameter="false".

Just another shot in dark.

|||

Here is the code, it will return a record if I make an entry for all parameters. But if I leave one blank no records are returned.

Sub BindData() Session("TaskStart") =Me.BasicDatePickerHStart.SelectedValue Session("TaskEnd") =Me.BasicDatePickerHEnd.SelectedValueDim TStartAs String = Session("TaskStart")Dim TEndAs String = Session("TaskEnd")Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("SQL2ConnectionString").ConnectionString'If user unchecks use due date sproc.Dim strSprocAs String strSproc ="BindContactManagementAction" MyConnection =New SqlConnection(ConnectStr) MyCommand =New SqlCommand(strSproc, MyConnection) MyCommand.CommandType = CommandType.StoredProcedureDim ClientIDParamAs New SqlParameter("@.ClientID", SqlDbType.Int, 4) MyCommand.Parameters.Add(ClientIDParam) ClientIDParam.Value = Session("lgClientID")Dim TaskStartParamAs New SqlParameter("@.TStart",Me.BasicDatePickerHStart.SelectedValue) MyCommand.Parameters.Add(TaskStartParam) TaskStartParam.Value =Me.BasicDatePickerHStart.SelectedValueDim TaskEndParamAs New SqlParameter("@.TEnd",Me.BasicDatePickerHEnd.SelectedValue) MyCommand.Parameters.Add(TaskEndParam) TaskEndParam.Value =Me.BasicDatePickerHEnd.SelectedValueIf String.IsNullOrEmpty(Me.KeyWordText.Text)Then Dim KeywordParamAs New SqlParameter("@.Keyword", DBNull.Value) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)Else Dim KeywordParamAs New SqlParameter("@.Keyword",Me.KeyWordText.Text) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then Dim StatusParamAs New SqlParameter("@.Status", DBNull.Value) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)Else Dim StatusParamAs New SqlParameter("@.Status",Me.StatusSearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)End If If String.IsNullOrEmpty(Me.PrioritySearchDrop.SelectedItem.Text)Then Dim PriorityParamAs New SqlParameter("@.Priority", DBNull.Value) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)Else Dim PriorityParamAs New SqlParameter("@.Priority",Me.PrioritySearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)End If If String.IsNullOrEmpty(Me.CallerTextSearch.Text)Then Dim CallerParamAs New SqlParameter("@.Caller", DBNull.Value) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)Else Dim CallerParamAs New SqlParameter("@.Caller",Me.CallerTextSearch.Text) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)End If If String.IsNullOrEmpty(Me.ContactTypeSearchDrop.SelectedValue)Then Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID", DBNull.Value) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)Else Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID",Me.ContactTypeSearchDrop.SelectedValue) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)End If MyConnection.Open()Dim DSAs SqlDataReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) MyEditDataGrid.DataSource = DS MyEditDataGrid.DataBind()End Sub

|||

We have to find where the problem is:

1. Whether the Stored Procedure is running OK or not in your database?

2. If the sp can return records with limited parameters, we can focus on the code part.

PS:(this is a version I tested on my db which works)( By the way, I would use field name to Name parameters to avoid confusion, but this is not the problem here)

ALTER

PROCEDURE [dbo].[eP_BindContactManagementAction]

(

@.ClientID

int,

@.Priority

smallint=NULL,

@.TStart

datetime,

@.TEnd

datetime,

@.Status

nvarchar(50)=NULL,

@.ConTypeID

int=NULL,

@.Caller

nvarchar(50)=NULL,

@.Keyword

nvarchar(50)=NULL)

AS

SELECT

Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,ReminderDate,

Reminder

, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDate

FROM

tblTasks

WHERE

(ClientID= @.ClientID)

AND

(Priority= @.PriorityOR @.PriorityISNULL)

AND

(ActionDateBETWEEN @.TStartAND @.TEnd)

AND

(Status= @.StatusOR @.StatusISNULL)

AND

(ContactTypeID= @.ConTypeIDOR @.ConTypeIDISNULL)

AND

(Caller= @.CallerOR @.CallerISNULL)

AND

(SubjectLIKE @.KeywordOR @.KeywordISNULL)

END

|||

Ok, progress the sp is working in the database, I tested it in VS 2005 and it worked, so it must be in my code.

Any thoughts from my earlier post?

|||

I changed all my parameters to the code type below and it is working now:

I very much appreciate your help!

If String.IsNullOrEmpty(Me.KeyWordText.Text)Then MyCommand.Parameters.AddWithValue("@.Keyword", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Keyword",Me.KeyWordText.Text)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then MyCommand.Parameters.AddWithValue("@.Status", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Status",Me.StatusSearchDrop.SelectedItem.Text)End If

sql

Help with SP syntax

I have the stored proc. below and I'm passing two
parameters. What I'm trying to do is if either one of
the parameters is equal to "All", then change the value
of the paramter to an empty string or set another
variable to an empty string. SQL doesn't like the code I
have below. Please help.
CREATE PROCEDURE GetUSFSUsers
(
@.Role nvarchar(100),
@.Unit nvarchar(20)
)
AS
Declare @.Role2 nvarchar(100)
Declare @.Unit2 nvarchar(20)
If @.Role = 'All'
@.Role2 = ''
Else
@.Role2 = @.Role
If @.Unit = 'All'
@.Unit2 = ''
Else
@.Unit2 = @.UnitYou can change it to
if @.role = 'all'
set @.role2 = ''
else
set @.role2 = @.role
if @.unit = 'all'
set @.unit2 = ''
else
set @.unit2 = @.unit
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help with setting Algorithm Paramteres

I was walking through the Text Mining example - which at one step required me to set Algorithm Parameters - MAXIMUM_OUTPUT_ATTRIBUTES=0. When I tried that the project would not build giving an error -
Error (Data mining): The 'MAXIMUM_INPUT_ATTRIBUTES' data mining parameter is not valid for the 'XYZ' model.

I was getting the same error when I tried to set it for Microsoft_neural_netowrk - Hidden_Node_ratio. When I do a properties from "set Algorithm Properties" from Mining Model, I do not see these properties set as default.

I have installed SQLServer 2005 Standard Edition Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00

Any help would be much appreciated.

Thanks
Rajeev Gupta

These parameters are only available in the Enterprise Edition.|||

Do you plan to add these features in future versions of standard edition.

Neural Network modeling suffers greatly as we cannot add more layers/neurons to the model. Are there any hacks...

Thanks
Rajeev Gupta

|||Then are we to assume the Text Mining tutorials are only useful for the Enterprise Edition?

Help with setting Algorithm Paramteres

I was walking through the Text Mining example - which at one step required me to set Algorithm Parameters - MAXIMUM_OUTPUT_ATTRIBUTES=0. When I tried that the project would not build giving an error -
Error (Data mining): The 'MAXIMUM_INPUT_ATTRIBUTES' data mining parameter is not valid for the 'XYZ' model.

I was getting the same error when I tried to set it for Microsoft_neural_netowrk - Hidden_Node_ratio. When I do a properties from "set Algorithm Properties" from Mining Model, I do not see these properties set as default.

I have installed SQLServer 2005 Standard Edition Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00

Any help would be much appreciated.

Thanks
Rajeev Gupta

These parameters are only available in the Enterprise Edition.|||

Do you plan to add these features in future versions of standard edition.

Neural Network modeling suffers greatly as we cannot add more layers/neurons to the model. Are there any hacks...

Thanks
Rajeev Gupta

|||Then are we to assume the Text Mining tutorials are only useful for the Enterprise Edition?

Help with query field parameters

Hi everyone,

within one of my reports I would like to take an input parameter, feed this into a dataset which is then used to populate the dataset of the query used in the main body of the report. It won't allow me todo this, could you guys offer any advice on how this may be possible?

E.g

Sub Query:

@.personIds = SELECT personid FROM people WHERE name = @.name;

Main Report Query

SELECT * FROM orders WHERE personId IN (@.personIds)

I cannot change the "main report query" as this is actually a stored procedure from an external application vendor. Please help.

Kind regards

Taz

Is the @.Name the parameter in your report?

If so, then set up the population of that parameter up with the SQL:

Select PersonID, Name from People

with the value = PersonID and the label = Name

and then in your main query, the personid is returned as the parameter value.

Hope that helps.

BobP

|||

Hi Bobp, thanks for the response.

The problem I have is that the SQL (SELECT * FROM people) will return 100s of rows. I dont want to populate the drop down with all of these, instead I want the drop down to have generic options (i.e. "David", "James") which when selected perform the query to get ther relvant IDs and then populate the query that the main report is based on.

Does that clarify at all?

Thanks again for your suggestion, do you have any further ideas?

Kind regards
Taz

|||

Yes, actually... one more idea...

Create parameter named @.Name. This is a string, user type in.

Then create another parameter named @.personIDs. This should be a string, with hidden and multi value selected.

Create a dataset, using the following SQL:

Select personid from people where name like '%' + @.name + '%'

Use this new data set as the Default Values Query for the @.PersonIDs parameter.

Then you can pass personid to your main query like this:

SELECT * FROM orders WHERE personId IN (@.personIds)

Make sure that in the parameter list, the @.Name parameter is first in the list.

Let me know if that works for you. I have tested, and am using that approach in several reports where the select list is too long.

Another way to do this would be to make the @.PersonIDs NOT hidden, and also use the new dataset to populate the available values of it, and allow the user to select an individual. This way, if the user types in DAVID, a second parameters asks the user to select from a list of '%David%'

BobP

|||

Bobp,

fantastic, this worked perfectly! many thanks for your help here.

One further question in regards to thw SQL command IN. i.e.

SELECT * FROM people WHERE peopleid IN(1,2,3,4);

is it possible to easily negate the IN within reporting services filters?

I have something like

=Fields!Name.value IN =Parameters!Names.value

How can I negate this?

Any help appreciated, however what you have done so far is fantastic enough! :)

Kind regards
Taz

|||

No problem at all...

I am not sure what you are trying to do with the filter... Could you give some more detail?

by default, the names that come back should be like the names parameter.

Thanks

BobP

|||

BobP - BIM wrote:

No problem at all...

I am not sure what you are trying to do with the filter... Could you give some more detail?

by default, the names that come back should be like the names parameter.

Thanks

BobP

Well, I am finding that I am overloading the input parameter for my stored procedure. It has a limit of 4000 characters, and my dynamic SQL is along the region of 5200 characters.

I can generate the SQL for the stored proc in 2 ways, either get the clients who have bought something (small list) or get the clients who haven't bought something (very long list). The former works fine (small list) however when i try to send in the big list it exceeds the limit of the stored proc and thus falls over.

Thus, I thought maybe I could instead return everything and then create a filter on my dataset where I do something like

Expression:
=Fields!ClientName.Value

Operator:
IN

Value:

=Parameters!ResultsOfTheSQLQueryWeCreatedBefore.Value

This works, i.e. show all the rows where the ClientName appears in the Parameter list. I was wonder if there was a simple way of making it show all the rows where the ClientName does NOT appear in the Parameter list without loading the huge list instad (as this would be slow).

Man this is a difficult one to explain. I hope I was succesful. Thanks for your time Bob!

Taz

Help with Query Field Parameters

Hi everyone,
within one of my reports I would like to take an input parameter, feed this
into a dataset which is then used to populate the dataset of the query used
in the main body of the report. It won't allow me todo this, could you guys
offer any advice on how this may be possible?
E.g
Sub Query:
@.personIds = SELECT personid FROM people WHERE name = @.name;
Main Report Query
SELECT * FROM orders WHERE personId IN (@.personIds)
I cannot change the "main report query" as this is actually a stored
procedure from an external application vendor. Please help.
Kind regards
TazWhat is your exact problem? Can you run the stored procedure from Query
Analyzer?
As I said, I don't really understand the issue but if you are trying to do a
master detail report you should be using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23TPfRN95GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> within one of my reports I would like to take an input parameter, feed
> this into a dataset which is then used to populate the dataset of the
> query used in the main body of the report. It won't allow me todo this,
> could you guys offer any advice on how this may be possible?
> E.g
> Sub Query:
> @.personIds = SELECT personid FROM people WHERE name = @.name;
> Main Report Query
> SELECT * FROM orders WHERE personId IN (@.personIds)
> I cannot change the "main report query" as this is actually a stored
> procedure from an external application vendor. Please help.
> Kind regards
> Taz
>|||Sorry, let me try again.
In my main report I have a drop down parameter with 2 values, lets say its
called "Locations" with 2 posible values
Locations:
Leeds
Wakefield
Based on this I need to run a query that will return all the customers
within that region, i.e.
SELECT customerids FROM customer WHERE location=@.location
I would then like to feed the results of this query into the query used in
the dataset that populates the report (which is actually a stored procedure
not a query). i wanted todo this by setting the Parameter within thr dataset
= to the results of the above query (which I tried to create as a seperate
dataset).
You see, the stored procedure is hard coded with a ... "WHERE IN(@.params)" ,
and I would like my above query to populate the @.params parameter for me.
I hope this clarifies in some way, if not, i can have another bash. Finally,
the sceanrio i gave above is not the one I have, however it represents the
same problem. Such, i cant change it.
Thanks
Taz
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7Jffe95GHA.3952@.TK2MSFTNGP04.phx.gbl...
> What is your exact problem? Can you run the stored procedure from Query
> Analyzer?
> As I said, I don't really understand the issue but if you are trying to do
> a master detail report you should be using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23TPfRN95GHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> within one of my reports I would like to take an input parameter, feed
>> this into a dataset which is then used to populate the dataset of the
>> query used in the main body of the report. It won't allow me todo this,
>> could you guys offer any advice on how this may be possible?
>> E.g
>> Sub Query:
>> @.personIds = SELECT personid FROM people WHERE name = @.name;
>> Main Report Query
>> SELECT * FROM orders WHERE personId IN (@.personIds)
>> I cannot change the "main report query" as this is actually a stored
>> procedure from an external application vendor. Please help.
>> Kind regards
>> Taz
>

Help with query - anyone?

I have 3 tables,

first table is
CRT with crtID and crtNAME parameters, second table is
CRTP with crtpID and crtpNAME parameters and third is
FORM with crtpID and crtID parameters.

Third table joins parameters from other two whit their keys - crtpID
and crtID.

My question is how to make query to list crtNAME and crtpNAME in my
results??

For example FORM contains,

crtpID, crtID
001, 005
002, 005
003, 007
etc.

I want to list names associated to those ID-s joined in third table...

I hope that you understand me - thanks very much for any help.SELECT CRT.crtNAME, CRTP.crtpNAME
FROM CRT
JOIN FORM
ON CRT.crtID = Form.crtID
JOIN CRTP
ON Form.crtpID = CRTP.crtpID

Roy Harvey
Beacon Falls, CT

On 18 Aug 2006 00:57:21 -0700, "legenda" <dispet@.gmail.comwrote:

Quote:

Originally Posted by

>I have 3 tables,
>
>first table is
>CRT with crtID and crtNAME parameters, second table is
>CRTP with crtpID and crtpNAME parameters and third is
>FORM with crtpID and crtID parameters.
>
>Third table joins parameters from other two whit their keys - crtpID
>and crtID.
>
>My question is how to make query to list crtNAME and crtpNAME in my
>results??
>
>
>For example FORM contains,
>
>crtpID, crtID
>001, 005
>002, 005
>003, 007
>etc.
>
>I want to list names associated to those ID-s joined in third table...
>
>I hope that you understand me - thanks very much for any help.

Help with optional parameter query with IN statements

I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn’t not pass in the parameter.

I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.

One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it’s run! Not a good solution, but maybe it’s the best I can do . . .

I have tried many different approaches using different functions, etc. but I’ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:

1. To add the parameter to the WHERE clause and test for NULL I’ve used the COALESCE function such as “WHERE table.fieldname = COALESCE(@.Param, table.fieldname)”. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.

2. To handle multiple items, for example, if @.Param = ‘3,7,98’ (essentially, a csv separated list of keys)

Code Snippet

WHERE table.fieldname IN(COALESCE(@.Param, table.fieldname))

doesn’t work because @.Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I’m using

Code Snippet

ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))

which works brilliantly in my WHERE statement AS LONG AS @.Param ISN’T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?

I’ve tried

Code Snippet

WHERE @.Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))

and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was

Code Snippet

ISNULL (table.fieldname, 0) IN (CASE WHEN @.Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@.Param,’,’)))

This fails with “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression” due to the multiple values in the parameter. (I can’t understand why the line without the CASE statement works, but the CASE line doesn’t!)

Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!

First of all, I think we need more background information...

-What is the end goal of the query?

-How are users interacting with the application? (reporting services, asp.net, etc.)

-What is the database structure like? How many tables are being joined, Approx size of tables

-Are all 17 parameters needed? Perhaps you could develop a set of linked reports that give the user more detailed information as they drill deeper.

As far as performance, I wouldn't worry too much about having the query compiled every time, this really doesn't take that long. Query performance in most cases comes from proper indexing.

|||

Anthony,

Thank you for your response!

Here's the info:

- I migrated an Access Jet database to SQL but still use Access as the client.

- Query is the source for an Access report that is the most comprehensive report in the program. The user has sorting and grouping options in the report that allow them to view most everything they need from one report.

- The database structure is very good, and the tables aren't huge, the average size of a table is 15k records, but this query touches approximately 1/4 of all tables in the db. 20 tables are being joined and the largest table of the 20 has 28k records.

- This query is inserting all the values into a temp table which I then run my report queries on. Here's ALL the code with some of the WHERE clause: (I've put a couple of different multi-item parameter techniques in the WHERE clause- of course, neither of them work.)

Code Snippet

ALTER PROCEDURE [dbo].[spAG_ProjectStatusTESTFunction]

-- Add the parameters for this sp here

@.AGProjectKey varchar(255) = NULL,

@.OpenClosed bit = NULL,

@.ClientKey varchar(200) = NULL,

@.ProdCoKey varchar(255) = NULL,

@.ProjectTitle varchar(100) = NULL,

@.AgentKey varchar(50) = NULL,

@.GenreKey varchar(200) = NULL,

@.ProjectTypeKey varchar(255) = NULL,

@.ProjectType2Key varchar(255) = NULL,

@.DirectorKey varchar(255) = NULL,

@.ProdKey varchar(255) = NULL,

@.ExecProdKey varchar(255) = NULL,

@.ContactKey varchar(255) = NULL,

@.ProjectStatus varchar(50) = NULL,

@.Distributor varchar(50) = NULL,

@.MusSup varchar(255) = NULL,

@.NonClientFinalOut varchar(255) = NULL

AS

BEGIN

DECLARE @.sSQL varchar(8000);

DECLARE @.sWHERE varchar(8000);

DECLARE @.sORDERBY varchar(8000);

CREATE TABLE #rAG_ProjectStatusPre

(

[CoProdCompany] varchar(255) NULL, [ProductionCompanyDesc] varchar(500) NULL,

[AGProjectKey] int NOT NULL, [ProjectTitle] nvarchar(100) NULL, [ProdOfficeAddr1] nvarchar(50) NULL,

[ProdOfficeAddr2] nvarchar(50) NULL, [ProdOfficeAddr3] nvarchar(50) NULL, [ProdOfficeCity] nvarchar(15) NULL,

[ProdOfficeState] nvarchar(2) NULL, [ProdOfficeFax] nvarchar(20) NULL, [ProdOfficePhone] nvarchar(20) NULL,

[ProdOfficeZip] nvarchar(9) NULL, [ClientKey] int NULL, [FirstName] nvarchar(15) NULL, [LastName] nvarchar(20) NULL,

[AgentKey] int NULL, [AgentName] nvarchar(30) NULL, [Producer] nvarchar(30) NULL,

[ExecProducer] nvarchar(30) NULL, [Director] nvarchar(30) NULL, [Contact] nvarchar(30) NULL,

[Cast] nvarchar(30) NULL, [Source] nvarchar(50) NULL, [ProjectTypeDesc] nvarchar(50) NULL,

[ProjectType2Desc] nvarchar(50) NULL, [GenreDesc] nvarchar(50) NULL, [Network] nvarchar(50) NULL,

[ProductionStatusDesc] nvarchar(50) NULL, [ClosedProject] bit NULL, [ClosedDate] datetime NULL,

[MusicSupervisor] nvarchar(255) NULL, [Location] nvarchar(255) NULL, [ProdStartDate] nvarchar(50) NULL,

[WrapShootDate] nvarchar(50) NULL, [SpottingDate] nvarchar(50) NULL, [RecordingDate] nvarchar(50) NULL,

[MixingDate] nvarchar(50) NULL, [DeliveryDate] nvarchar(50) NULL, [ReleaseDate] nvarchar(50) NULL,

[FinalDub] nvarchar(50) NULL, [FilmTVBudget] nvarchar(50) NULL, [MusicBudget] nvarchar(50) NULL,

[Synopsis] nvarchar(255) NULL, [ProjectStatus] nvarchar(255) NULL, [NonClientFinalOut] nvarchar(255) NULL

);

BEGIN

SET @.sORDERBY = '';

SET @.sWHERE = '';

SET @.sSQL = '';

INSERT INTO #rAG_ProjectStatusPre (CoProdCompany, ProductionCompanyDesc, AGProjectKey, ProjectTitle,

ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,

ClientKey, [FirstName], [LastName], AgentKey, AgentName, Producer, ExecProducer, Director, Contact, [Cast], Source,

ProjectTypeDesc, ProjectType2Desc, GenreDesc, Network, ProductionStatusDesc, ClosedProject, ClosedDate, MusicSupervisor,

Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,

ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut)

SELECT tProductionCompanies_1.ProductionCompanyDesc AS CoProdCompany,

tProductionCompanies.ProductionCompanyDesc, tAGProjects.AGProjectKey,

tAGProjects.ProjectTitle, tAGProjects.ProdOfficeAddr1, tAGProjects.ProdOfficeAddr2,

tAGProjects.ProdOfficeAddr3, tAGProjects.ProdOfficeCity, tAGProjects.ProdOfficeState,

tAGProjects.ProdOfficeZip, tAGProjects.ProdOfficeFax, tAGProjects.ProdOfficePhone,

tClients.ClientNumber AS [ClientKey], tClients.FirstName AS [FirstName],

tClients.LastName AS [LastName], tAgents.AgentKey, tAgents.AgentName,

[tMailList].[FirstName] + ' ' + [tMailList].[LastName] AS Producer,

[tMailList_1].[FirstName] + ' ' + [tMailList_1].[LastName] AS ExecProducer,

[tMailList_2].[FirstName] + ' ' + [tMailList_2].[LastName] AS Director,

[tMailList_3].[FirstName] + ' ' + [tMailList_3].[LastName] AS Contact,

[tMailList_4].[FirstName] + ' ' + [tMailList_4].[LastName] AS [Cast],

tProjectSources.Source, tProjectTypes.ProjectTypeDesc, tProjectType2s.ProjectType2Desc,

tGenre.GenreDesc, tAGProjects.Network, tProductionStatus.ProductionStatusDesc, tAGProjects.ClosedProject,

tAGProjects.ClosedDate, tAGProjects.MusicSupervisor, tAGProjects.Location, tAGProjects.ProdStartDate,

tAGProjects.WrapShootDate, tAGProjects.SpottingDate, tAGProjects.RecordingDate,

tAGProjects.MixingDate, tAGProjects.DeliveryDate, tAGProjects.ReleaseDate, tAGProjects.FinalDub,

tAGProjects.FilmTVBudget, tAGProjects.MusicBudget, tAGProjects.Synopsis,

tAGProjects.ProjectStatus, tAGProjects.NonClientFinalOut

FROM (tMailList RIGHT JOIN (tClients RIGHT JOIN ((((((((((((((tAGProjects LEFT JOIN tProductionCompanies ON

tAGProjects.ProductionCompanyKey = tProductionCompanies.ProductionCompanyKey) LEFT JOIN

tProductionCompanies AS tProductionCompanies_1 ON tAGProjects.CoProductionCompanyKey =

tProductionCompanies_1.ProductionCompanyKey) LEFT JOIN tProjectTypes ON tAGProjects.ProjectTypeKey =

tProjectTypes.ProjectTypeKey) LEFT JOIN tProjectType2s ON tAGProjects.Type2Key = tProjectType2s.ProjectType2Key)

LEFT JOIN tGenre ON tAGProjects.GenreKey = tGenre.GenreKey) LEFT JOIN tProductionStatus ON

tAGProjects.ProductionStatusKey = tProductionStatus.ProductionStatusKey) LEFT JOIN

(tProjectAgents LEFT JOIN tAgents ON tProjectAgents.AgentKey = tAgents.AgentKey) ON

tAGProjects.AGProjectKey = tProjectAgents.AGProjectKey) LEFT JOIN (tProjectCast LEFT JOIN

tMailList AS tMailList_4 ON tProjectCast.ContactKey = tMailList_4.MailListKey) ON

tAGProjects.AGProjectKey = tProjectCast.AGProjectKey) LEFT JOIN tProjectClients ON

tAGProjects.AGProjectKey = tProjectClients.AGProjectKey) LEFT JOIN (tProjectContacts LEFT JOIN

tMailList AS tMailList_3 ON tProjectContacts.ContactKey = tMailList_3.MailListKey) ON

tAGProjects.AGProjectKey = tProjectContacts.AGProjectKey) LEFT JOIN (tProjectDirectors LEFT JOIN

tMailList AS tMailList_2 ON tProjectDirectors.ContactKey = tMailList_2.MailListKey) ON

tAGProjects.AGProjectKey = tProjectDirectors.AGProjectKey) LEFT JOIN (tProjectExecProducers LEFT JOIN

tMailList AS tMailList_1 ON tProjectExecProducers.ContactKey = tMailList_1.MailListKey) ON

tAGProjects.AGProjectKey = tProjectExecProducers.AGProjectKey) LEFT JOIN tProjectProducers ON

tAGProjects.AGProjectKey = tProjectProducers.AGProjectKey) LEFT JOIN tProjectSources ON

tAGProjects.AGProjectKey = tProjectSources.AGProjectKey) ON tClients.ClientNumber = tProjectClients.ClientKey) ON tMailList.MailListKey = tProjectProducers.ContactKey)

WHERE tAGProjects.ClosedProject = COALESCE(@.OpenClosed, tAGProjects.ClosedProject)

AND @.AGProjectKey IS NULL OR ISNULL(tAGProjects.AGProjectKey, 0) IN (SELECT Value FROM dbo.fn_Split(@.AGProjectKey,','))

AND tAGProjects.ProjectTitle LIKE COALESCE(@.ProjectTitle, tAGProjects.ProjectTitle)

AND ISNULL(tClients.ClientNumber, 0) IN(CASE WHEN @.ClientKey IS NULL THEN ISNULL(tClients.ClientNumber, 0) ELSE @.ClientKey END)

END

BEGIN

SELECT DISTINCT AGProjectKey, ProjectTitle, FirstName, ClientKey, LastName, ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,

Network, ClosedProject, ClosedDate, MusicSupervisor, Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,

ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut, ProjectTypeDesc, ProjectType2Desc,

GenreDesc, ProductionCompanyDesc, CoProdCompany, ProductionStatusDesc

FROM #rAG_ProjectStatusPre

END

DROP TABLE #rAG_ProjectStatusPre

END

|||

Try:

case

when (@.Param is null) or (table.fieldname is null) then 1

else case when exists(select * from dbo.fn_split(@.Param, ',') as t where t.value = table.fieldname) then 1 else 0 end

end = 1

Not sure it will give good performance.

Here is a very good article about this theme.

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

|||

Okay, thanks for the follow-up information.

The previous poster's code will work, but in my opinion it makes the code rather confusing for future developers. I would code it to dynamically build the where clause.

As the table get larger performance will almost certainly become an issue due to the large number of joins. Make sure that you at least index the larger tables on the join columns and if possible create a 'covered index'. If you are unaware what that is you can learn more on books-online or try googling it.

You could code the where clause something like this...

declare @.where varchar(1000)

set @.where='where '

if @.AGProjectKey is not null

begin

set @.where=@.where + @.AGProject + ' and '

end

if @.ClientKey is not null

begin

set @.where=@.where + @.ClientKey + ' and '

end

Good luck. Let me know if you have any more questions.