Improving my Code - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Improving my Code

Author

Topic

Meltdown
Starting Member

37 Posts

Posted - 2006-02-08 : 07:23:10

Hi everyone, I'm new to SQL Server and TSQL and would appreciate if some of the more experieenced developers would look over my code andn suggest any improvements.

Basically what happens is the users has a set of 10 combo boxes (on an Access form) that they can use to filter the data they see.

I'm creating the 'dynamic sql' server side but I was wondering if it would be better to do it client side?.

My code is a bit long, but mostly duplication, thanks for any tips

Regards
Dave



ALTER PROCEDURE [dbo].[SummaryOrders]
@CompCode varChar(10) = Null,
@Status varChar(20) = Null,
@RepName varChar(20) = Null,
@ProductionManager varChar(3) = Null,
@SalesManager varChar(3) = Null,
@AdType varChar(30) = Null,
@Features varChar(30) = Null,
@ProductName varChar(10) = Null,
@IssueName varChar(10) = Null,
@WhichOrders varChar(6) = Null,
@StartDate varChar(10) = Null,
@EndDate varChar(10) = Null
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

--declare variables
Declare @blnFirstCondition bit
Declare @strCriteria varchar(3000)
Declare @strCriteriaFields varchar(2400) 
Declare @strCriteriaWhere varchar(1024) 
Declare @strCriteriaOrderBy varchar(1024)
Declare @strCriteriaTerminate varchar(1)

Set @blnFirstCondition = 'True'
Set @strCriteria = ''

Set @strCriteriaFields = 'SELECT dbo.tblOrders.Feature, dbo.tblOrders.OrderNo, dbo.tblOrders.Signed_Order, dbo.tblOrders.Current_Order, dbo.tblOrders.Back_To_Rep, 
                      dbo.tblOrders.Notes, dbo.tblOrders.Comp_Code, dbo.tblCompanies.Comp_Name, dbo.tblOrders.Order_Date, dbo.tblOrders.Rep_Code, 
                      dbo.tblReps.Name, dbo.tblOrders.Prod_Price, dbo.tblOrders.Prod_Code, dbo.tblProducts.MainProductCode, dbo.tblProducts.Description, 
                      dbo.tblOrders.Sub_Prod_Code, dbo.tblSubProducts.AST, dbo.tblOrders.Design_Time, dbo.tblOrders.Status, dbo.tblProdManagers.ProdMngCode, 
                      dbo.tblProdManagers.Name AS ProductionManager, dbo.tblSalesManagers.SalesMngCode, dbo.tblSalesManagers.Name AS SalesManager, dbo.tblOrders.Designer_Code, 
                      dbo.tblDesigners.Name AS Designer,
                      
 "ValidPrice" = 
     CASE 
           WHEN Prod_Price >= 0 THEN Prod_Price
           ELSE 0
      END,
"Cancellation" = 
     CASE 
           WHEN Prod_Price < 0 THEN Prod_Price
           ELSE 0
      END
FROM         dbo.tblStatus INNER JOIN
                      dbo.tblSubProducts RIGHT OUTER JOIN
                      dbo.tblSalesManagers RIGHT OUTER JOIN
                      dbo.tblProdManagers INNER JOIN
                      dbo.tblReps ON dbo.tblProdManagers.ProdMngCode = dbo.tblReps.ProdMngCode INNER JOIN
                      dbo.tblProducts INNER JOIN
                      dbo.tblDesigners RIGHT OUTER JOIN
                      dbo.tblCompanies INNER JOIN
                      dbo.tblOrders ON dbo.tblCompanies.Comp_Code = dbo.tblOrders.Comp_Code ON dbo.tblDesigners.Code = dbo.tblOrders.Designer_Code ON 
                      dbo.tblProducts.Code = dbo.tblOrders.Prod_Code ON dbo.tblReps.Code = dbo.tblOrders.Rep_Code ON 
                      dbo.tblSalesManagers.SalesMngCode = dbo.tblReps.SalesMngCode ON dbo.tblSubProducts.Code = dbo.tblOrders.Sub_Prod_Code ON 
                      dbo.tblStatus.Status = dbo.tblOrders.Status'

Set @strCriteriaWhere = ' WHERE '
Set @strCriteriaOrderBy = ' ORDER BY tblOrders.Order_Date DESC'
Set @strCriteriaTerminate = ';'


If @CompCode <> 0
Begin
        If @blnFirstCondition = 'True'
        Begin
				
					Declare @strCriteriaCustomer nvarchar(100)
					Set @strCriteriaCustomer = 'dbo.tblOrders.Comp_Code =' + @CompCode
					Set @blnFirstCondition = 'False'
			
         End
         Else
         Set @strCriteriaCustomer = ''
 End        
     Else
         Set @strCriteriaCustomer = ''



--set the Status WHERE clause
If @Status <> 'All'
Begin
Declare @strCriteriaStatus varchar(50)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaStatus = 'dbo.tblOrders.Status = ' + '''' + @Status  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaStatus = ' AND dbo.tblOrders.Status = ' + '''' + @Status  + ''''
 End        
     Else
         Set @strCriteriaStatus = ''

--set the Reps WHERE clause
If @RepName <> '0'
Begin
Declare @strCriteriaRep varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaRep = 'dbo.tblOrders.Rep_Code = ' + '''' + @RepName  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaRep = ' AND dbo.tblOrders.Rep_Code = ' + '''' + @RepName  + ''''
 End        
     Else
         Set @strCriteriaRep = ''

--set the Production Manager WHERE clause
If @ProductionManager <> '0'
Begin
Declare @strCriteriaPManager varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaPManager = 'dbo.tblProdManagers.ProdMngCode = ' + '''' + @ProductionManager  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaPManager = ' AND dbo.tblProdManagers.ProdMngCode = ' + '''' + @ProductionManager  + ''''
 End        
     Else
         Set @strCriteriaPManager = ''

--set the Sales Manager WHERE clause
If @SalesManager <> '0'
Begin
Declare @strCriteriaSalesManager varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaSalesManager = 'dbo.tblSalesManagers.SalesMngCode = ' + '''' + @SalesManager  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaSalesManager = ' AND dbo.tblSalesManagers.SalesMngCode = ' + '''' + @SalesManager  + ''''
 End        
     Else
         Set @strCriteriaSalesManager = ''

--set the Ad Type WHERE clause
If @AdType <> ' All'
Begin
Declare @strCriteriaAdType varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaAdType = 'dbo.tblOrders.Sub_Prod_Code = ' + '''' + @AdType  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaAdType = ' AND dbo.tblOrders.Sub_Prod_Code = ' + '''' + @AdType  + ''''
 End        
     Else
         Set @strCriteriaAdType = ''

--set the Features WHERE clause
If @Features <> '0'
Begin
Declare @strCriteriaFeatures varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaFeatures = 'dbo.tblOrders.Feature = ' + '''' + @Features  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaFeatures = ' AND dbo.tblOrders.Feature = ' + '''' + @Features  + ''''
 End        
     Else
         Set @strCriteriaFeatures = ''



--set the Product Issue WHERE clause
Declare @strCriteriaIssue varchar(100)
If @IssueName <> '0'
Begin
        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaIssue = 'dbo.tblOrders.Prod_Code = ' + '''' + @IssueName  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaIssue = ' AND dbo.tblOrders.Prod_Code = ' + '''' + @IssueName  + ''''
 End        
     Else If (@IssueName = '0' AND @ProductName <> '0')
Begin
        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaIssue = 'dbo.tblProducts.MainProductCode = ' + '''' + @ProductName  + ''''
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaIssue = ' AND dbo.tblProducts.MainProductCode = ' + '''' + @ProductName  + ''''
 End        
     Else
        Set @strCriteriaIssue = ''


--set the Which Order Type WHERE clause
If @WhichOrders <> 'All'
Begin
Declare @strCriteriaWhich varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaWhich = 'dbo.tblOrders.Current_Order = ' + @WhichOrders  
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaWhich  = ' AND dbo.tblOrders.Current_Order = ' +  @WhichOrders  
 End        
     Else
         Set @strCriteriaWhich  = ''


--set the Start Date WHERE clause
If @StartDate  <> 'NoDate'
Begin
Declare @strCriteriaStartDate varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaStartDate = 'dbo.tblOrders.Order_Date >= ' + '''' + @StartDate  + '''' 
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaStartDate  = ' AND dbo.tblOrders.Order_Date >= '+ '''' + @StartDate  + '''' 
 End        
     Else
         Set @strCriteriaStartDate  = ''


--set the End Date WHERE clause
If @EndDate  <> 'NoDate'
Begin
Declare @strCriteriaEndDate varchar(100)

        If @blnFirstCondition = 'True'
        Begin	
			Set @strCriteriaEndDate = 'dbo.tblOrders.Order_Date <= ' + '''' + @EndDate  + '''' 
			Set @blnFirstCondition = 'False'			
         End
			Else
			Set @strCriteriaEndDate  = ' AND dbo.tblOrders.Order_Date <= '+ '''' + @EndDate  + '''' 
 End        
     Else
         Set @strCriteriaEndDate  = ''



If @blnFirstCondition = 'True'        
    SET @strCriteria = @strCriteria + @strCriteriaFields + @strCriteriaOrderBy + @strCriteriaTerminate
Else 
SET @strCriteria = @strCriteria
 + @strCriteriaFields
 + @strCriteriaWhere  
 + @strCriteriaCustomer 
 + @strCriteriaStatus 
 + @strCriteriaRep 
 + @strCriteriaPManager
 + @strCriteriaSalesManager
 + @strCriteriaAdType
 + @strCriteriaFeatures
 + @strCriteriaIssue 
 + @strCriteriaWhich 
 + @strCriteriaStartDate 
 + @strCriteriaEndDate 
 + @strCriteriaOrderBy 
 + @strCriteriaTerminate
 --End if
       

	Exec sp_sqlexec @strCriteria
		 WITH RECOMPILE


END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 07:57:24

Search for Where in @MyCSV in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-08 : 10:11:42

See if this helps: http://www.sommarskog.se/dyn-search.html

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources