Help with sproc with one parameter that can contain multiple values

I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>

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_executesql and return parameter

I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.

While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.

So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:

 Public Function totalrecordsbysql(list as arraylist) as integer
 dim RetVal as new integer
 dim querystring as string
 Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
 Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
 cmd.commandtype = commandtype.storedprocedure
 dim mydr as SqlDataReader
 cmd.Parameters.add("@.TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
 cmd.Parameters.add("@.age",sqldbtype.int).value = 18
 cn.Open()
 try
 mydr=cmd.executereader()
 catch e as sqlexception
 dim err as sqlerror
 dim strErrorString as string
 for each err in e.Errors
 strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
 trace.write("sqlexception",strErrorString)
 Next
 finally
 RetVal = cmd.parameters("@.TotalRecords").value
 end try
 Return RetVal
 cn.close()
 End Function

Now here is the stored procedure:

CREATE PROCEDURE SimpleDynProfileCount
@.age int,
@.TotalRecords int output
AS
Declare @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
select @.sql = 'select @.xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@.sql, 16, 1)
IF @.age > 0
Select @.sql = @.sql + ' AND age > @.xage '
Select @.paramlist = '@.xage int, @.xTotalRecords int output'
Execute sp_executesql @.sql,@.paramlist,@.age,@.xTotalRecords = @.TotalRecords output
select @.TotalRecords
GO

Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11Your code works fine for me in Query Analyzer -- with one exception. I needed to change the double forward slashes (//) to double dashes (--) in front of the RAISERROR.

You might also place a SET NOCOUNT ON at the top of your stored procedure. This will suppress the information "xx items selected" messages and will avoid having them returned inadvertently as a resultset.

Terri|||That's what is so strange: I don't receive error messages, just the wrong answer (zero). The double dashes are just artistic license: I didn't remember the comment tag. I just eliminate the line, altogether, in production.|||Try adding SET NOCOUNT ON after your AS at the top of the stored procedure.

Terri|||I have added the statement, but the results are the same. Works fine with the RAISERROR statement, but returns zero without the statement.

I don't want to just leave the RAISERROR statement active. It will come back to haunt me later.

rod|||I have resolved the syntax.

I made a mistake in the vb.net when I used a datareader to retrieve the output value!

Instead of:

mydr=cmd.executereader()

the syntax should be:

cmd.executeNonQuery()

Using this syntax I removed from the Stored Procedure, the RAISERROR statement and the last SELECT @.totalrecords.

After doing all that, everything worked as expected.

-rod

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 parameter in WHERE

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.ID

this works fine. its simple. I have this in a report and I would like
to add a parameter @.p1 to add to the query

where Product_Sales.Order_Amount >= @.p1

my problem is that if @.p1 is null I want to retrieve all recorders in
Product_List. any ideas.

Do you need more clarification.Try:

where Product_Sales.Order_Amount =
CASE
when @.p1 is null then Product_Sales.Order_Amount
else @.p
END

--

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"knowzero" <isacp@.bhphoto.comwrote in message
news:1174504953.877881.232990@.b75g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.ID
>
this works fine. its simple. I have this in a report and I would like
to add a parameter @.p1 to add to the query
>
where Product_Sales.Order_Amount >= @.p1
>
my problem is that if @.p1 is null I want to retrieve all recorders in
Product_List. any ideas.
>
Do you need more clarification.
>

|||On 21 Mar 2007 12:22:38 -0700, knowzero wrote:

Quote:

Originally Posted by

>my query:
>select Product_List.*, Product_Sales.*
>from Product_List
>left outer join Product_Sales on Product_List.ID = Product_Sales.ID
>
>this works fine. its simple. I have this in a report and I would like
>to add a parameter @.p1 to add to the query
>
>where Product_Sales.Order_Amount >= @.p1
>
>my problem is that if @.p1 is null I want to retrieve all recorders in
>Product_List. any ideas.

Hi knowzero,

WHERE Product_Sales.Order_Amount >= COALESCE(@.p1,
Product_Sales.Order_Amount)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||knowzero wrote:

Quote:

Originally Posted by

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.ID
>
this works fine. its simple. I have this in a report and I would like
to add a parameter @.p1 to add to the query
>
where Product_Sales.Order_Amount >= @.p1
>
my problem is that if @.p1 is null I want to retrieve all recorders in
Product_List. any ideas.

where @.p1 is null or Product_Sales.Order_Amount >= @.p1

Help with parameter

Hi,
I am querying a sybase database for my table & need to add a start date &
end date to the query.
my where statement looks like this
WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
can i please have some assistance around inserting the parameters start_date
& end_date
i have tried
WHERE (ACDCallDetail.CallStartDt>=:start_date AND
ACDCallDetail.CallStartDt<:end_date) and also WHERE
(ACDCallDetail.CallStartDt>=@.start_date AND
ACDCallDetail.CallStartDt<@.end_date) to no avaial.
i also need to have the query that extracts the month only (for a month to
date) out of say the start_date & the end_date (for month to date figures)
Thankyou in adavance for any help
toddWhat happens when you use
WHERE ACDCallDetail.CallStartDt >= @.start_date
AND ACDCallDetail.CallStartDt < @.end_date
?
Derrick
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> Hi,
> I am querying a sybase database for my table & need to add a start date &
> end date to the query.
> my where statement looks like this
> WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> can i please have some assistance around inserting the parameters
> start_date
> & end_date
> i have tried
> WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> ACDCallDetail.CallStartDt<:end_date) and also WHERE
> (ACDCallDetail.CallStartDt>=@.start_date AND
> ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> i also need to have the query that extracts the month only (for a month to
> date) out of say the start_date & the end_date (for month to date figures)
> Thankyou in adavance for any help
> todd|||The parameters box comes up & i enter dates (tried both 01/08/2005 or
01/08/2005 12:00 AM) & i get following error message
must declare variable '@.start_date'
Thanks
Todd
"Derrick Van Hoeter" wrote:
> What happens when you use
> WHERE ACDCallDetail.CallStartDt >= @.start_date
> AND ACDCallDetail.CallStartDt < @.end_date
> ?
> Derrick
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> > Hi,
> > I am querying a sybase database for my table & need to add a start date &
> > end date to the query.
> > my where statement looks like this
> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> >
> > can i please have some assistance around inserting the parameters
> > start_date
> > & end_date
> >
> > i have tried
> >
> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
> > (ACDCallDetail.CallStartDt>=@.start_date AND
> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> >
> > i also need to have the query that extracts the month only (for a month to
> > date) out of say the start_date & the end_date (for month to date figures)
> >
> > Thankyou in adavance for any help
> >
> > todd
>
>|||Todd,
In what environment are you running the query?
Derrick
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:6E1BE054-6750-4126-A455-643185384088@.microsoft.com...
> The parameters box comes up & i enter dates (tried both 01/08/2005 or
> 01/08/2005 12:00 AM) & i get following error message
> must declare variable '@.start_date'
> Thanks
> Todd
> "Derrick Van Hoeter" wrote:
>> What happens when you use
>> WHERE ACDCallDetail.CallStartDt >= @.start_date
>> AND ACDCallDetail.CallStartDt < @.end_date
>> ?
>> Derrick
>>
>> "Tango" <Tango@.discussions.microsoft.com> wrote in message
>> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
>> > Hi,
>> > I am querying a sybase database for my table & need to add a start date
>> > &
>> > end date to the query.
>> > my where statement looks like this
>> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
>> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
>> >
>> > can i please have some assistance around inserting the parameters
>> > start_date
>> > & end_date
>> >
>> > i have tried
>> >
>> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
>> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
>> > (ACDCallDetail.CallStartDt>=@.start_date AND
>> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
>> >
>> > i also need to have the query that extracts the month only (for a month
>> > to
>> > date) out of say the start_date & the end_date (for month to date
>> > figures)
>> >
>> > Thankyou in adavance for any help
>> >
>> > todd
>>|||Use
WHERE
(ACDCallDetail.CallStartDt>=@.start_date AND
ACDCallDetail.CallStartDt<@.end_date)
You must add parameters to the report Called start_date and end_date ( use
the same case as the where clause)
Then open the data set and check the Parameters tab to ensure there is a
mapping between the parameter and the variable..
To get a month value from a date in SQL
select Datepart(mm,Getdate())
This returns the number of the month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> Hi,
> I am querying a sybase database for my table & need to add a start date &
> end date to the query.
> my where statement looks like this
> WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> can i please have some assistance around inserting the parameters
> start_date
> & end_date
> i have tried
> WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> ACDCallDetail.CallStartDt<:end_date) and also WHERE
> (ACDCallDetail.CallStartDt>=@.start_date AND
> ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> i also need to have the query that extracts the month only (for a month to
> date) out of say the start_date & the end_date (for month to date figures)
> Thankyou in adavance for any help
> todd|||Thanks Guys
I am using the where statement as suggested, the 2 parameters in the report.
Still doesnt work.
when i run the query in generic query designer, i get asked for parameters
where i have tried entering date format dd/mm/yyyy 12:00 AM or just
dd/mm/yyyy. I have also tried changing the parameter type from datetime to
string & i get the following message
Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare
variable '@.start_date'.
Its no use using the sql query designer as the system just locks up when i
have dates pre coded or gives error message when i attempt to run the query
"Providor can not derive parameter information and setparameterinfo has not
been called"
Look forward to hearing from you.
Todd
Im not sure if this means anything but i am querying a sybase database via
an ODBC (type) datasource.
"Wayne Snyder" wrote:
> Use
> WHERE
> (ACDCallDetail.CallStartDt>=@.start_date AND
> ACDCallDetail.CallStartDt<@.end_date)
> You must add parameters to the report Called start_date and end_date ( use
> the same case as the where clause)
> Then open the data set and check the Parameters tab to ensure there is a
> mapping between the parameter and the variable..
> To get a month value from a date in SQL
> select Datepart(mm,Getdate())
> This returns the number of the month...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> > Hi,
> > I am querying a sybase database for my table & need to add a start date &
> > end date to the query.
> > my where statement looks like this
> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> >
> > can i please have some assistance around inserting the parameters
> > start_date
> > & end_date
> >
> > i have tried
> >
> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
> > (ACDCallDetail.CallStartDt>=@.start_date AND
> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> >
> > i also need to have the query that extracts the month only (for a month to
> > date) out of say the start_date & the end_date (for month to date figures)
> >
> > Thankyou in adavance for any help
> >
> > todd
>
>|||Todd,
I have received the same error message before when I have changed the query
around the parameters. I don't have Reporting Service installed on this
machine so I'm going from memory, but in the generic query design pane,
there is a button to refresh data. Find that button and refresh the data,
then try to run the query with the parameters again. You may still get an
error but it should be a different error.
Let me know how it goes.
Derrick
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:58575D27-7103-4748-86C4-D4B2D3CF27E9@.microsoft.com...
> Thanks Guys
> I am using the where statement as suggested, the 2 parameters in the
> report.
> Still doesnt work.
> when i run the query in generic query designer, i get asked for parameters
> where i have tried entering date format dd/mm/yyyy 12:00 AM or just
> dd/mm/yyyy. I have also tried changing the parameter type from datetime to
> string & i get the following message
> Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare
> variable '@.start_date'.
> Its no use using the sql query designer as the system just locks up when i
> have dates pre coded or gives error message when i attempt to run the
> query
> "Providor can not derive parameter information and setparameterinfo has
> not
> been called"
> Look forward to hearing from you.
> Todd
> Im not sure if this means anything but i am querying a sybase database via
> an ODBC (type) datasource.
>
> "Wayne Snyder" wrote:
>> Use
>> WHERE
>> (ACDCallDetail.CallStartDt>=@.start_date AND
>> ACDCallDetail.CallStartDt<@.end_date)
>> You must add parameters to the report Called start_date and end_date (
>> use
>> the same case as the where clause)
>> Then open the data set and check the Parameters tab to ensure there is a
>> mapping between the parameter and the variable..
>> To get a month value from a date in SQL
>> select Datepart(mm,Getdate())
>> This returns the number of the month...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tango" <Tango@.discussions.microsoft.com> wrote in message
>> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
>> > Hi,
>> > I am querying a sybase database for my table & need to add a start date
>> > &
>> > end date to the query.
>> > my where statement looks like this
>> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
>> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
>> >
>> > can i please have some assistance around inserting the parameters
>> > start_date
>> > & end_date
>> >
>> > i have tried
>> >
>> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
>> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
>> > (ACDCallDetail.CallStartDt>=@.start_date AND
>> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
>> >
>> > i also need to have the query that extracts the month only (for a month
>> > to
>> > date) out of say the start_date & the end_date (for month to date
>> > figures)
>> >
>> > Thankyou in adavance for any help
>> >
>> > todd
>>|||Thanks for your interest Derrick & Tom,
in generic query designer the error message after entering dates is 'must
declare variable '@.start''.
when i refresh the pane i get error message That data extension odbc does
not support named paramters. use unnmaed paramaters instaed.
Todd
"Derrick Van Hoeter" wrote:
> Todd,
> I have received the same error message before when I have changed the query
> around the parameters. I don't have Reporting Service installed on this
> machine so I'm going from memory, but in the generic query design pane,
> there is a button to refresh data. Find that button and refresh the data,
> then try to run the query with the parameters again. You may still get an
> error but it should be a different error.
> Let me know how it goes.
> Derrick
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:58575D27-7103-4748-86C4-D4B2D3CF27E9@.microsoft.com...
> > Thanks Guys
> >
> > I am using the where statement as suggested, the 2 parameters in the
> > report.
> > Still doesnt work.
> > when i run the query in generic query designer, i get asked for parameters
> > where i have tried entering date format dd/mm/yyyy 12:00 AM or just
> > dd/mm/yyyy. I have also tried changing the parameter type from datetime to
> > string & i get the following message
> > Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare
> > variable '@.start_date'.
> > Its no use using the sql query designer as the system just locks up when i
> > have dates pre coded or gives error message when i attempt to run the
> > query
> > "Providor can not derive parameter information and setparameterinfo has
> > not
> > been called"
> >
> > Look forward to hearing from you.
> > Todd
> > Im not sure if this means anything but i am querying a sybase database via
> > an ODBC (type) datasource.
> >
> >
> > "Wayne Snyder" wrote:
> >
> >> Use
> >> WHERE
> >> (ACDCallDetail.CallStartDt>=@.start_date AND
> >> ACDCallDetail.CallStartDt<@.end_date)
> >>
> >> You must add parameters to the report Called start_date and end_date (
> >> use
> >> the same case as the where clause)
> >> Then open the data set and check the Parameters tab to ensure there is a
> >> mapping between the parameter and the variable..
> >>
> >> To get a month value from a date in SQL
> >>
> >> select Datepart(mm,Getdate())
> >>
> >> This returns the number of the month...
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> >> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> >> > Hi,
> >> > I am querying a sybase database for my table & need to add a start date
> >> > &
> >> > end date to the query.
> >> > my where statement looks like this
> >> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> >> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> >> >
> >> > can i please have some assistance around inserting the parameters
> >> > start_date
> >> > & end_date
> >> >
> >> > i have tried
> >> >
> >> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> >> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
> >> > (ACDCallDetail.CallStartDt>=@.start_date AND
> >> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> >> >
> >> > i also need to have the query that extracts the month only (for a month
> >> > to
> >> > date) out of say the start_date & the end_date (for month to date
> >> > figures)
> >> >
> >> > Thankyou in adavance for any help
> >> >
> >> > todd
> >>
> >>
> >>
>
>

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.