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.
Author |
Topic |
meadow0
Starting Member
6 Posts |
Posted - 2013-03-15 : 13:58:23
|
Hello all,I've created a (probably over complex) query that will eventually allow a user to select a value from 3 different variables. @Status, @AcctName, @RptName. Since I'm allowing the end user to multi-select different values from the variables, my query begins to become a bit more complex... For instance:declare @status varchar(150)select @status = 'ERROR,REVIEW,COMPLETE,DELIVERY PREPARED,ALERT'IF (@status = 'ALL'AND @AcctName <> 'ALL' AND @RptName <> 'ALL') BEGINselect * from vw_document_mainWHERE ','+REPLACE(@AcctName,'','')+',' LIKE '%,'+acct_name+',%'AND ','+REPLACE(@RptName,'','')+',' LIKE '%,'+rpt_title+',%'ENDELSEBEGIN.......Since I'd have to allow for customization I have 9 different IF ELSE statements. What I need help with doing is creating an ORDER BY variable for the entire IF ELSE statement. I.E - @ColName = 'system_Status' then the entire IF ELSE statement would Order by System Status... Any idea? I began writing a query...ORDER BY CASE @ColName WHEN 'system_Status' THEN system_status WHEN 'document_status_code' THEN document_status_codeHowever, I'm not sure where this ORDER BY clause would go into my IF ELSE statement... would it need to be after each individual SELECT statement? Any clarification would help, thanks!! |
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2013-03-15 : 14:03:17
|
Yes it has to be in each SELECT statment. So put Order by afterAND ','+REPLACE(@RptName,'','')+',' LIKE '%,'+rpt_title+',%' and similarly in the Select in ELSE....You can always use PRINT to see the query and then execute that in query analyzer to make sure the dynamic query is correct. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-15 : 14:13:39
|
I'm not sure what your performance is like with your current set of queries or how much of a concern that is. However, you might be able to use a catach-all query (http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/): something like:select * from vw_document_mainWHERE ( ','+REPLACE(@AcctName,'','')+',' LIKE '%,'+acct_name+',%' OR @AcctName = 'ALL' ) AND ( ','+REPLACE(@RptName,'','')+',' LIKE '%,'+rpt_title+',%' OR @RptName = 'All' ) Another option is go to dynamic sql, that should gain you more performance and possibly be easier to read. |
|
|
|
|
|
|
|