?
Solved

SQL stored procedure to filter a parameter passed or null

Posted on 2011-10-25
6
Medium Priority
?
411 Views
Last Modified: 2012-05-12
I have a stored procedure to filter a SQL view by multiple parameters. One of the parameters I need to filter with is (say) by 'AuditNo'. It can be either passed as a NULL or as an integer (say 290).
The result I expect is as follows:
1. If null passed it should return all records regardless of 'AuditNo' (both containing data and nulls)
2. If a value passed (e.g. 290) it should only return the records with 'AuditNo' = 290
With the following code, I get correct records for 1 above and same results for 2 too (which is wrong). How can this be fixed?

Within SQL Stored Procedure
 
Declare @AuditNumberStart int
	Declare @AuditNumberEnd int
	IF (@AuditNumber IS NOT NULL)
		BEGIN
		SELECT @AuditNumberStart = @AuditNumber
		SELECT @AuditNumberEnd = @AuditNumber
		END
	ELSE
		BEGIN
		SELECT @AuditNumberStart = 0
		SELECT @AuditNumberEnd = 10000
		END

Open in new window


 
SELECT * 

FROM vMyView

WHERE
	.....
AND
	.....
AND
	(AuditNo BETWEEN @AuditNumberStart AND @AuditNumberEnd OR AuditNo IS NULL)
AND
	....
AND
	....

Open in new window


C# Code
 
SqlParameter paramAuditNumber = new SqlParameter("@AuditNumber", SqlDbType.Int, 4);
        if (auditNumber == "" || auditNumber == null)
        {
            paramAuditNumber.Value = DBNull.Value;
        }
        else
        {
            paramAuditNumber.Value = auditNumber;
        }
        myCommand.Parameters.Add(paramAuditNumber);

Open in new window

0
Comment
Question by:MrB8r
6 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 37029339
Look into SQL "COALESCE" Function. basicly if variable NULL or have value this function wil automaticaly do the rest


Select * From Table Where AuditNo = COALESCE(@AuditNo, AuditNo)


http://msdn.microsoft.com/en-us/library/ms190349.aspx
0
 

Author Comment

by:MrB8r
ID: 37029442
Hi adilkan,
Thanks for the response.
But that code only returns rown containing non-null values when passed NULL value.
0
 
LVL 5

Accepted Solution

by:
mcs0506 earned 668 total points
ID: 37029594
hi,

you can get all records by using  audit number directly in view query without using range variables
@AuditNumberStart
@AuditNumberEnd
here is updated query

SELECT *

FROM vMyView

WHERE
      .....
AND
      .....
AND
          (AuditNo =CASE WHEN @AuditNumber IS NULL THEN AuditNo ELSE @AuditNumber END
         OR  'A' = CASE WHEN @AuditNumber IS NULL THEN 'A' ELSE 'B' END)
AND
      ....
AND
      ....
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 17

Assisted Solution

by:Carlos Villegas
Carlos Villegas earned 1332 total points
ID: 37030384
Hello, you can try:
SELECT
      * 
FROM
      vMyView
WHERE
	.....
AND
	.....
AND
	(@AuditNumber IS NULL OR AuditNo BETWEEN @AuditNumberStart AND @AuditNumberEnd)

Open in new window

0
 
LVL 17

Assisted Solution

by:Carlos Villegas
Carlos Villegas earned 1332 total points
ID: 37030413
I have reviewed your code, based on your explanation:
1. If null passed it should return all records regardless of 'AuditNo' (both containing data and nulls)
2. If a value passed (e.g. 290) it should only return the records with 'AuditNo' = 290
You need to use this:
SELECT
      * 
FROM
      vMyView
WHERE
	.....
AND
	.....
AND
	(@AuditNumber IS NULL OR AuditNo = @AuditNumber)

Open in new window

0
 

Author Closing Comment

by:MrB8r
ID: 37035014
Thanks all for the help.
3 accepted solutions gave me the expected results.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question