Is there such a thing has arrays in tsql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Is there such a thing has arrays in tsql?

Author

Topic

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-10 : 20:25:09

I'm have a stored procedure that iterates through a list of numbers and adds an item for each number (user id) some of these ids are duplicates which is fine even necessary for the first part of my query but for the last I need to ensure that no duplicates id's are passed to the stored procedure, in this case called 'spInsertForBackupNote'. My thoughts here was to do something like this:

SET @Note_Buffer = @UserID -- @Note_Buffer being some kind of array?

IF @Note_Buffer = @UserID -- If its been added to the buffer we dont execute sp
BEGIN
Do Nothing here
END

ELSE

BEGIN
EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID
END

I know this would never work because it would always be false since I just added the same userid to the buffer that I want to add. But I think you see my problem. I know it should be an easy one but my TSQL is limited. I've posted the whole sp. Hope someone can help.

CREATE PROCEDURE spInsertAssignedNotesByList
@FK_UserIDList NVARCHAR(4000) = NULL,
@FK_NoteIDList NVARCHAR(4000) = NULL,
@By_Who INT,
@UserID INT

AS
SET NOCOUNT ON

DECLARE @Length INT
DECLARE @Note_Length INT
DECLARE @Note_Buffer INT

DECLARE @FirstUserIDWord NVARCHAR(4000)
DECLARE @FirstNoteIDWord NVARCHAR(4000)

DECLARE @FK_UserID INT
DECLARE @FK_NoteID INT

SELECT @Length = DATALENGTH(@FK_UserIDList )
SELECT @Note_Length = DATALENGTH(@FK_NoteIDList )

DECLARE @TempFK_NoteIDList NVARCHAR(4000) --= NULL
DECLARE @Temp_NoteLength INT

SET @TempFK_NoteIDList = @FK_NoteIDList
SET @Temp_NoteLength = DATALENGTH(@FK_NoteIDList )

-- IF @Length > @Note_Length -- If we have more users than notes

BEGIN

WHILE @Length > 0
BEGIN

IF @Length > 0

EXECUTE @Length = PopFirstWord @FK_UserIDList OUTPUT, @FirstUserIDWord OUTPUT
SELECT @FK_UserID = CONVERT(INT, @FirstUserIDWord)

IF @Length > 0
BEGIN

SET @FK_NoteIDList = @TempFK_NoteIDList
SET @Note_Length = @Temp_NoteLength

WHILE @Note_Length > 0
BEGIN
EXECUTE @Note_Length = PopFirstWord @FK_NoteIDList OUTPUT, @FirstNoteIDWord OUTPUT
SELECT @FK_NoteID = CONVERT(INT, @FirstNoteIDWord)

IF @Note_Length > 0
EXECUTE spInsertAssignedNoteDetail @FK_UserID, @FK_NoteID

SET @Note_Buffer = @UserID
EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID, @By_Who, @UserID -- NEW HERE
END
END

END
END

--------------------------------------------------
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-10 : 20:38:58

Take a look through our CSV articles:

http://www.sqlteam.com/searchresults.asp?SearchTerms=csv

There are a couple of methods you can use to parse a comma-separated list of values and will give you the same functionality of an array. You should also look at other uses for tally tables:

http://www.sqlteam.com/searchresults.asp?SearchTerms=tally

As they are very useful for replacing loops in code. In all likelihood you can use a tally table to generate all of the rows you need in one operation, no loops necessary.

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-10 : 20:51:34

Thanks for that, however its a lot bigger tahn I anticpated, Sure I can't do this with just a few extra lines?

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-10 : 21:01:46

Also I already have the value from comma separated list. I just need to check that I'm not about to pass the same one as before into my sp.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-10 : 21:04:32

Without knowing what PopFirstWord does, we can't provide much more advice. It would be best if you can post some sample data that shows what you're passing to the procedure and what kind of output or results you get (or want to get). You need to describe WHAT you want to accomplish, not HOW you want to accomplish it. There's more than one way to skin a cat.

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-10 : 21:37:41

This justs parse my string, it all works fine. This isn't relevant really to my problem because it does everything that I need it to do up until the execution of the last sp. Basically it parses the numbers from the string one at a time. It passes one number to both stored procedures at the end. The second to last procedure I need to pass all the number regardless of whether they are duplicates or not. But for the last procedure I simply need to make sure that we only execute it as long as that number has not been passed in before.

What I have done is create a tally table called Buffer_UserIDs. Now somehow I need to ask the db table Buffer_UserIDs 'is this number in there' if not add it. When its come to that same number again, the check will reveal that this number exists in the db table Buffer_UserIDs and will abandon the execution of that last sp. IE it will not execute for same id.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-10 : 21:53:06

Rob is trying to show a better approach using set-based processing. From the code you have posted it appears you are approaching this problem using iteratative methods and the links Rob provided were showing you set-based methods for these types of tasks. But as he mentioned, it is difficult to fully grasp what you are trying to accomplish without sample inputs and outputs.

Can you provide some sample data inputs/outputs of what you are trying to accomplish?

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-10 : 22:06:29

input : {1,3,4,6,6,8,9,3,5}

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-10 : 22:48:10

[code]/*
--Create a Tally table used for the function below

create table Numbers (n int primary key)
declare @n int; set @n = 0
while @n <=4000
begin
insert into Numbers
select @n
set @n = @n+1
end
*/

declare @FK_UserIDList NVARCHAR(4000),
@FK_NoteIDList NVARCHAR(4000),
@By_Who INT,
@UserID INT

--Sample Inputs
SET @FK_UserIDList = '1,3,4,6,6,8,9,3,5'
SET @FK_NoteIDList = '3,6,8,9,3,5,5,6,4'
SET @By_Who = 5
SET @UserID = 3

--Parse @FK_UserIDList , @FK_NoteIDList into Stack
SELECT IDENTITY(int,0,1) id,
CASE WHEN n <= LEN(@FK_UserIDList)+1 THEN NullIf(SubString(',' + @FK_UserIDList + ',' , n , CharIndex(',' , ',' + @FK_UserIDList + ',' , n) - n) , '') END AS FK_UserID ,
CASE WHEN n <= LEN(@FK_NoteIDList)+1 THEN NullIf(SubString(',' + @FK_NoteIDList + ',' , n , CharIndex(',' , ',' + @FK_NoteIDList + ',' , n) - n) , '') END AS FK_NoteID
INTO #Stack
FROM Numbers
WHERE n <= Len(',' + @FK_UserIDList + ',') AND SubString(',' + @FK_UserIDList + ',' , n - 1, 1) = ','

--Our Stack
select * from #stack

/*

--EXECUTE spInsertAssignedNoteDetail @FK_UserID, @FK_NoteID
--Can now be just an insert

INSERT INTO <Table>
SELECT FK_UserID,FK_NoteID
FROM #Stack

--EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID, @By_Who, @UserID
--Can now also just be an insert

INSERT INTO <Table>
SELECT FK_UserID,FK_NoteID,@By_Who,@UserID
FROM #Stack

*/

DROP TABLE #stack[/code]Now you said:

quote:
but for the last I need to ensure that no duplicates id's are passed to the stored procedure, in this case called 'spInsertForBackupNote'. My thoughts here was to do something like this:

What duplicate id's are you referring to?

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-10 : 23:01:45

the duplicate id's that sometimes occur in my input string, by the way thanks for that, it was exactly what I was after.

Rauken
Posting Yak Master

108 Posts

Posted - 2004-02-24 : 11:47:57

Check out www.sommarskog.se, you will find an excellent article how to do "arrays in t-sql".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources