Stored Procedure Parameter default to return ALL 3

[toekneel]

I've been trying to find the complete answer to this one, and I've found bits and pieces that help, but not 100% complete for my needs.

I have a large "Super Query" for the end user, where they will choose drop down boxes or enter dates or enter numbers in order to return all the records in the system to match their criteria.

I understand the need to have a default parameter value in the stored procedure. If I use '%' for string values, it successfully returns all of the values except the parameters where the filter applies. My problem is that I have other data types where I need to set up similar filters. I have int, bit, datetime, smalldatetime, and ntext.

Can anyone help me in getting the proper syntax for running a stored procedure using these data types where if they are not specified they return all of the records?

 

[fluteplr]

if you pass null when you want the parameter to be ignored you can use a where clause like.

select * from mytable
where
(myfield = @myparameter or @myparameter is null)

Be VERY careful with the parentheses.

 

[toekneel]

This doesn't seem to change things. When running this query in query analyzer, it is still giving me the error message: "Syntax error converting the varchar value '%' to a column of data type int."

 

[ClaireHCM]

Could you give some example how you want to input parameter and how you want the result to be?

The way fluteplr answered is by guessing what you really want.Maybe it's better you can be more specific.

 

[toekneel]

Here's my sample code:

The form passes values for any of the four variables, but if a variable is NOT selected, then I want ALL records that meet the other criteria.

CREATE PROCEDURE [dbo].[Test] @First nvarchar(15)='%', @Last nvarchar(15)='%', @ForemanID int='%', @Active bit ='%'
AS
select * from tblForeman
where strForemanFirstName like @First and strForemanLastName like @Last and ysnActiveInd like @Active
and autoForemanID like @ForemanID and ysnActiveInd like @Active
GO

By the way, Claire, I benefitted from reading your previous postings on this subject. Glad to see you have interest in helping me on this!

Tony

 

[sunila7]

Hi Tony,

Try changing this SP... Does it work....

CREATE PROCEDURE [dbo].[Test] @First nvarchar(15)=NULL, @Last nvarchar(15)=NULL, @ForemanID int=NULL, @Active bit =NULL
AS
select * from tblForeman
where
(strForemanFirstName like @First or @First IS NULL)
and
(strForemanLastName like @Last or @LAst is NULL)
and
(ysnActiveInd like @Active or @Active IS NULL)
and
(autoForemanID like @ForemanID or @foremanID is null)
and
(ysnActiveInd like @Active or @active is NULL)
GO

Sunil

 

[toekneel]

This code seems to be working! I'll work at testing it on some of the larger sprocs and post an update here when I have them tested.

Thanks for the input!

Tony

 

[ClaireHCM]

Hi this will work too.!

alter PROCEDURE proc10 @id nvarchar(15)='%', @name nvarchar(15)='%',@rowid int=null
AS
if @rowid is null
begin
select * from accnts
where id like @id and Name like @name and rowid like '%'
end
else
begin
select * from accnts
where id like @id and Name like @name and rowid like @rowid
end
GO

 

[toekneel]

Thanks for the help on this one! I've been working all day at putting together the underlying details. I have a form with 30 fields the user can select in order to view results. I ended up using the first sample (from sunila7) as it arrived first and it was working as the second sample came in.

It did take a bit of work to concatenate the code for calling the stored procedure from my front end (MS Access Project 2000) but all in all it is working like a charm!

Thanks for the help!

Tony

 

[xutopia]

A star to Claire. That example rocks!

Gary

http://www.xutopia.com

Haran
==========================

 

[synapsevampire]

Try this:

Say you have 2 dates that might be passed through, in the Where clause, leverage the coalesce function, that way no IFs are involved:

CREATE PROCEDURE [DBO].[ap_Sel_TransSumByAccount]
@startdate datetime = null,
@enddate datetime = null,
AS
select * from AtsTransacts
where
AtsTransacts.dtsTransactDate >= COALESCE(@startdate,AtsTransacts.dtsTransactDate)
AND AtsTransacts.dtsTransactDate <= COALESCE(@enddate,AtsTransacts.dtsTransactDate)

-k

 

[toekneel]

I'm not familiar with the COALESCE function, but in reading about it online, it wouldn't fit in this instance. In this instance, you have 30 possible parameters for a query, and you want to be able to use any of the combinations the user requests. Am I correct in interpreting that COALESCE would stop at the first non-null returned? So that, depending upon the order of the parameters, 10 could be selected, but only the first in the code would be included in the COALESCE function?

I'm still learning this stuff, and I appreciate the opportunities to learn that Tek-tips have provided!

Tony