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
----------------
GOThere are not arrays in TSQL (at least the current version).
You can simulate it by placing the values into a temp table (perhaps by passing in a delimited string, and then using a user defined function that returns a table variable). Then, once you have the table, you can do what TSQL is best at, Set operations.|||can you show an example of how to display the contents of a table variable?
Thanks|||What do you mean by "display"? SQL Server runs on the server, and as such really does not expose a user interface.|||the UDF returns a table variable and I need to bind to it and display the contents on a .net web page.|||At the end of the SP,
SELECT * FROM TableVariable
Do an ExecuteReader or similar and bind the datareader to the grid, whatever.|||I am using a strongly typed dataset and am using com.executenonquery to bind to a datagrid and the results are:
I get back the columns names from the function or sp (they both do the same) but the columns are empty.
