Can SQL Server do this?

I was curious.
I am building a filter string on a webpage to pass to a stored
procedure. The filter string would be some kind of IN clause.
Is there a way to pass the IN clause to the stored procedure?
For instance, let's say you have a stored procedure called MyProc
It takes 1 parameter, maybe called @.alpha, potentially a string like
"('CA','MA')"
Is there a way that you could say something like:
WHERE @.alpha
In other words, pass the filter string directly to the SP? I tried it
and couldn't get it to work and I was wondering if there were a way to
even do this so I can keep the query as a stored procedure.Hi Brent
Before we start I am hoping that you are aware with code injection issues
that the dynamic creation of SQL statements can lead to. I advise strongly
against building dynamic queries as such because the security implications
are enormous.
I would possible prepopulate a temp table such as the following.
CREATE TABLE #criteria ( lookup varchar(50))
INSERT #criteria (lookup) VALUES("John")
INSERT #criteria (lookup) VALUES("Peter")
INSERT #criteria (lookup) VALUES("Alan")
Then call the procedure
EXEC sp_lookup
With will do something like
SELECT somefield FROM sometable s where EXISTS ( SELECT * FROM #criteria
WHERE s.somelookupfield = #criteria.lookup)
This may require you to recode some of the page but it is safer, as long of
course as you validate the Web form input first so it doen't get injectect
into the insert statements.
Hope this helps
Phil
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||The only way you can do that is to use dynamic sql. This can introduce
security concerns, so you should be careful about using it. There have been
many posts on this newsgroup about dynamic sql--some with links to very good
information, so I suggest you review them and consider all of the
ramifications before implementing it.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||I personally prefer this method:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
...if you need it in pure T-SQL.
ML|||I use a method described here:
http://www.sommarskog.se/arrays-in-sql.html
In the table of contents, click on "List-of-strings".
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>