SQL Server - Very Complicated 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

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/2026"
@dtTo = "01/01/2026"

Hi Johann,
You've only provided the stored procedure but you didn't explain what the problem is (so I will guess).  I will assume that you are not getting any results when you run your stored procedure.  If this is the case, change all your WHERE clause that uses the LIKE operator, from this:
WHERE e_title LIKE @searchstring 
to this:
WHERE e_title LIKE '%' + @searchstring + '%'

Hi Johann,
When passing parameters, you should use single-quotes instead of double-quotes:
@dtFrom = '01/01/2026'
@dtTo = '01/01/2026'
If this doesn't solve your problem, please post the exact error message you are getting.

Hi Ronaldo
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#
Its not actually giving me an error, its not retreiving any results.  This is a global search so i t must retreive all records
How can i debug the stored proc?

Hi Johann,
To debug the stored procedure, you can use Query Analyzer.  Simply use the EXEC command to execute the stored procedure and pass the parameters in the same order as how they are specified in your SP:
EXEC [dbo].[stpEventsSearchResult] <Pass the parameters here>
If you get a result set from Query Analyzer, then you should post your client-code.  If you don't get any result, then there could be something wrong with the sp or maybe in the data.

No it is not retreiving any results, so there must be something wrong
I tried to debug but it is giving me this error:-
SP debugger may not work properly if you log on as 'Local System Account'
How can i log on to debug?  And how do go through the stored proc, what commands I mean?

Hi Johann,
One way of debugging your code is to execute each SELECT statement you have and replacing the variables with the actual values you are passing as parameters.  Try with the following SELECT statement:
Select * from pod_Events WHERE e_title LIKE @searchstring or e_description LIKE @searchstring 
If this doesn't give any result, make sure that you include the % wildcard character before and after the search string.

I tried to do that with a few vars, however when i return the stored proc as a dataset, and bind it to the datagrid, its telling me that "Object reference not set to an instance of an object"
Would u like me to send u the code?  Maybe its better?

Yes, to give us a better understanding of what's happening both in the SQL Server side as well as in the client-side, please post your code.

can i send u everything by mail?

Unfortunately, no.  This is to give other posters a chance to answer also.

ok here it goes
    Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
        ev = New EventsInfo
        Dim item As ListItem
        For Each item In lboxRelevantIssues.Items
            If item.Selected Then
                Dim dr As DataRow
                dr = dsRI.stpSelectRelevantIssues.NewRow
                dr("ri_id") = item.Value
                dr("ri_description") = item.Text
                dsRI.stpSelectRelevantIssues.Rows.Add(dr)
            End If
        Next
        For Each item In cblEvInvolve.Items
            If item.Selected Then
                Dim dr As DataRow
                dr = dsRI.stpSelectInvolvement.NewRow
                dr("i_id") = item.Value
                dr("i_description") = item.Text
                dsRI.stpSelectInvolvement.Rows.Add(dr)
            End If
        Next
        If txtDateFrom.Text = "" Then
            txtDateFrom.Text = "01/01/2026"
        End If
        '-------------------------------JM CODE------------------------------
        Dim myFromDate As DateTime = Convert.ToDateTime(txtDateFrom.Text)
        'Dim myFromDate As String = txtDateFrom.Text
        '-------------------------------END JM CODE------------------------------
        If txtDateTo.Text = "" Then
            txtDateTo.Text = "01/01/2026"
        End If
        '-------------------------------JM CODE------------------------------
        Dim myToDate As DateTime = Convert.ToDateTime(txtDateTo.Text)
        'Dim myToDate As String = txtDateTo.Text
        '-------------------------------END JM CODE------------------------------
        dgSearchResult.DataSource = ev.GetSearchResult("%" & txtSearchFor.Text & "%", chkAdvSearch.Checked, myFromDate, myToDate, regId, schId, dsRI).Tables("stpEventsSearchResult").DefaultView
        dgSearchResult.DataBind()
        pnlSeachTitles.Visible = True
    End Sub
   Public Function GetSearchResult(ByVal srcstring As String, ByVal srctype As Boolean, ByVal dtFrom As Date, ByVal dtTo As Date, ByVal reg As Integer, ByVal sch As Integer, ByVal ri As dsSearch) As DataSet
        Dim ds As DataSet = New DataSet
        Dim dataAdapter As SqlDataAdapter
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        ' create the conection object for scalar reader to use
        conn = New SqlConnection
        conn.ConnectionString = ConnectionString
        ' return the players pk_player
        cmd = New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = conn
        cmd.CommandText = "stpEventsSearchResult"
        cmd.Parameters.Add("@searchstring", SqlDbType.VarChar)
        cmd.Parameters("@searchstring").Value = srcstring
        cmd.Parameters.Add("@dtFrom", SqlDbType.DateTime)
        cmd.Parameters("@dtFrom").Value = dtFrom
        cmd.Parameters.Add("@dtTo", SqlDbType.DateTime)
        cmd.Parameters("@dtTo").Value = dtTo
        cmd.Parameters.Add("@searchtype", SqlDbType.Int)
        cmd.Parameters("@searchtype").Value = CType(srctype, Integer)
        cmd.Parameters.Add("@region", SqlDbType.Int)
        cmd.Parameters("@region").Value = reg
        cmd.Parameters.Add("@school", SqlDbType.Int)
        cmd.Parameters("@school").Value = sch
        cmd.Parameters.Add("@ri_id", SqlDbType.Int)
        cmd.Parameters("@ri_id").Value = 0
        cmd.Parameters.Add("@i_id", SqlDbType.Int)
        cmd.Parameters("@i_id").Value = 0
        If ri.stpSelectRelevantIssues.Rows.Count = 0 And ri.stpSelectInvolvement.Rows.Count = 0 Then
            dataAdapter = New SqlDataAdapter(cmd)
            dataAdapter.Fill(ds, "stpEventsSearchResult")
        Else
            If ri.stpSelectRelevantIssues.Rows.Count > 0 Then
                Dim i As Integer
                For i = 0 To ri.stpSelectRelevantIssues.Rows.Count - 1
                    cmd.Parameters("@ri_id").Value = ri.stpSelectRelevantIssues.Rows(i)("ri_id")
                    dataAdapter = New SqlDataAdapter(cmd)
                    dataAdapter.Fill(ds, "stpEventsSearchResult")
                Next
            End If
            If ri.stpSelectInvolvement.Rows.Count > 0 Then
                Dim i As Integer
                For i = 0 To ri.stpSelectInvolvement.Rows.Count - 1
                    cmd.Parameters("@i_id").Value = ri.stpSelectInvolvement.Rows(i)("i_id")
                    dataAdapter = New SqlDataAdapter(cmd)
                    dataAdapter.Fill(ds, "stpEventsSearchResult")
                Next
            End If
        End If
        Return ds
    End Function

Hi Johann,
Here's a few things that you can check for:
1.  Make sure that when you run the stored procedure from Query Analyzer using the same parameters that you send from the application that you get your desired output.
2.  Step through your code and identify specifically the line that produced your error.  That's a starting point of identifying where the problem is.
3.  Try specifying the length of a VARCHAR parameter, particularly this one:
cmd.Parameters.Add("@searchstring", SqlDbType.VarChar) 
As far as I know, if it is not specified, it defaults to a value of 1.
Hope this helps.

Thanks mate
I managed to get it to work.  I had some corrupt data in the database.
Tahnsk for everything
Johan