Single and multiple parameter - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Single and multiple parameter

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2015-01-16 : 21:45:05

Hello SQL expert,

Below is my simple data and I want to make ID as my parameter. How do I query below data so I can enter either a single value like, 23, 06 or 15 or ALL value at once? ID data type is INT.

ID Country
23 USA
14 Africa
15 Asia
17 USA
24 Asia
06 Australia
07 Asia
23 USA
45 Africa
47 Rusia
21 India
33 USA
35 Africa
38 India
39 Australia
01 Asia
05 USA
06 USA
07 Asia
29 Australia
22 India
21 USA

Thank you.

SQLBoy

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-17 : 02:16:21


DECLARE @vcIDs AS VARCHAR(500)
-- used VARCHAR because in this way I am able to pass a list of IDs
SET @vcIDs ='23,14,07'
--SET @vcIDs ='24'


-- now will create cte and fill it with individual ID splitted.
;WITH cteIDs
AS
	(
		SELECT t.u.value('.','INT') AS ids
		FROM
			(
			SELECT CAST('<ID>'+ REPLACE(@vcIDs,',','</ID><ID>') + '</ID>' AS XML) AS xmlVar) AS X
			CROSS APPLY X.xmlVar.nodes('ID') t(u)
	)

--SELECT * FROM cteIDs
,Countries(ID,Country)
AS
(
	SELECT 23, 'USA' UNION ALL
	SELECT 14, 'Africa' UNION ALL
	SELECT 15, 'Asia' UNION ALL
	SELECT 17, 'USA' UNION ALL
	SELECT 24, 'Asia' UNION ALL
	SELECT 06, 'Australia' UNION ALL
	SELECT 07, 'Asia'
)


SELECT
	CNT.*
FROM
	Countries AS CNT
	INNER JOIN cteIDs AS I
	ON CNT.ID=I.ids