Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: Writin a Stored Procedure

    hi
    I want to write a stored procedure that create a sql statement, depend on user filters.
    suppose 1 line of this sql statemenet is common and 1 line depend on what user select from a combo. for be apprehensible

    consider this syntax that i wrote in asp code for creating this statement:
    Code:
     
    dim sqlst
    sqlst="SELECT * FROM TBUsers WHERE Age>30 "
    
    IF idd1=1 THEN 
    	sqlst=sqlst+"AND "	
    	sqlst=sqlst+"Height>170 "
    END IF
    IF idd2=1 THEN 
    	sqlst=sqlst+"AND "	
    	sqlst=sqlst+"Weight>60 "
    END IF
    & So On...

    The issue is that i don want to repeat that first line more than 1 time otherwise this sp could written in this way:

    Code:
     
    CREATE PROCEDURE sp_test
    @hei int =0
    @wei int =0
    AS
    
    if @hei=0 and @wei=0
    begin
    SELECT * FROM TBUsers WHERE Age>30
    end
    
    if @hei=1 and @wei=0
    begin
    SELECT * FROM TBUsers WHERE Age>30 AND Height>170
    end
    
    if @hei=0 and @wei=1
    begin
    SELECT * FROM TBUsers WHERE Age>30 AND Weight<60
    end
    
    if @hei=1 and @wei=1
    begin
    SELECT * FROM TBUsers WHERE Age>30 AND Height>170 AND Weight<60
    end
    
    GO

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Try this ... and look up CASE in BOL

    <code>
    CREATE PROCEDURE sp_test
    @hei int =0
    @wei int =0
    AS

    SET NOCOUNT ON

    declare @sql varchar(8000), @condition varchar(1000)

    select @sql = 'SELECT * FROM TBUsers WHERE Age>30', @condition = ' '

    select @condition =
    CASE
    WHEN isnull(@hei, 0) > 0 THEN @condition = @condition + 'AND Height = ' + convert(varchar(05), @hei)
    END

    select @condition =
    CASE
    WHEN isnull(@wei, 0) > 0 THEN @condition = @condition + 'AND Weight = ' + convert(varchar(05), @hei)
    END

    select @sql = @sql + @condition

    EXEC @sql

    GO
    </code>

Posting Permissions

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