Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Case selection with from clause

Posted on 2008-10-08
8
Medium Priority
?
759 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
8 Comments
 
LVL 93

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

971 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