[thelist] mssql stored procedure-using a variable for 'AND' and 'OR'

[Ken Schaefer]

Note: if you are building an SQL statement dynamically inside your sproc, and then executing that string, then you are defeating the purpose of using parametised queries - you might as well just use inline SQL.

If this is ASP.NET, then a better way to do this would be to:
a) parse the string in your ASP.NET page
b) build the SQL statement in your ASP.NET page, using parameters (?)
c) supplying the parameter values derived from (a)
d) having ADO.NET send the statement to SQL

That will keep you safe from SQL injection attacks, whilst letting you build the SQL statement on the fly. You don't get the same code separation as using a sproc, or the security stuff, but it's a lot better than building an SQL statement dynamically inside a sproc.

Cheers
Ken

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org]
> On Behalf Of Joel D Canfield
> Sent: Tuesday, 26 August 2008 6:53 AM
> To: thelist at lists.evolt.org
> Subject: [thelist] mssql stored procedure-using a variable for 'AND' and 'OR'
>
> I'm building a simple search tool (trying to use parameterised queries,
> stored procs, all that secure stable stuff I've never used before.)
>
> I want to let the visitor enter as many comma-separated terms as they
> like, and select AND or OR for the search (of course, I'll use less
> geeky terminology in the tool.)
>
> I have no clue how to create a stored procedure which will accept an
> unknown number of search terms, nor how to make the 'AND' and 'OR'
> dynamic based on the user's choice.
>
> Thoughts?