pasing parameter in multiple value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

pasing parameter in multiple value

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-04-02 : 23:06:50

Dear All

I have a problem with my SQL syntax, the SQL syntax can't passing parameter array(multiple values). The SQL syntax like:

Declare @name as char(50)

Set @name='erick','david'
Select * from employee where name in(@name)

Is possible to pasing parameter in multiple value like we execute
SQL like
Select * from employee where name in('erick','david')

oh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-03 : 00:06:11

Using a CSV with an IN sub-select:

http://www.sqlteam.com/item.asp?ItemID=11499

Tara

xpandre
Posting Yak Master

212 Posts

Posted - 2004-04-05 : 07:49:05

This proc i got from this forum does it in style!!:D

USE Northwind
GO

CREATE PROC mySPROC99
  @csv varchar(1000)
, @delim varchar(10)
AS
  BEGIN
SELECT *
  FROM Suppliers
 WHERE @delim + @csv + @delim like '%' + @delim + region + @delim + '%'
  END
GO


SELECT * FROM Suppliers WHERE Region IN ('LA','MI','OR')

SupplierID  CompanyName                              ContactName                    ContactTitle                   Address                                                      City            Region          PostalCode Country         Phone                    Fax                      HomePage                                                                                                                                                                                                                                                         
----------- ---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- --------------- ------------------------ ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2           New Orleans Cajun Delights               Shelley Burke                  Order Administrator            P.O. Box 78934                                               New Orleans     LA              70117      USA             (100) 555-4822           NULL                     #CAJUN.HTM#
3           Grandma Kelly's Homestead                Regina Murphy                  Sales Representative           707 Oxford Rd.                                               Ann Arbor       MI              48104      USA             (313) 555-5735           (313) 555-3349           NULL
16          Bigfoot Breweries                        Cheryl Saylor                  Regional Account Rep.          3400 - 8th Avenue Suite 210                                  Bend            OR              97101      USA             (503) 555-9931           NULL                     NULL

(3 row(s) affected)



EXEC mySproc99 'LA,MI,OR',','

SupplierID  CompanyName                              ContactName                    ContactTitle                   Address                                                      City            Region          PostalCode Country         Phone                    Fax                      HomePage                                                                                                                                                                                                                                                         
----------- ---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- --------------- ------------------------ ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2           New Orleans Cajun Delights               Shelley Burke                  Order Administrator            P.O. Box 78934                                               New Orleans     LA              70117      USA             (100) 555-4822           NULL                     #CAJUN.HTM#
3           Grandma Kelly's Homestead                Regina Murphy                  Sales Representative           707 Oxford Rd.                                               Ann Arbor       MI              48104      USA             (313) 555-5735           (313) 555-3349           NULL
16          Bigfoot Breweries                        Cheryl Saylor                  Regional Account Rep.          3400 - 8th Avenue Suite 210                                  Bend            OR              97101      USA             (503) 555-9931           NULL                     NULL

(3 row(s) affected)



DROP PROC mySproc99
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-05 : 09:13:26

again, be careful with that technique if you have lots of rows in your data to search -- it cannot make use of indexes and forces a scan.

- Jeff

jimmyleg
Starting Member

1 Post

Posted - 2004-04-06 : 11:59:47

/*
Try this function. I have one for just about every datatype.
The idea came from SQL Magazine a while ago.
*/

SET NOCOUNT ON

USE tempdb
GO

IF OBJECT_ID('VarcharTable') IS NOT NULL DROP FUNCTION dbo.VarcharTable
GO

CREATE FUNCTION dbo.VarcharTable (@VarcharList varchar(4000), @DelimiterValue char(1))
RETURNS @tblVarcharTable TABLE (VarcharValue varchar(100))

AS

BEGIN
IF @DelimiterValue IS NULL SET @DelimiterValue = ','

DECLARE @Pointer int,
@VarcharListSubString varchar(4000)

SET @Pointer = 1
SET @VarcharListSubstring = ''

WHILE @Pointer < (LEN(@VarcharList) + 1)
BEGIN
IF SUBSTRING(@VarcharList, @Pointer, 1) <> @DelimiterValue
SET @VarcharListSubString = @VarcharListSubString + SUBSTRING(@VarcharList, @Pointer, 1)
ELSE
BEGIN
IF LEN(@VarcharListSubString) > 0
INSERT @tblVarcharTable(VarcharValue) VALUES (RTRIM(LTRIM(@VarcharListSubstring)))
SET @VarcharListSubstring = ''
END
SET @Pointer = @Pointer + 1
END
IF LEN(@VarcharListSubstring) > 0
INSERT @tblVarcharTable(VarcharValue) VALUES (RTRIM(LTRIM(@VarcharListSubstring)))
RETURN
END

GO

select * from pubs..titles
where pub_id in (select * from dbo.VarcharTable('1389, 0877', ','))
GO

IF OBJECT_ID('VarcharTable') IS NOT NULL DROP FUNCTION dbo.VarcharTable
GO

SET NOCOUNT OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources