Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 28th, 2005, 07:09 PM
Authorized User
 
Join Date: Nov 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Very Complicated Stored Proc

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

 
Old November 28th, 2005, 08:03 PM
Friend of Wrox
 
Join Date: Jun 2004
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

Can u tell what error it gives?

Regards
Ganesh
 
Old November 28th, 2005, 08:05 PM
Authorized User
 
Join Date: Nov 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Its just not retreiving any results

 
Old November 28th, 2005, 08:13 PM
Friend of Wrox
 
Join Date: Jun 2004
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

since there is not any errors which are thrown, it definelty seems to be the problem with the date format? can u try to pass it as 'yyyy-mm-dd' insted

Regards
Ganesh
 
Old November 28th, 2005, 08:16 PM
Authorized User
 
Join Date: Nov 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How can i do that?

 
Old November 29th, 2005, 01:53 PM
Friend of Wrox
 
Join Date: Jun 2004
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

try to pass
@dtFrom = "01/01/2000"
@dtTo = "01/01/2099"
as
@dtFrom = "2000/01/01
@dtTo = "2099/01/01"


Regards
Ganesh
 
Old November 29th, 2005, 07:58 PM
Authorized User
 
Join Date: Nov 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks mate

I managed to get it to work

Cheers

 
Old November 29th, 2005, 09:02 PM
Friend of Wrox
 
Join Date: Jun 2004
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

cool..

Regards
Ganesh





Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Proc vs Functions. carumuga SQL Server 2000 1 January 7th, 2008 07:17 AM
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
optional where in stored proc david_ste SQL Server ASP 2 October 27th, 2005 07:16 AM
Simplify the stored proc stephanel SQL Server 2000 3 August 5th, 2003 08:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.