August 5, 2013 at 8:58 am
Hello all.
I am trying to use my first IF statement within a SP. The concept is simple. I have a aspx web form where the user can select a CATEGORY (sends a Cat ID # which is a Int) and a "search phrase". Variables are sent to SQL Server via SP and results are spit out onto a page.
The drop down list for "categories" also has a "ALL Categories" option in case the user wants to search them all. I have hard coded this "All" value to 9999. So the HTML behind the drop down list might look like this:
<select name="ddlCategories" ....>
<option value="">**Please Select**</option>
<option selected="selected" value="9999"><ALL Categories></option>
<option value="5">Application Fees</option>
...etc...
My code then (asp.net) then passes the form values to the SP and gets the results back. Simple.
I am trying to write the SP to use one of two SELECT Statements. This is what I have below but it seems to error on the last "END".
Little help??
Create Procedure [dbo].[sp_SearchDownloadsResults-Beta]
@strSearchPhrase varchar(25),
@intCategoryID Int
AS
BEGIN
-- User did NOT Select ALL Categories and searched by a specific category.
If @intCategoryID <> 9999 Begin
SELECT ....stufff.....FROM...stuff....
WHERE
((dbo.Downloads.Active = 1))
AND
(
(dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' )
OR
(dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' )
)
AND
(dbo.Downloads.DownloadCategoryID = @intCategoryID)
ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASC
End
--User opted to search by ALL categories.
Else Begin
SELECT ....etc....
WHERE
((dbo.Downloads.Active = 1))
AND
(
(dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' )
OR
(dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' )
)
ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASC
End <--- This is where is seems to expect more.....
August 5, 2013 at 9:26 am
Corrected. Removed "BEGIN".
Works so far.
August 5, 2013 at 9:49 am
RedBirdOBX (8/5/2013)
Corrected. Removed "BEGIN".Works so far.
Yes from what I read in your initial post you should either remove the first Begin after as or add another END as the very last statement in your SP.
Create Procedure [dbo].[sp_SearchDownloadsResults-Beta]
@strSearchPhrase varchar(25),
@intCategoryID Int
AS
BEGIN -- Begin SPROC code
-- User did NOT Select ALL Categories and searched by a specific category.
If @intCategoryID <> 9999 Begin
SELECT ....stufff.....FROM...stuff....
WHERE
((dbo.Downloads.Active = 1))
AND
(
(dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' )
OR
(dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' )
)
AND
(dbo.Downloads.DownloadCategoryID = @intCategoryID)
ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASC
End
--User opted to search by ALL categories.
Else Begin
SELECT ....etc....
WHERE
((dbo.Downloads.Active = 1))
AND
(
(dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' )
OR
(dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' )
)
ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASC
End <--- This is where is seems to expect more.....
End -- End the Sproc code
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply