Syntax of For-Loop in SQL Server

Syntax of for-loop in SQL Server

T-SQL doesn't have a FOR loop, it has a WHILE loop

WHILE (Transact-SQL)

WHILE Boolean_expression
BEGIN

END

SQL Server FOR EACH Loop

SQL is primarily a set-orientated language - it's generally a bad idea to use a loop in it.

In this case, a similar result could be achieved using a recursive CTE:

with cte as
(select 1 i union all
 select i+1 i from cte where i < 5)
select dateadd(d, i-1, '2010-01-01') from cte

How do I get my for loop to replace these words in a T-SQL query in python?

In Python, you cannot interpolate strings in the manner you attempted, specifically embedding a value directly in another string body which may be possible in other langauges (PHP, Perl, etc.).

Usually in Python, placeholders and binded values are used. While there are multiple ways to interpolate strings in Python (>=2.6), the preferred universal way is with str.format. Since you need to format the same value, place all with first ordinal position, {0}:

for i in assets: 
    q1 = """SELECT 'UnC' AS [{0}], SUM(CASE WHEN [{0}] IS NULL THEN 1 ELSE 0 END) AS [{0} Count]
            FROM SM.dbo.vSC SCL
            JOIN IP.dbo.vSLUN(3) S
                ON SCL.iSID = S.isID
            JOIN SMa.dbo.S_C SC(noLock) ON S.isID = SC.iSID
            WHERE SC.bIsInA IS NULL AND [{0}] IS NULL
         """

    q1 = q1.format(i)

    df1 = pd.read_sql(q1, conn)

how to use for loop in stored procedure sql server 2005

Creating a manual loop in a SQL Server procedure is always a bad idea - SQL Server operates in sets of data - and your statement should also be set-oriented.

In your case, what I would do is this:

• shred the XML into a temporary table (as you already do)
• then update the existing values based on a join condition
• remove those rows updated from the temporary table
• the remaining rows need to be inserted

So your code would be something like:

CREATE PROCEDURE InsertIntoMyTable @mytable xml 
AS BEGIN
   SELECT 
      colx.value('(PointsliceId)[1]', 'INT') AS PointSliceId,
      colx.value('(Pt_timestamp)[1]', 'DATETIME') AS Point_timestamp
      colx.value('(FloatValue)[1]', 'FLAOT') AS Float_Value
   INTO #TMP 
   FROM @mytable.nodes('DocumentElement/mytable') AS Tabx(Colx)

-- udpate the existing rows
UPDATE dbo.PointValue_Float
SET FloatValue = t.FloatValue 
FROM #TMP t  
WHERE t.PointSliceId = PointValue_Float.PointSliceId 
  AND t.Pt_timeStamp = PointValue_Float.Pt_timeStamp 

-- remove those from the #TMP table
DELETE FROM #TMP
WHERE EXISTS
  (SELECT * FROM dbo.PointValue_Float 
   WHERE PointSliceId = #TMP.PointSliceId AND Pt_timeStamp = #TMP.Pt_timeStamp)

-- INSERT the remaining rows    
INSERT INTO 
    dbo.PointValue_Float(PointSliceId, Pt_timeStamp, FloatValue) 
  SELECT 
     PointSliceId, Pt_timeStamp, FloatValue 
  FROM #TMP
END

Run a while loop for a fixed amount of time in SQL

You mean like this?

DECLARE INT @maxtime= 72, DATETIME @endtime;

SET @endtime = DATEADD(HOUR, 72, CURRENT_TIMESTAMP);

WHILE @endtime >= CURRENT_TIMESTAMP
BEGIN
   -- DoStuff()
END;

FYI: current_timestamp is the ANSI equivalent of getdate().

How can INSERT INTO a table 300 times within a loop in SQL?

You may try it like this:

DECLARE @i int = 0
WHILE @i < 300 
BEGIN
    SET @i = @i + 1
    /* your code*/
END

How to use for loop scenario in sql server

I'm inferring from your question that this is the complete solution you're actually looking for:

    SELECT MSchoolDetails.* 
      FROM MLTalukDetails
INNER JOIN MSchoolDetails ON MTalukDetails.l_t_id = MSchoolDetails.L_T_Id
     WHERE MLTalukDetails.L_D_ID = 4 AND MSchoolDetails.Y_Id=@yearid

Normally one has a join condition which involves both tables.

I'd suggest reading up on the various joins.

---

Related Topics