Very Complicated Stored Proc - 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)

Very Complicated Stored Proc

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2005-11-28 : 17:31:47

Dear All,

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.

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-28 : 17:58:40

>>its giving errors
what are the errors?

>>I amended this to datetime
Do you mean you re-defined the datatype of the date_posted column? Or you changed the input paremters (@dtFrom,@dtTo) to datetime?

Were you trying to fix something? Why not just undo your change?

Be One with the Optimizer
TG

monfu
Yak Posting Veteran

81 Posts

Posted - 2005-11-28 : 19:48:44

The errors are that it is not retreiving anything back

Yes i did re-define the datatype of date_posted.

I cannot undo the change since now I am inserting everytying correctly in datetime

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-28 : 20:10:37

Due to the length, complexity, and format of the stored procedure that was posted, it is almost impossible for us to help you. I would highly recommend hiring a consultant who can work on-site with you to get this working. If that isn't feasible, you are going to need to provide a lot more information for us to assist. The type of information that we will need is DDL for all tables involved in the stored procedure, sample data for these tables using INSERT INTO statements (so that we can play with it on our machines), sample inputs of the stored procedure along with the expected result set when those parameters are passed in, and anything else that might help us help you.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources