I have some SQL code as inline SQL (bad habit, I know). Now I want to convert this to an sproc, but I'm pretty much out of ideas here. The code looks like this:
string
SQL ="SELECT * FROM MyDBTable WHERE 1=1";if (txtMyField1.Text !=""){
SQL = SQL +
" AND MyField1 = @.MyField";}
if (txtMyField2.Text !=""){
SQL = SQL +
" AND MyField2 LIKE '%'+ @.MyField2 + '%'";}
if (txtMyField3.Text !=""){
SQL = SQL +
" AND MyField3 LIKE '%' + @.MyField3 + '%'";}
I have an search page built on ASP.NET 2.0. Based on what the user has entered to the form fields, the SQL in constructed on the fly. Since this is now inside codebehind file (aspx.cs), I want to get rid of it and move it to an sproc. But the question is how ? Some simple SQL clauses are easy to convert to an sproc but this is causing me lots of issues.
You may try this see if it is working or not:
CREATE PROCEDURE dbo.sp_Test (
@.MyField varchar(5) = null,
@.MyField2 varchar(5)= null,
@.MyField3 varchar(5)= null
)
AS
Begin
declare @.Syntax varchar(500)
SELECT @.Syntax = 'SELECT * FROM MyDBTable' +
'WHERE 1=1' +
CASE
WHEN @.MyField IS NOT NULL and @.MyField <> ''
THEN ' AND MyField1 = ''' + @.MyField + ''''
ELSE ''
END +
CASE
WHEN @.MyField2 IS NOT NULL and @.MyField2 <> ''
THEN ' AND MyField2 LIKE ''%'+ @.MyField2 + '%'''
ELSE ''
END +
CASE
WHEN @.MyField3 IS NOT NULL and @.MyField3 <> ''
THEN ' AND MyField3 LIKE ''%'+ @.MyField3 + '%'''
ELSE ''
END
EXECUTE(@.syntax)
End
You need adjust the size of the variables as request.
|||Thanks for the code. I'll try this later and let you know how it worked.|||Thanks JimmyM for your assistance, it worked !!! However you had an typo in your reply, since FROM was there twice. But this wasn't an issue.|||Sorry for the typo, glad that the solution is working for you.sql