Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Union Queries/Store procedure Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:14 AM
Points: 36, Visits: 52
We are trying to set up a stored procedure that will use up to three search criteria to search in two different fields then union them together.
[dbo].[SP_NAME]


@Criteria1 varchar (50),
@Criteria2 varchar (50),
@Criteria3 varchar (50)


AS
select Category,OPT,[Description],Comments,UOM,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,
Item9,Item10,Major_Group from tblDCOptions
where
Description like '@Criteria1' and
Description like '@Criteria2' and
Description like '@Criteria3' and
(InActive is null or InActive = 0 )

union

select Category,OPT,[Description],Comments,UOM,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,
Item9,Item10,Major_Group from tblDCOptions
where
Comments like '@Criteria1' and
Comments like '@Criteria2' and
Comments like '@Criteria3' and
(InActive is null or InActive = 0 )

order by OPT


If I run this as a sql query so take out the @criteria info and type in items it works fine. If I execute the stored procedure I get nothing.

Each criteria is something like %Search-item% or if it is blank it is just %%
Can this not be done in a stored procedure?
Post #1562713
Posted Thursday, April 17, 2014 9:34 AM This worked for the OP Answer marked as solution


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:09 AM
Points: 363, Visits: 1,091
Remove the quotes from around the parameters. '@Criteria1' should be @Criteria1
Post #1562723
Posted Thursday, April 17, 2014 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:14 AM
Points: 36, Visits: 52
thanks didn't even register that they were left over from typing text in manually to test.
Post #1562731
Posted Thursday, April 17, 2014 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
You might want to take a look at this blog post from Gail. She goes over how to handle catch-all type queries. This is very close to that type of situation and reading her post may very well help you improve the performance of your procedure.

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


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1562747
Posted Thursday, April 17, 2014 10:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:14 AM
Points: 36, Visits: 52
Thanks I will take a look.
Post #1562754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse