Single SQL Query with properr filtering criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Single SQL Query with properr filtering criteria

Author

Topic

Kuul13
Starting Member

4 Posts

Posted - 2012-03-16 : 12:06:58

I have a requirement to write a PROCEDURE that returns data based on the filter parameter. If they don't pass the filter it should just not use them. I remember I used to do something like the below query. In where clause we used to check for the parameter instead of writing multiple IF clauses. Any one who can help?


CREATE TABLE TEST 
(
	ID FLOAT,
	NAME VARCHAR(50),
	CHECKEDINTIME VARCHAR(10),
	CHECKEDOUTTIME VARCHAR(10),
	NOSHOWTIME VARCHAR(10)
)

INSERT INTO TEST VALUES (1, 'EXAM 1', '10:23:00', NULL, NULL)
INSERT INTO TEST VALUES (1, 'EXAM 2', '09:13:00', '11:23:00', NULL)
INSERT INTO TEST VALUES (1, 'EXAM 3', NULL, NULL, '09:00:00')
INSERT INTO TEST VALUES (1, 'EXAM 4', NULL, NULL, '10:10:00')

DECLARE
	@ID FLOAT = NULL,
	@IsCheckedIn BIT = 1,
	@IsCheckedOut BIT = null,
	@IsNoShow BIT = NULL
BEGIN	
SELECT
	*
FROM TEST
WHERE (@ID IS NULL OR [ID] = @ID)
		AND (
			(@IsCheckedIn IS NULL OR (@IsCheckedIn = 1 AND CHECKEDINTIME IS NOT NULL))
			OR (@IsCheckedOut IS NULL OR (@IsCheckedOut = 1 AND CHECKEDOUTTIME IS NOT NULL))
			OR (@IsNoShow IS NULL OR (@IsNoShow = 1 AND NOSHOWTIME IS NOT NULL))
			)
END

This should actually return all the data where user has checked in. If user passes IsCheckedIn and IsNoShow true then it should give all the records that checkedouttime and noshowtime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 12:17:25

it looks fine so far as only one out of @IsCheckedIn,@IsCheckedOut,@IsNoShow needs to be considered at a time. whats the issue you're facing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kuul13
Starting Member

4 Posts

Posted - 2012-03-16 : 16:15:17

quote:
Originally posted by visakh16

it looks fine so far as only one out of @IsCheckedIn,@IsCheckedOut,@IsNoShow needs to be considered at a time. whats the issue you're facing?

Issue is that I need to show all records based on the filter. If user pass IsCheckedIn = 1 and IsCheckedOut = 1, then I need all those transactions that have either checkintime or checkouttime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 16:30:49

then what about @IsNoShow? what value will you pass for it or whats the default value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kuul13
Starting Member

4 Posts

Posted - 2012-03-16 : 16:32:10

I think I got the solution. Isn't it resolve my problem?


SELECT
	*
FROM TEST
WHERE (@ID IS NULL OR [ID] = @ID)
		AND (
			(@IsCheckedIn IS NOT NULL and (@IsCheckedIn = 1 AND CHECKEDINTIME IS NOT NULL))
			or (@IsCheckedOut IS NOT NULL and (@IsCheckedOut = 1 AND CHECKEDOUTTIME IS NOT NULL))
			or (@IsNoShow IS NOT NULL and (@IsNoShow = 1 AND NOSHOWTIME IS NOT NULL))
			)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 17:54:06

No idea. Unless i understand how you're passing values for @IsCheckedIn,@IsCheckedOut etc I wont be able to suggest anything

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-16 : 18:37:44

Maybe read this: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

Kuul13
Starting Member

4 Posts

Posted - 2012-03-19 : 14:53:42

quote:
Originally posted by GilaMonster

Maybe read this: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

Thanks for the link. I was able to resolve my issue by using the query posted earlier.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-19 : 15:59:48

Oh that query will work, no question there. If the data volume is large and good performance is required however it may not be adequate.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources