Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
How to create array in sql server 2008. for implementing split function to hold the string type value.through foreach function.
Posted 9-Oct-12 2:05am
Comments
vivektiwari97701 at 9-Oct-12 7:14am
   
there is not a concept in SQL language as an array. SQL is a set based language. its better to upade your qus. and write your problem Properly.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can't create an array in SQL server - it has no concept of them as a datatype.
 
Perhaps this will help - it shows a way to pass a list as a string parameter:
DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I think following code block can help you
 

 
DECLARE @ids VARCHAR(100) = '7,3,120';
DECLARE @Xparam XML;
--Convert @ids to XML 
SELECT @Xparam = CAST('<i>' + REPLACE(@ids,',','</i><i>') + '</i>' AS XML)
 
--Query to compare the id against the ids result set by splitting the XML nodes 
--as a result set
SELECT * FROM table WHERE Id IN (SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i))
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

See the top 3 links given by Google[^]:
Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters[^]
How to declare array inside Sql Server Stored Procedure?[^]
how to create an array in sql server 2008[^]
 
By reading these articles and discussions, I think you can implement the split function to split the strings..
 
--Amit
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 450
1 OriginalGriff 230
2 DamithSL 150
3 Dave Kreskowiak 120
4 Suvendu Shekhar Giri 110
0 OriginalGriff 7,740
1 DamithSL 5,644
2 Sergey Alexandrovich Kryukov 5,404
3 Maciej Los 5,011
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 10 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100