[dba-SQLServer] Re: How To: Create a Stored Procedure using a parameter for IN

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





More information about the dba-SQLServer mailing list