I am trying to achieve something along the lines of a for-each, where I would like to take the Ids of a returned select statement and use each of them.
DECLARE @i int
DECLARE @PractitionerId int
DECLARE @numrows int
DECLARE @Practitioner TABLE (
idx smallint Primary Key IDENTITY(1,1)
, PractitionerId int
)
INSERT @Practitioner
SELECT distinct PractitionerId FROM Practitioner
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM Practitioner)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM Practitioner))
BEGIN
SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i)
--Do something with Id here
PRINT @PractitionerId
SET @i = @i + 1
END
At the moment I have something that looks like the above, but am getting the error:
Invalid column name 'idx'.
idx
is in @Practitioner
not Practitioner
. There are most often superior set-based alternatives to a for-each approach, if you show what you do with the row value perhaps an alternative can be suggested.
--Do something with Id here
is, it is likely we can show you how to solve this problem without any loops or cursors. In most cases, you want to use a set-based solution, since that is how SQL Server is optimized to work. Looping and treating one row at a time certainly has its place, but I suspect this isn't it.
You seem to want to use a CURSOR
. Though most of the times it's best to use a set based solution, there are some times where a CURSOR
is the best solution. Without knowing more about your real problem, we can't help you more than that:
DECLARE @PractitionerId int
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT PractitionerId
FROM Practitioner
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
WHILE @@FETCH_STATUS = 0
BEGIN
--Do something with Id here
PRINT @PractitionerId
FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
Suppose that the column PractitionerId is a unique, then you can use the following loop
DECLARE @PractitionerId int = 0
WHILE(1 = 1)
BEGIN
SELECT @PractitionerId = MIN(PractitionerId)
FROM dbo.Practitioner WHERE PractitionerId > @PractitionerId
IF @PractitionerId IS NULL BREAK
SELECT @PractitionerId
END
@PractitionerId
in the WHERE clause to limit the input to the MIN function before setting the new value of @PractitionerId
. Effective, but not obvious to us non-guru SQL users.
This generally (almost always) performs better than a cursor and is simpler:
DECLARE @PractitionerList TABLE(PracticionerID INT)
DECLARE @PracticionerID INT
INSERT @PractitionerList(PracticionerID)
SELECT PracticionerID
FROM Practitioner
WHILE(1 = 1)
BEGIN
SET @PracticionerID = NULL
SELECT TOP(1) @PracticionerID = PracticionerID
FROM @PractitionerList
IF @PracticionerID IS NULL
BREAK
PRINT 'DO STUFF'
DELETE TOP(1) FROM @PractitionerList
END
Your select count and select max should be from your table variable instead of the actual table
DECLARE @i int
DECLARE @PractitionerId int
DECLARE @numrows int
DECLARE @Practitioner TABLE (
idx smallint Primary Key IDENTITY(1,1)
, PractitionerId int
)
INSERT @Practitioner
SELECT distinct PractitionerId FROM Practitioner
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @Practitioner)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
BEGIN
SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i)
--Do something with Id here
PRINT @PractitionerId
SET @i = @i + 1
END
I would say everything probably works except that the column idx
doesn't actually exist in the table you're selecting from. Maybe you meant to select from @Practitioner
:
WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
because that's defined in the code above like that:
DECLARE @Practitioner TABLE (
idx smallint Primary Key IDENTITY(1,1)
, PractitionerId int
)
The following line is wrong in your version:
WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
(Missing the @)
Might be an idea to change your naming convention so that the tables are more different.
Although cursors usually considered horrible evil I believe this is a case for FAST_FORWARD cursor - the closest thing you can get to FOREACH in TSQL.
Here is the one of the better solutions.
DECLARE @i int
DECLARE @curren_val int
DECLARE @numrows int
create table #Practitioner (idx int IDENTITY(1,1), PractitionerId int)
INSERT INTO #Practitioner (PractitionerId) values (10),(20),(30)
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM #Practitioner)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM #Practitioner))
BEGIN
SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i)
--Do something with Id here
PRINT @curren_val
SET @i = @i + 1
END
Here i've add some values in the table beacuse, initially it is empty.
We can access or we can do anything in the body of the loop and we can access the idx by defining it inside the table definition.
BEGIN
SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i)
--Do something with Id here
PRINT @curren_val
SET @i = @i + 1
END
I made a procedure that execute a FOREACH
with CURSOR
for any table.
Example of use:
CREATE TABLE #A (I INT, J INT)
INSERT INTO #A VALUES (1, 2), (2, 3)
EXEC PRC_FOREACH
#A --Table we want to do the FOREACH
, 'SELECT @I, @J' --The execute command, each column becomes a variable in the same type, so DON'T USE SPACES IN NAMES
--The third variable is the database, it's optional because a table in TEMPB or the DB of the proc will be discovered in code
The result is 2 selects for each row. The syntax of UPDATE
and break the FOREACH
are written in the hints.
This is the proc code:
CREATE PROC [dbo].[PRC_FOREACH] (@TBL VARCHAR(100) = NULL, @EXECUTE NVARCHAR(MAX)=NULL, @DB VARCHAR(100) = NULL) AS BEGIN
--LOOP BETWEEN EACH TABLE LINE
IF @TBL + @EXECUTE IS NULL BEGIN
PRINT '@TBL: A TABLE TO MAKE OUT EACH LINE'
PRINT '@EXECUTE: COMMAND TO BE PERFORMED ON EACH FOREACH TRANSACTION'
PRINT '@DB: BANK WHERE THIS TABLE IS (IF NOT INFORMED IT WILL BE DB_NAME () OR TEMPDB)' + CHAR(13)
PRINT 'ROW COLUMNS WILL VARIABLE WITH THE SAME NAME (COL_A = @COL_A)'
PRINT 'THEREFORE THE COLUMNS CANT CONTAIN SPACES!' + CHAR(13)
PRINT 'SYNTAX UPDATE:
UPDATE TABLE
SET COL = NEW_VALUE
WHERE CURRENT OF MY_CURSOR
CLOSE CURSOR (BEFORE ALL LINES):
IF 1 = 1 GOTO FIM_CURSOR'
RETURN
END
SET @DB = ISNULL(@DB, CASE WHEN LEFT(@TBL, 1) = '#' THEN 'TEMPDB' ELSE DB_NAME() END)
--Identifies the columns for the variables (DECLARE and INTO (Next cursor line))
DECLARE @Q NVARCHAR(MAX)
SET @Q = '
WITH X AS (
SELECT
A = '', @'' + NAME
, B = '' '' + type_name(system_type_id)
, C = CASE
WHEN type_name(system_type_id) IN (''VARCHAR'', ''CHAR'', ''NCHAR'', ''NVARCHAR'') THEN ''('' + REPLACE(CONVERT(VARCHAR(10), max_length), ''-1'', ''MAX'') + '')''
WHEN type_name(system_type_id) IN (''DECIMAL'', ''NUMERIC'') THEN ''('' + CONVERT(VARCHAR(10), precision) + '', '' + CONVERT(VARCHAR(10), scale) + '')''
ELSE ''''
END
FROM [' + @DB + '].SYS.COLUMNS C WITH(NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(''[' + @DB + '].DBO.[' + @TBL + ']'')
)
SELECT
@DECLARE = STUFF((SELECT A + B + C FROM X FOR XML PATH('''')), 1, 1, '''')
, @INTO = ''--Read the next line
FETCH NEXT FROM MY_CURSOR INTO '' + STUFF((SELECT A + '''' FROM X FOR XML PATH('''')), 1, 1, '''')'
DECLARE @DECLARE NVARCHAR(MAX), @INTO NVARCHAR(MAX)
EXEC SP_EXECUTESQL @Q, N'@DECLARE NVARCHAR(MAX) OUTPUT, @INTO NVARCHAR(MAX) OUTPUT', @DECLARE OUTPUT, @INTO OUTPUT
--PREPARE TO QUERY
SELECT
@Q = '
DECLARE ' + @DECLARE + '
-- Cursor to scroll through object names
DECLARE MY_CURSOR CURSOR FOR
SELECT *
FROM [' + @DB + '].DBO.[' + @TBL + ']
-- Opening Cursor for Reading
OPEN MY_CURSOR
' + @INTO + '
-- Traversing Cursor Lines (While There)
WHILE @@FETCH_STATUS = 0
BEGIN
' + @EXECUTE + '
-- Reading the next line
' + @INTO + '
END
FIM_CURSOR:
-- Closing Cursor for Reading
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR'
EXEC SP_EXECUTESQL @Q --MAGIA
END
I came up with a very effective and (I think) readable way to do this.
Create a temp table and put the records you want to iterate in there Use WHILE @@ROWCOUNT <> 0 to do the iterating To get one row at a time do, SELECT TOP 1
Here's the code. Sorry, its using my variable names instead of the ones in the question.
DECLARE @tempPFRunStops TABLE (
ProformaRunStopsID int,
ProformaRunMasterID int,
CompanyLocationID int,
StopSequence int
);
INSERT @tempPFRunStops (ProformaRunStopsID, ProformaRunMasterID, CompanyLocationID, StopSequence)
SELECT
ProformaRunStopsID,
ProformaRunMasterID,
CompanyLocationID,
StopSequence
FROM ProformaRunStops
WHERE ProformaRunMasterID IN (
SELECT ProformaRunMasterID
FROM ProformaRunMaster
WHERE ProformaId = 15 )
-- SELECT * FROM @tempPFRunStops
WHILE @@ROWCOUNT <> 0 -- << I dont know how this works
BEGIN
SELECT TOP 1 * FROM @tempPFRunStops
-- I could have put the unique ID into a variable here
SELECT 'Ha' -- Do Stuff
DELETE @tempPFRunStops
WHERE ProformaRunStopsID = (SELECT TOP 1 ProformaRunStopsID FROM @tempPFRunStops)
END
Success story sharing
set
logic). Likely people who come to SQL and try to do something RBAR they are coming to it as a programmer who needs it to do something useful like this.