|
|
Posted by Robin Tucker on 06/28/05 14:30
This is the version of split array return table from sommarskog. I use this
to pass in arrays of keys (space separated). It's more efficient than
populating a temporary table with keys programmatically every time you need
to join (as Celko seems to suggest). Although in general Celko is correct
from a purest point of view, I do believe that a small function like this
allowing you to pass and split arrays in a stored procedure has more utility
than it does downsides. For example, my client needs to periodically check
a set of rows to see if they have been changed. These records are in no
particular order (whichever rows the user happens to be viewing). Instead
of writing each one to a working table one by one and then executing an SP
to check their timestamps, I pass in an array of keys, split it and join
with the split table to return the update state.
However, in your example, perhaps a working table would be a better idea. I
would only advise using array splitting algorithms server-side if they are
just autonumber unique keys, rather than whole rows of information. It's
just a quickish method for fetching arbitrary rows from your tables.
CREATE FUNCTION dbo.func_Split_Array_Return_Table (@list NTEXT)
RETURNS @Table TABLE ( listpos INT IDENTITY(1, 1) NOT NULL, number INT NOT
NULL) AS
BEGIN
DECLARE @pos INT, @textpos INT, @chunklen SMALLINT, @str NVARCHAR(4000),
@tmpstr NVARCHAR(4000), @leftover NVARCHAR(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
@chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = charindex(' ', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @Table (number)
VALUES
(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(' ', @tmpstr)
END
SET @leftover = @tmpstr
END
IF ltrim(rtrim(@leftover)) <> ''
INSERT @Table (number)
VALUES
(convert(int, @leftover))
RETURN
END
"Mark" <mjmather@gmail.com> wrote in message
news:1119885730.339475.84280@g47g2000cwa.googlegroups.com...
>
> Hi everyone,
>
> I just need a bit of advice as to where to start tackling a problem, if
> thats possible - thanks very much.
>
> I need a single stored procedure to make several inserts into my msde
> database. There will be two arguments to the stored proc. The first
> is a title argument which needs to be inserted into the first table
> after which the autonumbered primary key is captured with @@identity.
>
> The second argument is a delimited list of foreign keys which need to
> be inserted into the second table along with the new key from the first
> statement. This table is a link table with two columns - both foreign
> keys - ie its the link table in a many to many relationship.
>
> My problems is that as far as I know I can't use arrays in sql server
> cause it doesn't support them. And this has come about because I don't
> know how many rows need to be inserted into the link table. But there
> will always be at least one.
>
> I know I need to do this in a loop, but how do I split up the the
> second argument so that I can?
>
> Thanks,
>
> Mark
>
Navigation:
[Reply to this message]
|