Solved

Case selection with from clause

Posted on 2008-10-08
8
749 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 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:dotnetpro
Comment Utility
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
Comment Utility
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
Comment Utility
Is there anyone following up on this question ?
0
 
LVL 39

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now