[dba-SQLServer] Ho To: Create a Stored Procedure using aparameterfor IN

Robert's second suggestion is definitely better for lots of reasons, but
I often use the first suggestion anyway.

A couple of things to remember when you use the "inferior" method -- any
time you use a table in a dynamic select statement you need to grant
select permission on the table to the users.  Also, I've gotten stumped
testing this in QA b/c the results panel will say successful, but there
is no data.  I put my sql string in a variable such as @sql_string
varchar(1000) and then use "print @sql_string" in my sproc for testing.

--Susan



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Djabarov, Robert
Sent: Monday, February 16, 2004 9:39 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Ho To: Create a Stored Procedure using
aparameterfor IN


Of course, you can do:

Exec ('select ... from tblClient where ResidencyType in (' + @ResType +
')')

But we all know what THIS means, right?

I'd parse @ResType into a temptable, and then replace your WHERE clause
with INNER JOIN on that table.  Works like a charm every time :)


Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert
L. Stewart
Sent: Saturday, February 14, 2004 4:30 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Ho To: Create a Stored Procedure using a
parameterfor IN

Sometime I think I go brain dead.  ;-)

I am trying to do a stored procedure with a simple SQL statement
like:

SELECT ClientID, LastName, FirstName, ResidenceType
FROM tblClient
WHERE ResidenceType IN(@ResType)

I need to be able to pass something like:

house, hotel, homeless

and get a list of people with that residency type.  Nothing I have tried
has worked.  Can anyone give me some direction on doing this?

Thanks,

Robert L. Stewart
The Dyson Group
Expanding your sphere of Knowledge


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

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