SQL Server Performance

Very Complicated Stored Proc

Discussion in 'General Developer Questions' started by monfu, Nov 28, 2005.

  1. monfu New Member

    Sorry did not give you an explanation :_


    I am working on a stored proc the previous developer before me developer, and its giving errors. However, since I am not very experienced in stored procs, I cannot seem to find the error.

    I am assuming its something to do with the dates. Cause previously, the dates where entered in the databse as varchar(20)! And I amended this to datetime. However, since I amended that, the search is not working anymore.

    I am assuming the dates are not in a correct format.

    I am passing the parameters to the stored proc as follows:-

    @dtFrom = "01/01/2000"
    @dtTo = "01/01/2099"

    Here is the stored proc:-

    -----------------------------------------------------------------------
    CREATE PROCEDURE [dbo].[stpEventsSearchResult]
    @searchstring varchar(100),
    @dtFrom datetime,
    @dtTo datetime,
    @region int,
    @school int,
    @ri_id int,
    @i_id int,
    @searchtype int
    AS
    If @region = 0
    BEGIN
    Set @region = -1
    END
    If @searchtype = 0
    BEGIN
    Select * from pod_Events WHERE e_title LIKE @searchstring or e_description LIKE @searchstring
    END
    else
    BEGIN
    if @region > 0
    BEGIN
    if @ri_id > 0
    BEGIN
    if @i_id > 0
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Users_Schools.id_s = @school) AND (pod_Event_RI.ri_id = @ri_id) AND (pod_Event_Inv.i_id = @i_id)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Event_RI.ri_id = @ri_id) AND (pod_Event_Inv.i_id = @i_id)
    END
    END
    else
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Users_Schools.id_s = @school) AND (pod_Event_RI.ri_id = @ri_id)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Event_RI.ri_id = @ri_id)
    END
    END
    END
    else
    BEGIN
    If @i_id > 0
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Users_Schools.id_s = @school) AND (pod_Event_Inv.i_id = @i_id)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Event_Inv.i_id = @i_id)
    END
    END
    else
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region) AND (pod_Users_Schools.id_s = @school)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Zone.id_z = @region)
    END
    END
    END
    END
    else
    BEGIN
    if @ri_id > 0
    BEGIN
    If @i_id > 0
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Users_Schools.id_s = @school) AND (pod_Event_RI.ri_id = @ri_id) AND (pod_Event_Inv.i_id = @i_id)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Event_RI.ri_id = @ri_id) AND (pod_Event_Inv.i_id = @i_id)
    END
    END
    else
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Users_Schools.id_s = @school) AND (pod_Event_RI.ri_id = @ri_id)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Event_RI.ri_id = @ri_id)
    END
    END
    END
    else
    BEGIN
    If @i_id > 0
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Users_Schools.id_s = @school) AND (pod_Event_Inv.i_id = @i_id)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Event_Inv.i_id = @i_id)
    END
    END
    else
    BEGIN
    if @school > 0
    BEGIN
    Select * from pod_Events INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo) AND (pod_Users_Schools.id_s = @school)
    END
    else
    BEGIN
    Select * from pod_Events INNER JOIN pod_Users ON pod_Events.id_u = pod_Users.id_u INNER JOIN pod_Users_Schools ON pod_Users.id_u = pod_Users_Schools.id_u INNER JOIN pod_Schools ON pod_Users_Schools.id_s = pod_Schools.id_s INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
    WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo)
    END
    END
    END
    END
    END

    GO
    -----------------------------------------------------------

    Any help would really be appreciated, since I cannot solve it at the moment

    Thanks
  2. benwilson New Member

    If it is just the date format, try passing it in YYYYMMDD format

    @dtFrom = '20000101'
    @dtTo = '20990101'

    If it is more than just the date, you can also debug stored procs in Query Analyzer- Open QA, show the object browser (F8 will show/hide it), find the proc u wanna debug, right click it and select debug...u can then enter the values for any parameters and step through to find where it is going wrong

    'I reject your reality and substitute my own' - Adam Savage
  3. Madhivanan Moderator

    >>the dates where entered in the databse as varchar(20)!

    You should use Proper Datatype DATETIME to store Dates
    Otherwise you need to convert them to DateTime and Do query

    For more details on Dates, refer this
    http://www.sql-server-performance.com/fk_datetime.asp

    Madhivanan

    Failing to plan is Planning to fail
  4. dineshasanka Moderator

  5. druer New Member

    Here is my thought ....
    You know that your change broke code that worked, so simply change the code back to what it was before so that it will work again. Make the other change you needed to make in the proc, but ignore this issue if it was in fact working correctly before.

    Here is a good development model to always follow:
    If it isn't broken, don't fix it.

    While the previous developer was CRAZY for using a VARCHAR(20) field to hold a date value, they did what they did and they produced a working stored procedure. (It could be that they were to inexperienced to know how to convert a text value from some import program to a date, who knows.) So live with it unless performance is an issue, or there is a legitimate reason to modify it.

Share This Page