November 28, 2005 at 3:57 pm
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
November 28, 2005 at 4:07 pm
What's wrong with it?
Replace
AND (pod_Zone.id_z = @region) AND (pod_Users_Schools.id_s = @school) AND AND (pod_Event_RI.ri_id = @ri_id) AND (pod_Event_Inv.i_id = @i_id)
with
AND (pod_Zone.id_z = @region OR @region = 0) AND (pod_Users_Schools.id_s = @school OR @school = 0) AND AND (pod_Event_RI.ri_id = @ri_id OR @ri_id = 0) AND (pod_Event_Inv.i_id = @i_id OR @i_id = 0)
if it simplify your life.
At least you will maintain 1 statement, not 16.
_____________
Code for TallyGenerator
November 28, 2005 at 4:12 pm
I do not know what is exactly wrong.
The problem is that since i arranged the dates to datetime, its not working. So I am assuming that its the problem.
I am passing the dates to the @dtFrom and @dtTo parameters as follows:-
@dtFrom = "01/01/2000"
@dtTo = "01/01/2099"
Is that the correct format?
November 28, 2005 at 4:28 pm
Your dates are in datetime datatype.
So pass datetime, not varchar:
@dtFrom = convert(datetime, '01/01/2000', 103)
@dtTo = convert(datetime, '01/01/2099, 103)
Otherwise you depend on current DATEFORMAT settings.
_____________
Code for TallyGenerator
November 28, 2005 at 4:41 pm
I am doing this:-
Dim myToDate As DateTime = Convert.ToDateTime(txtDateTo.Text)
and passing myToDate.
I noticed that It was still passing the argument as text so I changed it to Date ie:-
ByVal dtFrom As Date, ByVal dtTo As Date
So now I am passing the vars as #1/1/2000#
How can i debug the stored proc?
November 28, 2005 at 4:49 pm
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[debug_SP_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[debug_SP_table]
SELECT @dtFrom as DateFrom, @dtTo as DateTo
INTO debug_SP_table
and see what you really pass.
_____________
Code for TallyGenerator
November 28, 2005 at 5:01 pm
Sorry but where do I put this code?
I tried to put it after my stored proc but gave me an error
'@dtFrom not declared
November 28, 2005 at 5:05 pm
No, it must be first statement inside your SP, just after "AS".
_____________
Code for TallyGenerator
November 28, 2005 at 5:12 pm
Ok i executed that code.
I assume its supposed to be with my tables right?
Cause there is nothing there
November 28, 2005 at 5:33 pm
What do you mean "there is nothing there "?
No table created, no lines in the table, or all values are NULL?
Can you catch SP call with Profiler?
_____________
Code for TallyGenerator
November 28, 2005 at 5:37 pm
no table created
Sorry did not understand what catch SP call with Profiler, i am a newbie on sql server
November 28, 2005 at 5:42 pm
In Enterprise Manager console in menu "Tools" press "SQL Profiler".
Connect it to your database and you'll see all queries are running against this database.
Press the button in your application and catch the SQL statement it passed to Server.
_____________
Code for TallyGenerator
November 28, 2005 at 5:50 pm
ok this is the SQL statement
exec stpEventsSearchResult @searchstring = '%%', @dtFrom = 'Jan 1 2000 12:00AM', @dtTo = 'Jan 1 2099 12:00AM', @searchtype = -1, @region = 0, @school = 0, @ri_id = 0, @i_id = 0
November 28, 2005 at 5:56 pm
You don't pass required parameter @searchtype. That's why SP has never been started.
Add it to you call in application or change parameter definition to
@searchtype int = NULL (or whatever to be assigned if parameter is not passed)
_____________
Code for TallyGenerator
November 28, 2005 at 6:00 pm
but in the stored proc I have the following:-
If @searchtype = 0
BEGIN
Select * from pod_Events WHERE e_title LIKE @searchstring or e_description LIKE @searchstring
END
else
BEGIN
I mean this stored proc was working before I changed the date types. I cannot change them back since I am doing all the insertions as datetime
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply