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

Jay Turley jayturley at gmail.com
Mon Aug 25 16:13:02 CDT 2008


When I did this for one of my applications, I passed the list of
comma-separated-values in as a varchar, and then parsed it in the SQL
stored procedure.

In addition, I passed in some flags to say how the CSVs should be handled.

Essentially, you end up using the information passed in to create the
actual sql as an NVARCHAR inside your stored procedure.

Then you do something like the following:

SELECT @sql = N'[bunch of sql goes here]';
SELECT @value1 = [some value];
SELECT @value2 = [some value];

SELECT @params = ''
SELECT @params = @params + N' @value1 VARCHAR(70)'
SELECT @params = @params + N', at value2 VARCHAR(4)'

EXEC sp_executesql @sql, @params, @value1 , @value2

HTH,

Jay

On Mon, Aug 25, 2008 at 1:52 PM, Joel D Canfield <joel at bizba6.com> wrote:
> 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?
>
> joel
>
> --
> Joel D Canfield
> Book at BizBa6.com
> 916.771.9297 office
> 916.765.1712 cell
> http://BizBa6.com/ <http://bizba6.com/>
>
>
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list