Multiple Query Options

Question

Multiple Query Options

Jonathen Scalet

SSC Journeyman

Points: 97
More actions
September 19, 2005 at 1:18 pm

#152547

Hi Guys-
I am trying to run a query that looks like this:
SELECT ZWPBID AS BANNERID, ZWPSSN AS SOCIAL, ZWPTITLE AS PTITLE, ZWPLSTNM AS LASTNAME, ZWPFSTNM AS FIRSTNAME,
ZWPMDLNM AS MID_INIT, ZWPLCLAD1 AS LOCADD1, ZWPLCLAD2 AS LOCADD2, ZWPLCLPHN AS LOCPHONE, ZWPHMEAD1 AS HOMEADD1,
ZWPHMEAD2 AS HOMEADD2, ZWPHMEPHN AS HOMEPHONE, ZWPISSTD AS IS_STU, ZWPISCSTD AS IS_CURR_ENROLLED,
ZWPISESTD AS IS_ENROLLED_STU, ZWPCLSTS AS IS_GRAD_STU, ZWPISFACT AS IS_FACULTY, ZWPISTFCT AS IS_TMP_FACULTY,
ZWPISSTAF AS IS_STAFF, ZWPISTSTA AS IS_TMP_STAFF, ZWPISRETD AS IS_RETIRED, ZWPISEMER AS IS_EMERITUS,
ZWPISGASS AS IS_GRAD_ASS, ZWPISEGAS AS IS_ENR_GRAD_ASS
FROM RMSPRDF.ZWMUPEOPLE
WHERE (ZWPSSN = ?)
right now it only queries based on Social Security Number, I need this report to query based on three values. Either SSN, Last Name, or First Name whatever the user specifies.
Any Ideas?
Thanks,
Jon

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

What you can do is either

A. Build 3 different queries and then run whichever one the customer selected. Pro cuts down execution plan. Con multiple places to make same change

B. Build your WHERE like .. WHERE ZWPSSN LIKE @SSN OR LastName LIKE @LNAME OR FirstName LIKE @FNAME

At the top of your SP IF there is no value or NULL assign the variable = '%' if there is a value assign the variable = to whatever the user sent in + '%'

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You might find this article usefull for such questions :

Dynamic Search Conditions in T-SQL

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

As requested per PM :

Not really in this case. I'd go with 3 queries in this case. Maybe I'd have a master sp call the correct sp to load the report depending on the parameters supplied.

The problem with this solution is that if you have to update the join info or add a column to the select, then you must change the info 3 times.

As for the other possibilities, you'll have to see which one to use when needed .

Jonathen Scalet SSC Journeyman Points: 97 More actions · Answer 4

AJ-

Im not quite sure what you are getting at? I am not an expert at RS. When you use a like statement dont you have to specify a pattern.

For example:

SELECT *

FROM Store_Information

WHERE store_name LIKE '%AN%'

Also what do you mean by at the top of my 'SP'?

Thanks for the quick reponse.

Jon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

SET @Param1 = ISNULL(@Param1, '') + '%'

SET @Param2 = ISNULL(@Param2, '') + '%'

SET @Param3 = ISNULL(@Param3, '') + '%'

if LEN(@Param1 + @Param2 + @Param3) > 3

begin

Select cols, list from dbo.YourQuery where Col1 like @Param1 and Col2 like @Param2 and Col3 like @Param3

end

else

--no param was set

if the param was null then the condition would be like :

where col1 like '%' and col2 like '%' and col3 like 'something%'

Jonathen Scalet SSC Journeyman Points: 97 More actions · Answer 6

Do I need to put that in a function? When exactly do I put your statement at?

Thanks for the help.

Jon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

In a stored proc.

Or in am inline table function if you need to use that in an inner join.

graham-216530 Grasshopper Points: 18 More actions · Answer 8

Hi

select .....

where (ZWPSSN=@sn or @SN='all')

and (lastname like '%' + @ln + '%' or @ln='All')

and (firstname like '%' + @fn + '%' or @ln='All')

Give the user the select of all as well as ...