Solved

Case selection with from clause

Posted on 2008-10-08
8
753 Views
Last Modified: 2012-05-05
Experts i am trying to replace a dynamic sql query inside my sproc with a general statement but using CASE. I am trying to replace if then else and appending to a string with using CASE stmts...I believe that will improve the performance of the query. Is that correct ? I wrote something but incorrect, can u please guide me to correct it ? I hope you understand what i am trying to achieve here.

declare @size integer
set @size=1
select * from size + (case @size<>0 then ',size_scale where size.size_scale_id=size_scale.size_scale_id and size.size_scale_id=@size' end)
0
Comment
Question by:dotnetpro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22671977
declare @size integer, @sql varchar(max)
set @size=1
set @sql = 'select * from size ' + (case @size<>0 then ',size_scale where size.size_scale_id=size_scale.size_scale_id and size.size_scale_id=' + CAST(@size AS varchar) end)
exec(@sql)
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22672003
ithis should do i think
if u wanna to return all records just set @size = null

declare @size int

set @size=1


select
    *
from size
left join size_scale
    on size.size_scale_id = size_scale.size_scale_id
where
 (@size is null or size.size_scale_id = @size )


0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22672015
Can you include the rest of your SQL.  I'm guessing you are trying to do something like this...
declare @size integer 
set @size=1
 
declare @sql nvarchar(max),@parmList nvarchar(max)
set @sql = 'select field1,field2,field3 '
 
 
if @size <>0 
 
  set @SQL = @SQL + ',size_scale from MyTable where size.size_scale_id=size_scale.size_scale_id and size.size_scale_id=@size' end
else
set @SQL = @SQL + ' from MyTable ' end
 
set @parmList = '@size int'
exec sp_executesql @SQL,@parmlist,@size=@size

Open in new window

0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 1

Author Comment

by:dotnetpro
ID: 22672194
Well i am trying to lessen the amount of time taken to execute a set of 4 procedures. There are many parameters that get appended into the query based on their value. I am trying to write one select query that will take care of all the "if then else " scenarios and the query will be cached as well.
I am using sp_executesql as well but that also does not show much effect when the values of the input parameters are changing every time i execute the sql.
0
 
LVL 1

Author Comment

by:dotnetpro
ID: 22672285
Here is the code
@PO_Number varchar(256),              
@PO_Type varchar(256),              
@Vendor varchar(256),              
@Label varchar(256),              
@Gender varchar(256),              
@Season varchar(256),              
@Category varchar(256),              
@Classification varchar(256),              
@Description varchar(256),              
@Style varchar(256),              
@Quality varchar(256),              
@Color varchar(256),              
@Size varchar(256),              
@Type varchar(256),              
@Units_Greater_Than integer,              
@Excess_Inventory varchar(1),              
@Cost_Per_Unit_Over  money,              
@Cost_Per_Unit_Under money,              
@Warehouse varchar(256),              
@Section varchar(256),              
@Units_Less_Than integer,              
@Status varchar(256),              
@SO_Number varchar(256)        
         
AS              
        
        
declare @reporting_job_id bigint        
declare @stmt nvarchar(4000)              
declare @stmt1 nvarchar(4000)              
declare @fields varchar(2000)              
        
        
            
if (@Size <> '_SKIP_')              
begin              
select              
  distinct '(['+size.description+'_units'+'] > 0) and ' as field,              
  space(1) as RecList              
 into              
  #workingtable              
 from size, iter_intlist_to_table(@Size) i_size where              
 --quantity_size.size_id = size.size_id and              
 size.size_id = i_size.number               
              
 set @fields = ''              
 select              
  @fields=@fields+field              
 from              
  #workingtable              
                  
end              
                
set @stmt = ''              
set @stmt1 = ''              
    
--set @stmt='select distinct ''Section: ''+LTRIM(STR(section_number))+''   ''+section_header+'' Inventory'''              
set @stmt='select distinct ''Section: ''+LTRIM(STR(section_number))+''   ''+section_header+'' Inventory'''              
set @stmt= @stmt+' as "MainHeading" '              
set @stmt= @stmt+'from '              
set @stmt= @stmt+'wms_reports_staging_table  '              
if (@Size <> '_SKIP_')              
begin              
 set @stmt= @stmt+', size '              
 set @stmt= @stmt+',iter_intlist_to_table('''+@Size+''') i_size '              
end              
if @Label <> '_SKIP_'              
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Label+''') i_label '              
if @Classification <> '_SKIP_'              
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Classification+''') i_class '              
if @Vendor <> '_SKIP_'                  
     set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Vendor+''') i_vendor '              
if @Season  <> '_SKIP_'              
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Season+''') i_season '              
if @Gender <> '_SKIP_'                  
     set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Gender+''') i_gender '              
if @Category  <> '_SKIP_'                   
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Category+''') i_category '              
if @Status  <> '_SKIP_'                   
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Status+''') i_status '              
if @Description  <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Description+''') i_carton'              
if @Quality  <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Quality+''') i_quality '              
if @Color  <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Color+''') i_color'              
if @Warehouse  <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ',iter_intlist_to_table('''+@Warehouse+''') i_warehouse '              
if @Style  <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ', iter_intlist_to_table('''+@Style+''') i_style '              
if @PO_Type <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ', iter_intlist_to_table('''+@PO_Type+''') i_po_type '              
if @PO_Number <> '_SKIP_'              
    set @stmt1 = @stmt1 + ',iter_charlist_to_table('''+@PO_Number+''') i_po_number '              
if @SO_Number <> '_SKIP_'              
    set @stmt1 = @stmt1 + ',iter_charlist_to_table('''+@SO_Number+''') i_so_number '              
if @Section <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ', iter_intlist_to_table('''+@Section+''') i_section '              
if @Type <> '_SKIP_'                  
    set @stmt1 = @stmt1 + ', iter_charlist_to_table('''+@Type+''') i_type '              
                  
              
--set @stmt1 = @stmt1 + ' where inventory_status = ''Active'' '              
set @stmt1 = @stmt1 + ' where '              
if (@Size <> '_SKIP_')              
begin              
 set @stmt1 = @stmt1 + @fields              
 set @stmt1 = @stmt1 + ' size.size_scale_id = wms_reports_staging_table.size_scale_id and size.size_id = i_size.number and '              
end              
set @stmt1 = @stmt1 + ' 1 = 1 '              
if @Status <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (inventory_status_id = i_status.number) '              
if @PO_Number <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (po_number = i_po_number.item) '              
if @SO_Number <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (so_number = i_so_number.item) '              
if @PO_Type <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (po_type_id = i_po_type.number) '              
if @Section <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (section_number = i_section.number) '              
if @Type <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (case when type='''' then ''nej'' when type is null then ''nej'' else type end = i_type.item) '              
              
if @Vendor <> '_SKIP_'              
 set @stmt1 = @stmt1 + ' and (vendor_id = i_vendor.number) '              
if @Label <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (label_id = i_label.number) '              
if @Gender <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (gender_id = i_gender.number) '              
if @Season  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (season_id = i_season.number) '              
if @Classification  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (classification_id = i_class.number) '              
if @Category  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (category_id = i_category.number)  '              
if @Description  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (label_description_id = i_carton.number) '              
if @Style  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (style_id = i_style.number) '              
if @Quality  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (quality_id = i_quality.number) '              
if @Color  <> '_SKIP_'              
 set @stmt1 = @stmt1 + 'and (color_id = i_color.number) '              
if @Warehouse  <> '_SKIP_'              
set @stmt1 = @stmt1 + 'and (warehouse_id = i_warehouse.number) '              
--if (@Units_Greater_Than <> 0 )              
--    set @stmt1 = @stmt1 + 'and units > '+ str(@Units_Greater_Than)           
--if (@Units_Less_Than <> 0 )              
--    set @stmt1 = @stmt1 + 'and units < '+ str(@Units_Less_Than)              
if (@Cost_Per_Unit_Over <> 0.0 )              
    set @stmt1 = @stmt1 + 'and cost > '+ str(@Cost_Per_Unit_Over)               
if (@Cost_Per_Unit_Under <> 0.0 )              
    set @stmt1 = @stmt1 + 'and cost < '+ str(@Cost_Per_Unit_Under)              
if (@Excess_Inventory = '1')               
    set @stmt1 = @stmt1 + 'and cost = 0'              
set @stmt1= @stmt1+'  GROUP BY '              
set @stmt1= @stmt1+'  section_number, section_header, '              
set @stmt1= @stmt1+' quality_description+char(1)+label_description+char(1)+gender_description+char(1)+season_description+char(1)+classification_description+char(1)+label_description_description+char(1)+category_description '              
set @stmt1= @stmt1+' ,isnull(type, ''''), cost'              
              
set @stmt1= @stmt1+' having (sum(units) > '+str(@Units_Greater_Than)+' or '+str(@Units_Greater_Than)+' = 0)'              
set @stmt1= @stmt1+' and (sum(units) < '+str(@Units_Less_Than)+' or '+str(@Units_Less_Than)+' = 0)'              
              
set @stmt1= @stmt1+' order by ''Section: ''+LTRIM(STR(section_number))+''   ''+section_header+'' Inventory'''     
             
set @stmt=@stmt+@stmt1       
    
 
execute sp_executesql @stmt       
        
        
              
            
          
        

Open in new window

0
 
LVL 1

Accepted Solution

by:
dotnetpro earned 0 total points
ID: 22741337
Is there anyone following up on this question ?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22743002
Sorry.  Somehow I must have missed you posting the SQL.  I will review this ASAP.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question