Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    29

    Unanswered: using if statments in a stored proc

    i'm passing 4 paramaters to a stored proc. based on the values of the paramaters i add conditions to my select. can som one please reviwe the proc below and tell me if my syntax is wrong or if there is another way of doing this.

    Thank You,
    Thomas


    CREATE PROCEDURE [Multi_Picking_Slip_FillListview1]
    @str_Division nvarchar(50), @str_Season nvarchar(50), @str_Cust nvarchar(50), @str_ShipTo nvarchar(50) AS

    SELECT * from tblDistribution WHERE PikingNo = 'NO'

    If @str_Division <> ''
    AND Division =@str_Division

    If @str_Season <> ''
    AND Season = @str_Season

    If @str_Cust <> ''
    AND cusNumber = @str_Cust

    If @str_ShipTo <> ''
    AND shpStoreNo = @str_ShipTo

    GO

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: using if statments in a stored proc

    Use CASE WHEN instead of IF.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about

    Code:
    CREATE PROCEDURE [Multi_Picking_Slip_FillListview1]
    	  @str_Division nvarchar(50) = Null
    	, @str_Season nvarchar(50) = Null
    	, @str_Cust nvarchar(50) = Null
    	, @str_ShipTo nvarchar(50) = Null
    AS
    
    SELECT * 
      FROM tblDistribution 
     WHERE PikingNo = 'NO' 
       AND Division   = ISNULL(@str_Division,Division)
       AND Season     = ISNULL(@str_Season,Season)
       AND cusNumber  = ISNULL(@str_Cust,CusNumber)
       AND shpStoreNo = ISNULL@str_ShipTo,shpStoreNo)
    GO
    Gotta check the PLAN though
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2003
    Posts
    29
    Brett,

    won't this code @str_Division nvarchar(50) = Null over write any value i'm passing from my app?

    i should also mention that if the paramater is equal to blank i do not want to include it in my select statment.

    Thanks,
    Thomas

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Test it in QA...just cut and paste...

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99
    	  @OrderID int		= null
    	, @CustomerID nchar(10) = null
    	, @EmployeeID int 	= null
    	, @OrderDate datetime 	= null
    AS
    
    SELECT * 
      FROM Orders
     WHERE OrderId    = ISNULL(@OrderID,OrderID)
       AND CustomerID = ISNULL(@CustomerID,CustomerID)
       AND EmployeeID = ISNULL(@EmployeeID,EmployeeID)
       AND OrderDate  = ISNULL(@OrderDate,OrderDate)
    
    GO
    
    EXEC mySproc99
    GO
    
    EXEC mySproc99 10254
    GO
    
    EXEC mySproc99 null, 'TOMSP'
    GO
    
    EXEC mySproc99 null, null, 6
    GO
    
    EXEC mySproc99 null, null, null, '1996-09-05 00:00:00.000'
    GO
    
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    29
    thank you very much it worked perfectly.

    i have another problem maybe you can help me with.

    in my vb app i have a list of style numbers. the user can select an unlimited number of style and the ap will build the select statment off of the styles selected.

    str1="123,456,789"

    str="select * from tblstyle where style in (" & str1 & ")"

    how can i pass a string to my stored proc, then break it up for use in the above select. the string i want to pass will look like this "123,456,789". in this example i only put 3 styles but the user can select from 1 to unlimited styles.

    Thank you,
    Thomas

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well since you're heading that way anyway...

    The code I gave you is doing index scans...which is not good...it's better than a tbale scan, but a scan is a scan

    In the QA window, type [CTRL]+K to turn on SHOW PLAN to see what's going on...

    How much data we talking about?

    I hat to promote dynamic sql, but this does an index seek

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99
    	  @OrderID int		= null
    	, @CustomerID nchar(10) = null
    	, @EmployeeID int 	= null
    	, @OrderDate datetime 	= null
    AS
    
    DECLARE @SQL varchar(8000)
    
    SELECT @SQL = 'SELECT * FROM Orders WHERE 1=1 '
       IF @OrderID    IS NOT NULL  SET @SQL = @SQL + ' AND OrderId    = '+ CONVERT(varchar(25),@OrderID)
       IF @CustomerID IS NOT NULL  SET @SQL = @SQL + ' AND CustomerID = '+ '''' + @CustomerID + ''''
       IF @EmployeeId IS NOT NULL  SET @SQL = @SQL + ' AND EmployeeID = '+ CONVERT(varchar(25),@EmployeeID)
       IF @OrderDate  IS NOT NULL  SET @SQL = @SQL + ' AND OrderDate  = '+ '''' + CONVERT(varchar(25),@OrderDate) + ''''
    EXEC(@SQL)
    GO
    
    EXEC mySproc99
    GO
    
    EXEC mySproc99 10254
    GO
    
    SELECT * FROM Orders WHERE OrderId = 10254
    
    EXEC mySproc99 null, 'TOMSP'
    GO
    
    EXEC mySproc99 null, null, 6
    GO
    
    EXEC mySproc99 null, null, null, '1996-09-05 00:00:00.000'
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2003
    Posts
    29
    i didn't know i could excute a string in a stored proc using the EXEC command. all i had to do was build my select statment through my vb app and pass the string to the proc an it worked fine. quick question i read that all stored procs are compiled and cashed. if they are will running my app this way be slower than if i used a regular stored proc.

    thanks for all the help you've given.
    Thomas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •