Nested inserts with TSQL

Hi I have to strings that can both each contain an indeterminable length. These strings are

UserID
NoteID

and will contain something like

UserID = '1, 2, 3, 4'
NoteID = '4, 9, 18, 21, 23, 27'

However its not known the length of each and so we could have the reverse of the above.

I am to insert x amount of notes to one userId and vice versa, but I'm trying to figure out how to do both so the insert would resemble the following.

UserID NoteID

1 4
1 9
1 18
1 21
1 23
1 27
2 4
2 9
etc, etc

This is sp that does it and it works fine for just one or the other, I just don't have much experience in this kind of thing. The spInsertAssignedNoteDetail at the end simply makes the insert when I have both numbers.

Heres my attempt, but i'm just stuck as to where to go from here

CREATE PROCEDURE spInsertAssignedNotesByList
@.FK_UserIDList NVARCHAR(4000) = NULL,
@.FK_NoteIDList NVARCHAR(4000) = NULL

AS
SET NOCOUNT ON

DECLARE @.Length INT
DECLARE @.Note_Length INT

DECLARE @.FirstUserIDWord NVARCHAR(4000)
DECLARE @.FK_UserID INT
DECLARE @.FK_NoteID INT

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

WHILE @.Length > 0 or @.Note_Length > 0
BEGIN
EXECUTE @.Length = PopFirstWord @.FK_UserIDList OUTPUT, @.FirstUserIDWord OUTPUT

IF @.Length > 0
BEGIN
SELECT @.FK_UserID = CONVERT(INT, @.FirstUserIDWord)

EXECUTE spInsertAssignedNoteDetail @.FK_UserID, @.FK_NoteID
END
END
----------------
GOOk I got this far, which does what I want with the first loop but when it comes to do the second outer do while for some reason the counter is zero instead of going backing to what it was when it started. Anyone know why it does this. Thanks

CREATE PROCEDURE spInsertAssignedNotesByList
@.FK_UserIDList NVARCHAR(4000) = NULL,
@.FK_NoteIDList NVARCHAR(4000) = NULL

AS
SET NOCOUNT ON

DECLARE @.Length INT
DECLARE @.Note_Length 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 )

WHILE @.Length > 0
BEGIN

IF @.Length > 0
EXECUTE @.Length = PopFirstWord @.FK_UserIDList OUTPUT, @.FirstUserIDWord OUTPUT
SELECT @.FK_UserID = CONVERT(INT, @.FirstUserIDWord)

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
END
END
----------------
GO

Nested case?

IS it possible to use nested case statement in TSQL,if yes what is the syntax of it

I m using SQL 2005

thanx

its possible...here is an example/syntax...

declare @.var1 int

declare @.var2 int

set @.var1 =1

set @.var2 =1

select

CASE @.var1

WHEN 1

THEN(

CASE @.var2

WHEN 1 THEN(

100)

ELSE 99

END)

ELSE 98

END

|||thanx very much.... this is the thing i asked for

Need TSQL HELP! long running TSQL task in SSIS.....

I was referred to this forum as this SSIS task is TSQL in nature

We have an SSIS package that seems to run long and then short then long and is random in nature in these times....8 hours is the average but it has been known to run for 4 hours and it currently running 10+ hours...

the data size hasnt changed much other than a few 1000 records more than the previous iteration.

here is what we are running right now as a step in the package and it has been running for over 10 hours so far.

I had to change some of the names of Tables etc... but here is the rought outline of what the SQL task does:

truncate table [Reporting].[dbo].[ReportTable]
INSERT INTO [Reporting].[dbo].[ReportTable] (pid)
SELECT DISTINCT [MainAPPDB].[dbo].[ReportTableHistory].[Client_ID]
FROM [MainAPPDB].[dbo].[ReportTableHistory]
LEFT OUTER JOIN [MainAPPDB].[dbo].[ReportTableStatus] ON [MainAPPDB].[dbo].[ReportTableStatus].[ReportTableStatusID] = [MainAPPDB].[dbo].[ReportTableHistory].[ReportTableStatusID]
LEFT OUTER JOIN [SecondAPPdb].[dbo].[tblBrokerContact] ON [SecondAPPdb].[dbo].[tblBrokerContact].[BrokerContact_ID] = [MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedBy]

UPDATE [Reporting].[dbo].[ReportTable] SET rdata = X.rAsXML
FROM
(
SELECT
C.pid as 'primid',
(SELECT [MainAPPDB].[dbo].[ReportTableStatus].[Description] --varchar(25)
,CONVERT(CHAR(30),[MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedOn],100) AS [LastUpdatedOn] --varchar(30)
,[SecondAPPdb].[dbo].[tblContact].[Contact_FName] + ' ' + [SecondAPPdb].[dbo].[tblContact].[Contact_LName] AS [LastUpdatedBy] --varchar(50)
,[MainAPPDB].[dbo].[ReportTableHistory].[Reason] --varchar(300)
FROM [MainAPPDB].[dbo].[ReportTableHistory]
LEFT OUTER JOIN [MainAPPDB].[dbo].[ReportTableStatus] ON [MainAPPDB].[dbo].[ReportTableStatus].[ReportTableStatusID] = [MainAPPDB].[dbo].[ReportTableHistory].[ReportTableStatusID]
LEFT OUTER JOIN [SecondAPPdb].[dbo].[tblContact] ON [SecondAPPdb].[dbo].[tblContact].[Contact_ID] = [MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedBy]
WHERE [C_ID] = C.pid FOR XML RAW ('ReportTableHistory'), ROOT('ReportTableStatusHistories'), ELEMENTS XSINIL) as rAsXML
FROM [Reporting].[dbo].[ReportTable] C) X
WHERE X.primid = pid

DECLARE @.inxml XML
DECLARE @.res XML
DECLARE @.pid INT

DECLARE cur CURSOR fast_forward FOR
SELECT pid,rdata FROM [Reporting].[dbo].[ReportTable]
OPEN cur
FETCH next FROM cur INTO @.pid, @.inxml
WHILE @.@.fetch_status = 0
BEGIN
EXEC ExternFunctions_FormatReportTableHistory @.inxml, @.res OUT
UPDATE [Reporting].[dbo].[Client] SET ReportTableHistory = @.res
WHERE [Reporting].[dbo].[Client].[Client_ID] = @.pid
FETCH next FROM cur INTO @.pid,@.inxml
END
CLOSE cur
DEALLOCATE cur

It may help to put in some timestamp statements that you can study by letting the SQL output to "Results to Text."

declare @.MyMin varchar(3)

declare @.MySec varchar(3)

declare @.MyMS varchar(4)

set @.MyMin = right(('00' + cast(datepart(mi, getdate()) as varchar(3))), 2)

set @.MySec = right(('00' + cast(datepart(ss, getdate()) as varchar(3))), 2)

set @.MyMS = right(('000' + cast(datepart(ms, getdate()) as varchar(4))), 3)

print 'Beginning step xxxx' + ' ' + @.MyMin + ':' + @.MySec + '.' + @.MyMS

How many rows are in [Reporting].[dbo].[ReportTable]?

How time-consuming is the EXEC inside your CURSOR loop:

EXEC ExternFunctions_FormatReportTableHistory @.inxml, @.res OUT

?

(Maybe you want some timing statements within that loop.)