Robert L. Stewart
rl_stewart at highstream.net
Thu Feb 19 15:07:29 CST 2004
Arthur,
You are correct. That is why I am
continuing with the information that
the other Robert sent to me.
I wanted something quick that I could
use for an immediate need. The code
that Robert sent to me seems to work
well and will allow me to use a sub-query
to make the process smoother and more
efficient.
Robert
At 12:00 PM 2/19/2004 -0600, you wrote:
>Date: Wed, 18 Feb 2004 17:56:52 -0800
>From: "Arthur Fuller" <artful at rogers.com>
>Subject: RE: [dba-SQLServer] Re: How To: Create a Stored Procedure
> using aparameter for IN
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <007501c3f68b$a5f54eb0$6601a8c0 at rock>
>Content-Type: text/plain; charset="us-ascii"
>
>IMO you permanently defeat the optimizations using this approach.
>
>Arthur
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert
>L. Stewart
>Sent: Monday, February 16, 2004 1:54 PM
>To: marion meh3
>Cc: Robert.Djabarov at usaa.com; Eric Easley
>Subject: [dba-SQLServer] Re: How To: Create a Stored Procedure using
>aparameter for IN
>
>
>Final SQL Stored Procedure:
>
>CREATE PROCEDURE usp_Build_In
> @Select varchar(500),
> @From varchar(150),
> @Where varchar(500),
> @In varchar(500),
> @OrderBy Varchar(250)
>AS
>BEGIN
> DECLARE @SQL varchar(1900)
> SET @sql = @Select + @From + @Where + @In + @Orderby
> EXEC(@sql)
>END
>
>To Use it:
>
>SET QUOTED_IDENTIFIER OFF
>GO
>exec usp_Build_In 'SELECT ClientID,ResidenceType,LastName ',
> 'FROM tbl_Client ',
> 'WHERE LastName IN(',
> "'Stewart','Jones')",
> 'ORDER BY LastName'
>
>Notice the Double-quotes on the @In parameter.
>
>Robert, I am still working on the idea of using the subquery. The code
>you sent to me only returns one row, the last one in the list. But I
>will have more time to work with it tomorrow.
>
>RLS