SQL SERVER – Simple Example of WHILE Loop With CONTINUE and BREAK Keywords

This is question is one of those question which is very simple and most of the users get it correct, however few users find it confusing for first time. I have tried to explain the usage of simple WHILE loop in first example. BREAK keyword will exit the stop the while loop and control is moved to next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example.

1) Example of WHILE Loop
DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
END
GO

ResultSet:
1
2
3
4
5

2) Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

ResultSet:
1
2
3

3) Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

ResultSet:
1
2
3
4
5

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

154 thoughts on “SQL SERVER – Simple Example of WHILE Loop With CONTINUE and BREAK Keywords

  1. Hello there,

    I wanted to thank you for all the help you provide in here.
    In regards to this while loop with ‘continue’ and ‘break’, if you are trying to avoid printing the ’4′, wouldn’t it be more accurate to code it like this?

    DECLARE @intFlag INT
    SET @intFlag = 1

    PRINT @intFlag
    WHILE (@intFlag <=5) BEGIN
    SET @intFlag = @intFlag + 1
    IF @intFlag = 4 — This will never executed
    CONTINUE;
    ELSE
    PRINT @intFlag
    END
    GO

  2. Hi,
    if i want user enter the number and then while loop run for that many times like i wrote small program for the table

    declare @i int
    declare @j int
    print ‘enter number’ @j
    set @i=1
    while (@i<=5)
    begin
    print @i
    set @j=@i*@j
    print @j
    set @i=@i+1
    end
    go

    it shows incorrect syntax error near ‘@j’
    Would you please tell me the correct way of entering number(@j) from the user.

    • in third line there is a error i think because u need to u concatination symbol ie + u have use then it will work fine

      • U can follow below the code, u did not Get any Error.

        Please Let me Know, If u need any further information in this regard.

        declare @i int
        declare @j int
        print ‘enter number’+ Cast(@j as varchar)
        set @i=1
        while (@i<=5)
        begin
        print @i
        set @j=@i*@j
        print @j
        set @i=@i+1
        end
        go

  3. HI

    Could you please explain the cursor vs while loop ..
    Is both are performance killer … how the while differ from cursor

  4. sir,

    i want function in that function i have to pass table name, column name and its data type. and when i execute that function at that time column should add in table. table name and column name will be same as parameter of function.

    • declare @n int, @factorial int
      select @n=5, @factorial=1

      select @factorial=@factorial*number from master..spt_values
      where type=’p’ and number between 1 and @n

      select @factorial as factorial

  5. nice article.
    but how can we iterate through each record obtained from the select statement in the stored procedure?
    thanks in advance

    • You can do it in many methods

      1 If you use front end application to show data, prefix it when displaying

      2 SELECT ‘prefix_’+cast(col as varchar(10)) from your_table

  6. sir,

    i had a doubt that is can we use while loop with select satement ? example i want to extract name one by one by using while loop in sql is it possible?

  7. please reply me …
    my question is….

    if i have a table with a field date_time..
    and i want to run a query when i found date_time matching with GETDATE() and continuously checking the date_time field.

    so this is how i have to write a loop or what ever you suggest …. to continuously running a loop ( endless loop)

  8. alter PROCEDURE [dbo].[usp_AddUpdatePatientRegistration]
    (
    @i int=0,
    @str1 nvarchar(50),
    @str nvarchar(50) output
    )

    AS
    BEGIN

    set @str1=(select tree_name from user_tree where app_no=’090001′)
    while(@i < @str1)
    begin
    set @str=@str +”+@str1
    end
    END

    This is my produture not run
    i want to @str1 select lotsof rows that rows are concanect one string in how
    and return that value

  9. @Shafique,

    Declare @Var int
    Set @Var = 1

    While @Var < = ( Select Count(*) from Table_Name)
    Begin
    perform action by executing scripts
    Set @Var = @Var+1
    End

    Should work, if you have questions, please let us know.
    ~ IM.

  10. hello sir,

    In sqlserver 2005 i had a table containing 3 lakh rows …i need to display 50000 records each time using loop.can anybody help me….

    thnks in advance.

  11. Hi Teja,

    Why you need to loop thru it and display 50000 rows?

    Its better to write query such a way, so it gives result of 50000 rows that you want to display.

    LOOP will decrease the performance.

    Thanks,
    Tejas
    SQLYoga.com

  12. @Anjaneyulu

    create proc USP_Example_Sp
    AS
    SET NOCOUNT ON
    Declare @count int
    Set @count = 1
    Declare @table table ( id int Identity , Ename Varchar (40))

    While @Count < = 10
    Begin
    Insert into @table values ('Imran')
    Set @Count = @Count + 1
    End
    Select * from @table
    SET NOCOUNT OFF

    go
    Exec USP_Example_Sp
    go
    Drop proc USP_Example_Sp

    ~ IM

  13. Thank you. Very helpful article indeed.

    Can anyone give me some inputs on this one.

    Is there a way to implement a loop similar to a ‘do-while’ so that the block is executed atleast once?

    Thanks in advance.

  14. Hello Pritam,

    Use a variable in while loop as below:

    @declare @val bit
    set @val = 1
    while @val
    begin

    your code
    set @val= some condition to run while loop
    end

    Regards,
    Pinal Dave

  15. Hi Pinal Dave,

    I am doing as an asp.net developer where i have to do back end coding too. Really you are doing good . we have to be great ful for ever to you. moreover i need a help about how sql server excuting each query?.i means that just wanna know about internal process of SQL server. it would be great if could help me out.

  16. Hi sr, i am doing a store procedure but dont work, i need to update a inventory from a invoice when this is canceled,

    table products
    prod_id
    prod_desc
    prod_invent

    table invoice
    invoice_id
    invoice_date

    table invoicedet
    invoicedet_id
    invoice_id
    prod_id
    invoice_cant

  17. Pinal-

    I am not sure if this is related to this post or not but it appears you may be able to help me…

    I have a table that looks like this:

    COL1 COL2
    11112 1
    22221 1
    33331 2
    12312 3
    82828 3
    76767 3

    and so on.

    As you can see, I have a unique value in COL1 but the value in COL2 has duplicates within the table. I only need to keep 1 row of each distinct COL2 value and no longer care about the remaining rows. I dont even care which one I keep, but I have to keep just 1 of each.

    Can you help me write a delete statement that will delete all of the surplus rows without deleting the last one?

    I have run a statement to identify the rows where the condition occurs, and have done so successfully. Out of 4901 rows the condition occurs on 200 distinct COL2 values a total of 1109 times. I need to get rid of the 909 surplus rows.

    Thank you for your time.

  18. Hi Bradon,

    If you are using SQL SERVER 2005 and above, you can use this query to identify Duplicate records and can delete it.

    What I understand is: You need to have distinct value for Colmun2, don’t care about value in Column1.

    Solution:

    with cte as(
    select ROW_NUMBER() OVER(PARTITION BY Col2 ORDER BY Col1) AS RowID,
    FROM Table
    )
    SELECT *
    from cte
    Where RowID>1

    This will list out rows that you want to delete. Please check result once to make sure. If it is fine, then you can change “select *” to ” Delete”.

    Please check result first and take backup of that table first, if you have any doubt.

    For reference to this query: http://www.sqlyoga.com/2009/03/sql-server-find-duplicate-rows-with.html

    Thanks,

    Tejas

      • u can follow below of the Syntax, U did not get duplicate Records. If you have any probleam Please call this Mobile Number 9866179952
        with cte as(SELECT a.* from (
        select ROW_NUMBER() OVER(PARTITION BY Col2 ORDER BY Col1) AS RowID,
        FROM Table) as a
        )

  19. The Stored Procedure has a date parameter.

    exec myProc ’2010-01-05′

    How can I run myProc in a Loop specifying the date parameter. myProc should run in a loop while date
    between ’2010-01-05′ and ’2010-01-25′.

    • What are you doing inside a procedure? Instead of calling the procedure 21 times in your case, you can have another parameter in the procedure which accepts to_date and execute the code inside a procedure as many times

  20. Hi, I need to loop through a recordset that has multiple records that have the same data in the field:

    demo table:

    name grade
    ——- ——–
    jim 90
    jim 80
    jim 70
    jim 60
    tom 30
    tom 80
    etc…

    What I need to do is loop through this data and where the name is the same as the previous name, add the data of grade together. This is just a quick sample table show what the data could look like. I actually need to concatenate a ntext field but figure for simplicity to show example with an integer to add.

  21. Thank you Brian, that works, the only issue now is that for some reason, the text “ ” is showing where carriage breaks are in the data. See the example data below. I changed the text field of the table to varchar(max) but I don’t think that is the issue.

    Example text:

    STEP #1:
    this is a test

    STEP #2:
    this is step 2

    STEP #3:
    this is step 3

  22. Looks like the text I was trying to paste didn’t come through or was converted.

    Let me try adding some quotes around the text. ”’ ”’

    STEP #1:”’ ”’
    this is a test”’ ”’
    ”’ ”’
    STEP #2:”’ ”’
    this is step 2”’ ”’
    ”’ ”’
    STEP #3:”’ ”’
    this is step 3”’ ”’
    ”’ ”’

  23. Brian, I was able to use a replace function to remove the text but I am not a sql / xml expert but guess that take is XML version of carriage return.

  24. Hello sir,

    I have one question.

    Is it possible to update the all columns of view which has been created with multiple tables.

    Suppose I have 2 tables A & B

    Columns in A are:

    Name
    Desig
    StateID

    Columns in B are:

    StateID
    StateName

    I have created view MyView with these tables:

    Columns in MyView are:

    Name
    Desig
    StateName

    So is this possible to update the values of all columns in MyView.

    Thanks

  25. select ROW_NUMBER() OVER (ORDER BY DetailID ASC) AS ROWID,* from trnInvoiceTicket

    What is the equivalent of the ROW_NUMBER() here in WHILE Loop?

  26. hi mate, I have created this procedure but it wont give me require result. Can you please tell me what is wrong in this..
    ALTER PROCEDURE dbo.GetBalance
    (
    @Sdate as DATETIME ,
    @Edate as DATETIME ,
    @OBNO as INT
    )
    AS
    WHILE @EDATE < = '01/01/2009'
    BEGIN
    SELECT
    SUM(GSXAM) AS EXPENCE,
    SUM(CPAY) AS PAYMENT,
    SUM(CPAY – GSXAM) AS BALANCE FROM ACCOUNTS WHERE OBNO = @OBNO AND (EXPDT BETWEEN @SDATE AND @EDATE);
    Set @Edate = @Edate – 30
    END

  27. Thank You.
    First time in three years I needed to do a loop within my stored procedure and you example worked well for my needs.

  28. Sir,can u help me that my query is that,i have more colums that is PCS201,PCS202,uptp212 & more values stored in this columns condition is that All the column values checked if their value <=10 if 10 define another field & also count how many greater then 10 or less then 10. plz help me.

  29. |ATA |SUBATA
    |Chapter |Chapter description
    | |SUBATA |SUBATA DESC
    | | SUBATA |Subata description details

    assuming the *|* is table.
    how to design a query like this?
    anyone have idea?

    still new in this. :)

  30. I am not sure if I am going about this the right way, but I would like to take this query I created and output it as one big table opposed to multiple tables. I see why its creating multiple tables because the select is in the loop.. however, I am not sure how else to do it.

    declare @YEAR int
    declare @LASTYEAR int
    declare @TOTAL int
    declare @PUSHED int
    declare @REMAINING int

    SET @YEAR = (select min(distinct year(exam_scheddate)) from T_STUDY)
    SET @LASTYEAR = (select max(distinct year(exam_scheddate)) from T_STUDY)

    While @YEAR <= @LASTYEAR
    BEGIN

    SET @TOTAL = (select count(*) from T_STUDY where year(exam_scheddate) = @YEAR)
    SET @PUSHED = (select count(*) from T_STUDY where year(exam_scheddate) = @YEAR and is_exported = 0)
    SET @REMAINING = (select count(*) from T_STUDY where year(exam_scheddate) = @YEAR and is_exported = 1)

    select @YEAR, @TOTAL, @PUSHED, @REMAINING

    set @YEAR = @YEAR + 1

    end

  31. id mbrshp_id org strt_dt OPEN_acct LOM RFND
    246812 580993 01103 1973-10-01 NULL NULL Yes
    246812 580993 01064 1974-11-18 NULL NULL Yes
    246812 580993 01025 1975-08-25 NULL NULL Yes
    246812 581554 01025 1979-09-25 NULL NULL Yes
    246812 581553 01104 1983-06-01 NULL NULL Yes
    246812 581553 01051 1984-11-19 NULL NULL Yes
    246812 581553 01104 1985-04-01 NULL NULL Yes
    246812 72007 01025 1987-05-08 NULL NULL NULL
    246812 72007 01038 1997-12-01 NULL NULL NULL
    246812 72007 01246 2003-07-01 Yes NULL NULL
    500128 126830 03453 1989-09-05 NULL NULL NULL
    500128 126830 4338 1994-07-01 Yes NULL NULL
    652678 140780 01061 2000-04-04 Yes NULL NULL

    I opologize for not able to remove text wrap in the above data.

    For each id, I need to pull the orgs grouped by mbrsp_id, open_acct, LOM and RFND. From the above data I need three different groups for each id as follows.

    The open_acct block for id 246812 will contain the following three records:
    id mbrshp_id org strt_dt
    246812 72007 01025 1987-05-08
    246812 72007 01038 1997-12-01
    246812 72007 01246 2003-07-01

    The LOM block for id 246812 will not contain any records as there are no records with a yes in LOM column for that id.

    The RFND block for id 246812 will contain the following seven records:
    id mbrshp_id org strt_dt
    246812 580993 01103 1973-10-01
    246812 580993 01064 1974-11-18
    246812 580993 01025 1975-08-25
    246812 581554 01025 1979-09-25
    246812 581553 01104 1983-06-01
    246812 581553 01051 1984-11-19
    246812 581553 01104 1985-04-01

    The open_acct block for id 500128 will contain the following two records:
    id mbrshp_id org strt_dt
    500128 126830 03453 1989-09-05
    500128 126830 4338 1994-07-01

    The LOM block for id 500128 will not contain any records as there are no records with a yes in LOM column for that id.

    The RFND block for id 500128 will not contain any records as there are no records with a yes in RFNDcolumn for that id.

    The open_acct block for id 652678 will contain the following two records:
    id mbrshp_id org strt_dt
    652678 140780 01061 2000-04-04

    The LOM block for id 652678 will not contain any records as there are no records with a yes in LOM column for that id.

    The RFND block for id 652678 will not contain any records as there are no records with a yes in RFND column for that id.

    Thanks for your help

  32. I want to do bulk insert based upon one condition. How can I do with SQL Server?

    Here is my query
    —————————-
    SELECT * FROM Firm f
    WHERE not EXISTS
    (SELECT * FROM SBEFirms s WHERE f.id = s.id )
    ————————–

    So I want to insert f.id to other table called temp in above loop itself.
    How can I do that?

    Thank you

  33. sir, i want to update a table1 using another table2 in a selected fields only if the data is existing otherwise if the data is not existing needs to create a data on that table1..can you give me some sample on that.

    Thanks in advance

    • This is the general logic

      update t1
      set col1=t2.col1, col2=t2.col2…
      from table1 as t1 inner join table2 as t2
      on t1.keycol=t2.keycol

      insert into table1(column_list)
      select column_list from table2 as t2
      where not exists(select * from table1 where keycol=t2.keycol)

  34. Hi Sir ,

    Your posts helped me not once but Several times, Keep up the good work which helps beginners Like me to grasp concepts with in no time !!

    Cheers
    Naveen Kasa

  35. Help !!

    I wanted to take current Year in a temp Variable and need +10 and -10 values

    Example :

    2001
    2002
    2003
    2004
    .
    .
    2011
    2012
    2013
    .
    .
    2020

    how to get this result ??

    Please Help me

    • select year(getdate())-number as year from master..spt_values
      where type=’p’ and number between 1 and 10
      union
      select year(getdate())+number from master..spt_values
      where type=’p’ and number between 1 and 10
      order by year

  36. i have 2 tables t1 and t2
    in first and second table i have data like this
    create table temp.t1 (id int,Name varchar(200),Normalized varchar(200))

    create table temp.t2 (OrgName varchar(20),ModifiedName varchar(20))

    insert into temp.t1 values(111,’Tata Motors co. limited’,”)
    insert into temp.t1 values(112,’Tata Motors co.’,”)
    insert into temp.t1 values(112,’Tata Motors c.o.’,”)
    insert into temp.t1 values(113,’Tata Motors corp.’,”)
    insert into temp.t1 values(114,’Tata Motors ltd.’,”)
    insert into temp.t1 values(114,’Tata Motors’,”)

    insert into temp.t2 values(‘limited’,’Ltd’)
    insert into temp.t2 values(‘co.’,’Co’)
    insert into temp.t2 values(‘c.o.’,’Co’)
    insert into temp.t2 values(‘corp.’,’Corp’)
    insert into temp.t2 values(‘ltd.’,’Ltd’)

    select * from temp.t1
    select * from temp.t2

    and result should be The results look like this: from table1
    update the normalized column based on table2

    111 Tata Motors co. limited Tata Motors co Ltd
    112 Tata Motors co. Tata Motors Co
    112 Tata Motors c.o. Tata Motors Co
    113 Tata Motors corp. Tata Motors Corp
    114 Tata Motors ltd. Tata Motors Ltd
    114 Tata Motors Tata Motors

    Notice that the first record replaced ‘limited’ and ‘co.’

  37. BookISBN BookTitle SubjectName AuthorName PublisherName
    1 java hassan abod ali
    1 java hassan ahmed ali
    1 java program abod ali
    1 java program ahmed ali
    2 C++ program laith abod
    3 C## hiiii laith abod

    how to show in data grid view ??
    BookISBN BookTitle SubjectName AuthorName PublisherName
    1 java hassan,program abod,ahmad ali

  38. BookISBN BookTitle SubjectName AuthorName PublisherName
    1———– java—— hassan——- abod———–ali
    1———– java—— hassan——–ahmed——–ali
    1———– java——-program——-abod———-ali
    1———- java——–program——-ahmed——- ali
    2———- C++——- program——-laith——— abod
    3———- C##——- hiiii————- laith——— abod

    how to show in data grid view ??
    BookISBN BookTitle SubjectName AuthorName PublisherName
    1———– java——-hassan,program–abod,ahmad–ali

    ??????

  39. Hey,

    I was wondering if you had a bulk insert todo could you use a while loop if the data was all ints and had a pattern.

    c1 c2 c3

    1—1—x
    2—1—x
    3—1—x
    4—1—x
    5—1—x
    6—1—x
    1—2—x
    2—2—x
    3—2—x

    ect…

    every 6 rows. Also x is a boolean where if c2 >= c1, x = true.

    So can i nest an if(for x) in a while in a while?

    Usually i would use a for loop.

    ps. this is to populate a new empty tabel.

  40. Hello Sir,
    whenever i am stuck in sql problems i always refer your articles, it has always helped me.

    thanks for sharing ur knowledge with everyone.
    Great admirer,
    Priya

  41. I receive a comma delimited text file from a trade partner that has three fields. The first contains a unique record ID, the second contains a series of one or more values called keycodes that are pipe delimited internally between each value within the field. Sometimes there is only one keycode value in the second field, and sometimes there are as many as a hundred. Additionally, the width of each value in the keycde field is inconsistent, ranging from 5 to 7 characters. The third field contains a number indicating how many values are in the internally pipe delimited keycode field. I need to import the data into a SQL Server table and flip the data so that I get one record for each combination of record ID and value in the second field. I’m using an ODBC Text File driver to read the data.

    For example, I want the imported table that looks like this:

    recordid, keycode, multiples
    100001, AAA100|AAA101|AAA102, 3
    100002, AAB100|AAAB101, 2
    100003, AAAA100|AAB102|AAAC100|AAD100, 4

    To look like:

    recordid keycode
    100001 AAA100
    100001 AAA101
    100001 AAA102
    100002 AAB100
    100002 AAAB101
    100003 AAAA100
    100003 AAB102
    100003 AAAC100
    100003 AAD100

    Thank you in advance for any help!

  42. DECLARE @intFlag INT
    SET @intFlag = 1

    WHILE (@intFlag <=5) BEGIN
    print @intflag

    SET @intFlag = @intFlag + 1
    IF @intFlag = 4
    Set @intFlag = @intFlag + 1

    END
    GO

    This code work on the third part.
    3) Example of WHILE Loop with CONTINUE and BREAK keywords

    1
    2
    3
    5

    it skip the 4 :p

    • Can you post your question in English language? My guess is that you want the following code

      select replicate(‘*’,number) from master..spt_values
      where type=’p’ and number between 1 and 7

  43. how to use loop inside my procedure for all month dates..
    my procedure is:

    alter procedure dateWiseAttnd
    as
    begin
    select Employee,
    max(case when day(att_date)=’01′ then Attendance_Status else ” end) ‘day1′,
    max(case when day(att_date)=’02′ then Attendance_Status else ” end) ‘day2′,
    max(case when day(att_date)=’03′ then Attendance_Status else ” end) ‘day3′,
    max(case when day(att_date)=’04′ then Attendance_Status else ” end) ‘day4′,
    max(case when day(att_date)=’05′ then Attendance_Status else ” end) ‘day5′,
    max(case when day(att_date)=’06′ then Attendance_Status else ” end) ‘day6′,
    max(case when day(att_date)=’07′ then Attendance_Status else ” end) ‘day7′,
    max(case when day(att_date)=’08′ then Attendance_Status else ” end) ‘day8′
    from
    (select e.EMP_FIRST_NAME+’ ‘+e.EMP_LAST_NAME Employee,a.Attendance_Status,a.att_date
    from attendance_report a,EMPLOYEE_MASTER e
    where MONTH(a.att_date)=’07′
    and YEAR(a.att_date)=’2011′
    and a.emp_id=e.ID
    )tbl
    group by Employee
    end

  44. Hi Pinal,

    I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
    My question is how to insert 400,000 rows at a time in a table.
    Can you let me know the script to generate it.

    Please do help me out.

    Thanks in Advance.

  45. Hello Sir,

    I have Temp table with the column(rec_seq,acc_id,tax_id,zip,party_name,addr_line1,addr_line2) and I want to display table with different columns in each row like :
    First row : rec_seq,acc_id,tax_id,zip (wehre rec_seq =1)
    Second row : party_name,addr_line1,addr_line2 (where rec_seq=2)
    and store this result into another table
    How can I do that..

    please do help me out.

    Thanks in advance.

  46. Hello Sir,

    I have one table named “Account” and fields are(rec_seq,acc_id,security_key,party_name,party_addr_line1) I want to display different coulmns in each row :

    output should be :

    First row :
    rec_seq,acc_id,security_key (where rec_seq=1 i.e. display selected column when rec_seq =1 )

    Second row:
    rec_seq,party_name,party_addr_line1,party_addr_line2 (where rec_seq=2i.e. display selected column when rec_seq =2)

    Right now Account table data look like below:

    rec_seq acc_id security_key party_name party_addr_line1
    1 10001 1 NULL NULL
    1 10002 2 NULL NULL
    2 10002 NULL b ab
    1 10002 3 NULL NULL
    2 10002 NULL b bc
    2 10002 NULL jk lm

    I want to fetch data from this table and display output like :
    (rec_seq=1 so display rec_seq,acc_id & security_id)
    (rec_seq=2 display party_name and party_addr_line1)

    1 10001 1
    1 10002 2
    2 10002 b ab
    1 10002 3
    2 10002 b bc
    2 10002 jk lm

    Please help me out..

    Thanks in advance!!

  47. –Select name FROM sys.databases order by name

    –Create DataBase And Drop Database
    declare @i int
    declare @sSQL nvarchar(50)
    –Create DataBase
    set @i=1
    while (@i<=10)
    begin
    print @i
    set @sSQL='Create database abc'+ cast(@i as nvarchar(50))
    EXEC(@sSQL)
    set @i=@i+1
    end
    –Drop DataBase
    set @i=1
    while (@i<=10)
    begin
    print @i
    set @sSQL='Drop database abc'+ cast(@i as nvarchar(50))
    EXEC(@sSQL)
    set @i=@i+1
    end

  48. I have one query below is the details,

    Tab:

    col1 Key col2 startdate enddate
    1 10 100 20111215 29991231
    1 10 100 20111220 20111225
    1 10 100 20111215 29991231
    1 10 100 20111215 29991231

    I want output as

    col1 Key col2 startdate enddate
    1 10 100 20111215 20111219
    1 10 100 20111220 20111225
    1 10 100 20111215 20111224
    1 10 100 20111225 29991231

    i think need to write a loop which will update the endate properly.

    Please help…

  49. Hi everyone,
    I have a table which holds data regarding ‘TicketNo’,its ‘Mkrdt’,’Loginid’,’Mkrid’ etc.. I want to list the ‘Startdate’ of each ticket , coz each ticket processing is done many days , and i want to also list ‘Days Taken’ for processing that ticket no.. I had a solution, but i get parcial results..I WANT TO LIST STATDATE N DATSTAKEN IN FRONT OF EACH ENTRY OF A PERTICULAR TICKET. .. I had a query related to this as ..

    SELECT a.ticketno, a.mkrdt
    FROM Tbl_Newtagg1 a, (SELECT TicketNo, min(mkrdt) AS StartDate FROM Tbl_Newtagg1 GROUP BY TicketNo) b
    WHERE a.TicketNo = b.TicketNo AND a.mkrdt =b.startdate

    –but it lists every ticket n its startdate only once.. plz help.. Thanks in advance

  50. Great Examples!
    I would love to share this on my blog.
    Please let me know if you would like to keep this posting content within your blog only.

    Thank you

    Enma

  51. The best way is use a cursor:

    example To Delete All Products

    and if 1 product record has dependencies

    For each Product record, there is a record also in Product Inventory table

    DECLARE
    cursor c1 is
    SELECT
    ID AS i_PRODUCTID,
    FROM PRODUCT
    ORDER BY ID ASC;

    BEGIN
    FOR DELETEPRODUCTRECORD in c1
    LOOP
    DELETE FROM PRODUCT_INVENTORY WHERE PRODUCTID = DELETEPRODUCTRECORD .i_PRODUCTID;

    DELETE FROM PRODUCT WHERE ID = DELETEPRODUCTRECORD .i_PRODUCTID;

    END LOOP;

    END;

    ==

    In terms of performance and simplicity, this is the best method i have seen

    • hi i am shunmuga sundari,
      i have one doubt that ” is it possible execute a ‘insert into’ query into the while or for loop?”

  52. how to use vb function to sql query function

    ex:
    function emp()

    str=”select * from employ”
    set rs=conn.execute(str)

    do while rs.eof=false

    for i= 1 to 6

    if sex=’m’ then

    update status=’male’ where empcode=” & rs!emocode & ”

    else

    update status=’female’ where empcode=” & rs!emocode & ”

    next

    loop

    end function

  53. hi i have a string like
    HHHHHHsuhhhsusu each character represents a date starting from 28/07/2012 and im trying to select it or create a table
    create table #calender (string as char(5)COLLATE SQL_Latin1_General_CP1_CI_AS, dat datetime)
    declare @counter as int
    set @counter = 3
    while (@counter < 367)
    begin
    –print @counter

    –insert into #calender (string,dat)
    select distinct substring (CCAL_Available_Days
    ,@counter,1), dateadd (day,@counter,'2012-07-28 00:00:00.000') date from dbo.CCALCalend
    where CCAL_Year = '2012'
    set @counter = @counter+1
    –select * from #calender

    end
    drop table #calender
    i am getting the error
    The column "string" cannot be modified because it is either a computed column or is the result of a UNION operator.
    can you please help

  54. declare @i int
    declare @n int
    set @i=0
    set @n=10
    while(@i<=@n)
    BEGIN
    PRINT 'A'
    set @i=@i+1
    END

    i have doubt when i use the variables i and n as integer type ,A is printed 11
    times
    declare @i varchar(10)
    declare @n varchar(10)
    set @i=0
    set @n=10
    while(@i<=@n)
    BEGIN
    PRINT 'A'
    set @i=@i+1
    END

    when i use the variables i and n as varchar ,A is printed twice can you please tell me the reason y this could be like this

  55. Hi i have one city table. in that i have to calculate the distance url between those cities. for example i have 5 city names like chennai,hyderabad,kolkuta, delhi, and mumbai. in that i have to calculate distance url like chennai to hyderabad, chennai to kokata, chennai to delhi, chennai to mumbai. i have query for single city but we have many cities in that table. i want to run the query for all cities in single query please help me….

  56. Hello sir,
    I need help from you
    i have two columns
    let me give example
    masterid attributename attributevalue
    ————- ——————– —————–
    1 mode_code barge
    1 sap_code B
    1 Description g

    here i know how many attribute names are there so i can use cases here to update.

    UPDATE CORE.MasterAttr
    SET AttributeValue = (
    CASE AttributeName
    WHEN ‘DESCRIPTION’ THEN ‘Barge’
    WHEN ‘SAP_MODE’ THEN ’01′
    WHEN ‘MODE_CODE’ THEN ‘B’
    END
    ) WHERE MasterId=1

    but in real scenario i dont know how many attribute name will be available.
    so i need to write storedprocedure .the input values will come from c#.
    suppose for master id 2 i can have 12 attribute names and i have to update attribute vales for those attribute names.
    if we give give masterid 4 and we can get 15 attribute names etc.

    so how can we dynamically loop it and write stored procedure sir.it will really helps me alot.can u please help me on this.

  57. Hi Pinal,
    Hope you are doing good.

    The looping query pasted below works fine in updating ‘hour’ in a date time column using NTILE().

    +++++++++++++++++++
    DECLARE @loop int
    DECLARE @inc int

    SET @inc=1
    WHILE (@inc<=24)

    BEGIN

    ;with up AS
    (
    SELECT salesorderdetailid,ntile(24) OVER (order by productid ) as ntile24
    from sales.salesorderdetail
    )
    UPDATE sales.salesorderdetail
    SET modifieddate=DATEADD(hh,@inc,modifieddate)
    from sales.salesorderdetail
    where salesorderdetailid in
    (
    select salesorderdetailid from up
    where NTILe24 =@inc
    )
    PRINT @inc
    SET @inc=@inc+1
    END

    –select * from sales.salesorderdetail tablesample (1000 rows)

    +++++++++++++++++++

    Is there any simplest way to solve this problem?
    OR
    If the query can be modified without using CTE?

    Kindly suggest how to increase the query performance when handling these situations.

    Thanks,
    Arunraja.

  58. I have created a view obtaining the RunningTotal joining the query] to itself that uses ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Location, Time
    ON a.Row >= b.Row AND a.location = b.location
    SUM(b.Qty) AS RunningTotal

    Here is a sample of the results – Question following sample

    Rownumber Location Time X1 X2 X3 Qty RunningTotal
    1 EAST 10/23/13 0:38 0.29719 0.1893 0.13493 250 250
    2 EAST 10/23/13 0:51 0.46958 0.36483 0.22245 250 500
    3 EAST 10/23/13 1:43 0.46958 0.36483 0.22245 -250 250
    4 EAST 10/23/13 1:58 0.46958 0.36483 0.22245 250 500
    5 EAST 10/23/13 2:10 0.46958 0.36483 0.22245 250 750
    6 EAST 10/23/13 2:23 0.23345 0.19788 0.09304 -250 500
    7 EAST 10/23/13 3:22 0.23345 0.19788 0.09304 -250 250
    8 EAST 10/23/13 3:38 0.23345 0.19788 0.09304 -250 0
    9 EAST 10/23/13 3:52 0.23345 0.19788 0.09304 250 250
    10 EAST 10/23/13 4:09 0.29719 0.1893 0.13493 250 500
    11 EAST 10/23/13 4:26 0.29719 0.1893 0.13493 250 750
    12 EAST 10/23/13 4:41 0.29719 0.1893 0.13493 -250 500
    13 EAST 10/23/13 5:07 0.29719 0.1893 0.13493 -250 250
    14 EAST 10/23/13 5:13 0.23345 0.19788 0.09304 -250 0
    15 EAST 10/23/13 5:19 0.23345 0.19788 0.09304 250 250
    16 EAST 10/23/13 5:23 0.29719 0.1893 0.13493 250 500
    1 WEST 10/23/13 0:38 0.29719 0.1893 0.13493 250 250
    2 WEST 10/23/13 0:51 0.46958 0.36483 0.22245 250 500
    3 WEST 10/23/13 1:43 0.46958 0.36483 0.22245 -250 250
    4 WEST 10/23/13 1:58 0.46958 0.36483 0.22245 250 500
    5 WEST 10/23/13 2:10 0.46958 0.36483 0.22245 250 750
    6 WEST 10/23/13 2:23 0.23345 0.19788 0.09304 -250 500
    7 WEST 10/23/13 3:22 0.23345 0.19788 0.09304 -250 250
    8 WEST 10/23/13 3:38 0.23345 0.19788 0.09304 -250 0
    9 WEST 10/23/13 3:52 0.23345 0.19788 0.09304 250 250
    10 WEST 10/23/13 4:09 0.29719 0.1893 0.13493 250 500
    11 WEST 10/23/13 4:26 0.29719 0.1893 0.13493 250 750
    12 WEST 10/23/13 4:41 0.29719 0.1893 0.13493 -250 500
    13 WEST 10/23/13 5:07 0.29719 0.1893 0.13493 -250 250
    14 WEST 10/23/13 5:13 0.23345 0.19788 0.09304 -250 0
    15 WEST 10/23/13 5:19 0.23345 0.19788 0.09304 250 250
    16 WEST 10/23/13 5:23 0.29719 0.1893 0.13493 250 500
    17 WEST 10/23/13 5:45 0.29719 0.1893 0.13493 250 750

    For the calculated results I want need to ignore any record before the LAST RunningTotal of 0 (in the actual table for the same location the RunningTotal could zero out several times over the time span) and get a SumProduct for the X1,X2,X4 values with the final RunningTotal. The query would only use the values in yellow and the results would be :

    Location SumProduct X1 SumProduct X2 SumProduct X3 Total Qty
    EAST 0.26532 0.19359 0.11399 500
    WEST 0.27594 0.19216 0.12097 750

    Can you help me with tthe sql to get these results?

  59. Sorry the yellow highlighting did not show. Basically for one location I need the values from the record after the LAST time the RunningTotal zeros out, although it may zero out many times before.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s