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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy