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

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


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com