[thelist] ASP/SQL: Parameter for IN () clause

[darren]

On Tuesday, May 28, 2002 at 02:49, Paul Cowan wrote:

PC> Eric Engelmann <eric.engelmann at geonetric.com> wrote:

>> I want to pass a string: "23,45,71,34,56" as a string parameter (I assume)
>> to a SQL stored procedure for use in an IN() statement.


PC> What you can do is build an NVARCHAR inside the stored proc... so

PC>     DECLARE @SQL NVARCHAR(4000)

PC>     SET @SQL = 'SELECT [CompanyName] FROM [COMPANIES] WHERE [COMPANYID] IN
PC> (' + @strCompanyIDList + ')'

PC>     call sp_executesql @SQL

this is a great way of executing strings in stored procs.  but beware,
the user that is calling the sproc must have select permissions on the
table that you are selecting from.

(normally a select * from ... in a stored proc runs under the
ownership of the stored proc - usually dbo - but when you use
exec(@sqlString) or sp_executesql the proc runs under the permissions
of the user calling it)

also, if you are executing the same dynamic sql string multiple times
with slightly different parameters, sp_executesql seems to be less
likely than execute to cause recompiles of your sproc as it can cache
an execution plan.

PC> Note that doing this is obviously a lot messier, and may or may not totally
PC> shaft your query performance due to recompilation issues etc.

another way, which we've found to be as fast as executing dynamic sql
is to pass the string in and to chop it on a delimiter into a temp
table then run the select and inner join the temp table.

loads of stuff on turning dynamic sql into static sql can be found on
www.sqlservercentral.com and search for Robert Marda.  registration
may be required, but it's free.

hth,

darren.