Better way to query SQL table for various inputs

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)

Better way to query SQL table for various inputs

Author

Topic

Ramm10
Starting Member

2 Posts

Posted - 2014-08-11 : 00:31:19

I am working on Stored Procedure in SQL 2008. I have to return the rows of a table based on different input combinations (around 17 combinations). 5 parameters are passed as i/p to this sp.

I started writing the SP checking in this way

DECLARE @varDynamicQuery VARCHAR(255)

SET @varDynamicQuery = 'SELECT Name, Address, PhoneDetails
FROM SuperUserDetails WITH(NOLOCK) WHERE '
/*
Combi 1
Input - Parameter - Output - Condition
-----------------------------------------------------
UID @in_varUID TableRows None

*/
IF(@in_varUID IS NOT NULL AND @in_varUID <> '')
BEGIN

SET @varDynamicQuery = @varDynamicQuery + 'UID = '''+@in_varUID+''''

EXEC (@varDynamicQuery)
END

/*
Combi 2
Input - Parameter - Output - Condition
-----------------------------------------------
Address @in_varAddr TableRows None

*/
IF(@in_varAddr IS NOT NULL AND @in_varAddr <> '')
BEGIN

SET @varDynamicQuery = @varDynamicQuery + 'UID = '''+@in_varAddr+''''

EXEC (@varDynamicQuery)
END

/*
Combi 3
Input - Parameter - Output - Condition
-----------------------------------------------------
UID @in_varUID TableRows None
Address @in_varAddr

*/
IF( (@in_varUID IS NOT NULL AND @in_varUID <> '') AND(@in_varAddr IS NOT NULL AND @in_varAddr <> '') )
BEGIN

SET @varDynamicQuery = @varDynamicQuery + 'UID = '''+@in_varUID+''' AND Address = ''''+@in_varAddr+'''

EXEC (@varDynamicQuery)
END

But, I am sure, a better way than this. As writing 17 combinations is not a good way.

If it better to write this using CASE statements?

The list of combinations are,(only 6 are shown) The input parameters to Sp are also listed.

Input Params C1 C2 C3 C4 C5 C6

UID Y N N N N N

Address N Y N N Y Y

PhoneDetails N N Y Y Y N

CreatedOn Y Y N Y Y Y

LastDate Y Y N N N Y

Please advice me on this.
Thanks
Ramm

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 07:54:48

Check out this article:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources