How do you access the value within a list in T-SQL?
I have a SQL statement that loops through and counts how many times a value appears in a specific column in
Table_1
Table_2
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
DECLARE @cnt INT = 1;
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
WHILE @cnt < 9
BEGIN
IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt)
BEGIN
UPDATE Staff_Manager.dbo.Staff_Count_TBL
SET Column_Value = (
SELECT COUNT(*)
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1'
GROUP BY Staff_No, Info_Data),
Column_Value2 = 'Data1'
WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt
END
ELSE
BEGIN
INSERT INTO Staff_Manager.dbo.Staff_Count_TBL
(Staff_No, Year_D, Month_D, Column_Index, Column_Value, Column_Value2)
SELECT 3201, 2016, 6, @cnt, COUNT(*), 'Data1'
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1'
GROUP BY Staff_No, Info_Data
END
SET @cnt = @cnt + 1
END
Data1
WHERE Staff_No = 3201 AND Date_Data
BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1'
WHERE Staff_No = 3201 AND Date_Data
BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]
SELECT 3201, 2016, 6, @cnt, COUNT(*), @MyList[@cnt]
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data
BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]
UPDATE
INSERT
What you created is not a list but a table variable. So how to Iterate over a table. Below is a simple example and I think you can proceed after if you understand it:
(Note: Cursors are not efficient when it comes to performance and large tables)
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
DECLARE @value VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT Value FROM @MyList
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @value
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @value
-- PUT YOUR LOGIC HERE
-- MAKE USE OR VARIABLE @value wich is Data1, Data2, etc...
FETCH NEXT FROM db_cursor INTO @value
END
CLOSE db_cursor
DEALLOCATE db_cursor
Prints:
Data1
Data2
Data3
Data4
Data5
Data6
Data7
Data8
So you have inside @value variable Data
, Data2
.. etc . I think this solves your problem.
WHILE
loop + OFFSET
+ FETCH NEXT
:DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);
WHILE @COUNTER < @MAX
BEGIN
SET @VALUE = (SELECT Value FROM @MyList
ORDER BY 1 OFFSET @COUNTER
ROWS FETCH NEXT 1 ROWS ONLY);
PRINT @VALUE
SET @COUNTER = @COUNTER + 1
END
You get the same result