very complicated stored proc
-
Run the query you've got from Profiler in Query Analiser, and it will tell you everything, if you don't beleive in my words.
_____________
Code for TallyGenerator -
I know what u are trying to tell me
but that @searchtype is set to 0 when the user does not click on the check box, which is the advanced search, and it simply does a normal sql search from the table
But if the user clicks on the checkbox, searchtype becomes -1 and the stored proc already caters for that.
I hope u are understanding what I am tryint to say
-
OK, what happens when you run it from QA?
I mean
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
_____________
Code for TallyGenerator -
it returns no results
-
And no errors???
Did you include this part for debug table?
Why it does not create the table, if there are no errors?
_____________
Code for TallyGenerator -
oh
it created the table and returned two rows now
DateFrom 1/1/2000
DateTo 1/1/2099
-
I decided to re-write the code
and I am doing a simple query now:-
CREATE PROCEDURE [dbo].[stpEventsSearchResultNew]
@searchstring varchar(100),
@dtFrom datetime,
@dtTo datetime,
@searchtype int
AS
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
If @searchtype = 0
BEGIN
Select * from pod_Events WHERE e_title LIKE @searchstring or e_description LIKE @searchstring
END
else
BEGIN
SELECT pod_Events.id_e, pod_Events.e_title, pod_Events.e_description, pod_Events.date_posted, pod_Schools.s_name AS School,
pod_Zone.z_name, pod_RelevantIssues.ri_Description, pod_Involvement.i_Description, dbo.pod_Events.e_filename
FROM pod_Events INNER JOIN
pod_Event_Schools ON pod_Events.id_e = pod_Event_Schools.id_e INNER JOIN
pod_Schools ON pod_Event_Schools.id_s = pod_Schools.id_s INNER JOIN
pod_Zone ON pod_Schools.id_z = pod_Zone.id_z INNER JOIN
pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN
pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN
pod_RelevantIssues ON pod_Event_RI.ri_id = pod_RelevantIssues.ri_id INNER JOIN
pod_Involvement ON pod_Event_Inv.i_id = pod_Involvement.i_id
WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= @dtFrom) AND (date_posted <= @dtTo)
END
GO
and still not returning any results.
I am assuming that when I enter the date, I am not entering it in a correct format
I am doing the following at the moment to insert the date in the database:-
cmd.Parameters.Add("@date_posted", SqlDbType.DateTime.DateTime)
cmd.Parameters("@date_posted").Value = Convert.ToDateTime(dt)
Since i am getting the date from a text field
Is this correct?
-
Looks all right, it must be data.
Run this:
select count(*)
from pod_Events
and comment all futher joins.
If result will be > 0 uncomment 1st join and rerun.
Finally you'll find the join which eliminates resultset.
_____________
Code for TallyGenerator -
you mean I comment all the inner joins in the stored proc???
-
Yes, copy text of SP to Query Analiser, make it ALTER PROCEDURE, and add execution string at the end.
Now you can play with it, no application involved anymore.
After you have found and fixed the problem restore original SELECT.
_____________
Code for TallyGenerator -
so if the count gives me 4 for example, there's a problem?
To tell you the truth, I starter re-writing this SP since I think there is too much complication for nothing
-
It's for you to decide.
If count(*) = 4 then 4 rows will be returned by normal select.
As I understand you get empty resultset, and that's a problem. So keep moving until next restored join will leave you woth count = 0
_____________
Code for TallyGenerator -
oh ok thanks
I will keep you informed then
Thanks for ur help Sergiy
-
Sergiy
I am trying to build the stored proc step by step
So the above code to retreive all the data from the database:-
CREATE PROCEDURE [dbo].[stpEventsSearchResultNew]
@searchstring varchar(100),
@dtFrom datetime,
@dtTo datetime,
@searchtype int
AS
If @searchtype = 0
BEGIN
Select * from pod_Events WHERE e_title LIKE @searchstring or e_description LIKE @searchstring
END
else
BEGIN
SELECT pod_Events.id_e, pod_Events.e_title, pod_Events.e_description, pod_Events.date_posted, pod_Schools.s_name AS School,
pod_Zone.z_name, pod_RelevantIssues.ri_Description, pod_Involvement.i_Description, dbo.pod_Events.e_filename
FROM pod_Events INNER JOIN
pod_Event_Schools ON pod_Events.id_e = pod_Event_Schools.id_e INNER JOIN
pod_Schools ON pod_Event_Schools.id_s = pod_Schools.id_s INNER JOIN
pod_Zone ON pod_Schools.id_z = pod_Zone.id_z INNER JOIN
pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN
pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN
pod_RelevantIssues ON pod_Event_RI.ri_id = pod_RelevantIssues.ri_id INNER JOIN
pod_Involvement ON pod_Event_Inv.i_id = pod_Involvement.i_id
WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= convert(varchar,@dtFrom,108)) AND (date_posted 0
BEGIN
SELECT dbo.pod_Events.id_e, dbo.pod_Events.e_title, dbo.pod_Events.e_description, dbo.pod_Events.date_posted, dbo.pod_Schools.s_name AS School,
dbo.pod_Zone.z_name, dbo.pod_RelevantIssues.ri_Description, dbo.pod_Involvement.i_Description, dbo.pod_Events.e_filename,
dbo.pod_Schools.id_z
FROM dbo.pod_Events INNER JOIN
dbo.pod_Event_Schools ON dbo.pod_Events.id_e = dbo.pod_Event_Schools.id_e INNER JOIN
dbo.pod_Schools ON dbo.pod_Event_Schools.id_s = dbo.pod_Schools.id_s INNER JOIN
dbo.pod_Zone ON dbo.pod_Schools.id_z = dbo.pod_Zone.id_z INNER JOIN
dbo.pod_Event_RI ON dbo.pod_Events.id_e = dbo.pod_Event_RI.id_e INNER JOIN
dbo.pod_Event_Inv ON dbo.pod_Events.id_e = dbo.pod_Event_Inv.id_e INNER JOIN
dbo.pod_RelevantIssues ON dbo.pod_Event_RI.ri_id = dbo.pod_RelevantIssues.ri_id INNER JOIN
dbo.pod_Involvement ON dbo.pod_Event_Inv.i_id = dbo.pod_Involvement.i_id
WHERE (dbo.pod_Schools.id_z = @region) AND
(e_title LIKE @searchstring OR e_description LIKE @searchstring)
AND (dbo.pod_Events.date_posted BETWEEN convert(varchar,@dtFrom,108) AND convert(varchar,@dtTo,108))
END
else
SELECT pod_Events.id_e, pod_Events.e_title, pod_Events.e_description, pod_Events.date_posted, pod_Schools.s_name AS School,
pod_Zone.z_name, pod_RelevantIssues.ri_Description, pod_Involvement.i_Description, dbo.pod_Events.e_filename
FROM pod_Events INNER JOIN
pod_Event_Schools ON pod_Events.id_e = pod_Event_Schools.id_e INNER JOIN
pod_Schools ON pod_Event_Schools.id_s = pod_Schools.id_s INNER JOIN
pod_Zone ON pod_Schools.id_z = pod_Zone.id_z INNER JOIN
pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e INNER JOIN
pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e INNER JOIN
pod_RelevantIssues ON pod_Event_RI.ri_id = pod_RelevantIssues.ri_id INNER JOIN
pod_Involvement ON pod_Event_Inv.i_id = pod_Involvement.i_id
WHERE (e_title LIKE @searchstring or e_description LIKE @searchstring) AND (date_posted >= convert(varchar,@dtFrom,108)) AND (date_posted 0 or even 0
Do i have something wrong?
-
I cannot see any difference between 1st option of the code and "region 0" part in 2nd one. Probably I miss some tiny detail, but you must have used copy-paste, so they must be identical and return identical results.
2nd code must return the same set with @region = 0 and @searchtype = -1 as 1st code with @searchtype = -1.
There must be something out of this script making difference.
It's kind of mistery, but I don't believe in miracles within T-SQL scripts. It's always something really silly sitting in front of your eyes but out of your sight.
Just 2 important notes:
1. Do not use
(date_posted >= convert(varchar,@dtFrom,108)) AND (date_posted = @dtTo and @dtFrom) -that's a right version of WHERE clause.
And if date_posted is not datetime it should be.
2. ALWAYS qualify object names with owner. If you mean to use dbo.pod_Events you must make sure you use the same table in every piece of code. And it's not only about tables. Views, SP, UDF are probably more affected, because they use to be scripted for creation. And if in DEV evironment you always login as "sa" and all objects owned by dbo automatically, it does not mean that it will work the same way in production.
BTW check if you have now objects owned not by dbo. Maybe it's a part of the problem.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply