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
Hi


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


output:

ID Country
23 USA
14 Africa
7 Asia




sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-17 : 02:19:06
If your system have a function that split the Ids, you can use it and fill it into a #tmpTable.
Then use the #tmp in the final join.




sabinWeb MCP
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 06:23:10
Use table valued parameter in the stored procedure.

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -