How to load a list of Integer into temp table by SP

3 Aug 2006 12:27 PM

mullin

hi, i have a sp and one of the input parameters is a list of integer,
i.e. '2181440,2183149,21221111'.

how can i convert it into temp table at sp?

create table #table1 (
AccountCode int
)

3 Aug 2006 12:34 PM

Uri Dimant

Use Anith's example

SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO

create table #table1 (
AccountCode int
)

DECLARE @Ids VARCHAR(200)
SET @Ids = '5,33,229,1,22'

INSERT INTO #table1
SELECT SUBSTRING(@Ids, n, CHARINDEX(',', @Ids + ',', n) - n)
from numbers where substring(','+@Ids,n,1)=','
AND n < LEN(@Ids) + 1
drop table Numbers

SELECT * FROM #table1

drop table Numbers

Show quote

"mullin" <mullin***@gmail.com> wrote in message
news:1154608067.778146.325460@i42g2000cwa.googlegroups.com...
> hi, i have a sp and one of the input parameters is a list of integer,
> i.e. '2181440,2183149,21221111'.
>
> how can i convert it into temp table at sp?
>
> create table #table1 (
> AccountCode int
> )
>

3 Aug 2006 12:36 PM

Dan Guzman

See http://www.sommarskog.se/arrays-in-sql.html for some techniques.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"mullin" <mullin***@gmail.com> wrote in message
news:1154608067.778146.325460@i42g2000cwa.googlegroups.com...
> hi, i have a sp and one of the input parameters is a list of integer,
> i.e. '2181440,2183149,21221111'.
>
> how can i convert it into temp table at sp?
>
> create table #table1 (
> AccountCode int
> )
>

3 Aug 2006 12:38 PM

msnews.microsoft.com

You can split strings in a stored proc. Rough example:

if @string is not null
begin
select * into #temp from split(@string,',')
end

You can also do an insert directly into a table of your choice instead of a
temp table.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

Show quote

"mullin" <mullin***@gmail.com> wrote in message
news:1154608067.778146.325460@i42g2000cwa.googlegroups.com...
> hi, i have a sp and one of the input parameters is a list of integer,
> i.e. '2181440,2183149,21221111'.
>
> how can i convert it into temp table at sp?
>
> create table #table1 (
> AccountCode int
> )
>

3 Aug 2006 12:55 PM

Uri Dimant

Split of T-SQL ? Are you sure?

Show quote

"msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message
news:eS2sTmvtGHA.4336@TK2MSFTNGP06.phx.gbl...
> You can split strings in a stored proc. Rough example:
>
> if @string is not null
> begin
> select * into #temp from split(@string,',')
> end
>
> You can also do an insert directly into a table of your choice instead of
> a temp table.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************************************************
> Think Outside the Box!
> *************************************************
> "mullin" <mullin***@gmail.com> wrote in message
> news:1154608067.778146.325460@i42g2000cwa.googlegroups.com...
>> hi, i have a sp and one of the input parameters is a list of integer,
>> i.e. '2181440,2183149,21221111'.
>>
>> how can i convert it into temp table at sp?
>>
>> create table #table1 (
>> AccountCode int
>> )
>>
>
>

3 Aug 2006 2:24 PM

Tracy McKibben

mullin wrote:

> hi, i have a sp and one of the input parameters is a list of integer,
> i.e. '2181440,2183149,21221111'.
>
> how can i convert it into temp table at sp?
>
> create table #table1 (
> AccountCode int
> )
>

One method:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/ParseDelimitedStringToTable

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com