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)
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
Realy it helped me on time for while command in sql
Thanks.
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.
It is the job of front end to prompt for the user input
You can’t do it from sql
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
hi i am really stuck in my assingnment……can u help me please?
Hai friend
In the above code is there one mistake that is @j=@i*@j it is not possible that at that place you have to put the @j=@i*1 …………………ok
In the 3rd line ur trying to print @j but no value is assigned to @j .. 1st assign the value and print
Thank you..helped me a lot..
HI
Could you please explain the cursor vs while loop ..
Is both are performance killer … how the while differ from cursor
Both are ineffecient for large set of data
However it depends
There is a chance that your cursor hit a table only once and some manipulations are done on the data. Whereas while loop may hit a table for each loop.
Hi, Please help me in dis,
i want to update the like first i need to check the value whether it is null or blank.. if yes den update the Null record to 7777(1st value), for the next Value updation it suppose to add 7777+1,next 7777+1+1, it shd go like dis… n store error message ‘Blank record or Null Record.’
Refer this quirky update technique
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx
Thank you ………….
Help me a lot…………………….
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.
You cannot do this from a function. Use a stored procedure for this becuase you need to write dynamic sql
good knowledge.. TQ
nice1
Thanks man..
It really saved my ass.
:->
Thanks,
It had given me a good Idea to solve errors in my project.
I want to use while loop inside the Common Table Expression.
can U tell me how to use it /
You can use union or union all inside a CTE
Nice tutorial, But I coudn’t find what I wanted.
How can I update many row using sql loop query ???
Post some sample data with expected result
Very GOOD Example
Keep it up
Thank you very much for your help on this example.
i want a function for factorial
tx
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
Thanks
Cheers Dude
Thankyou Dave, it helped me a lot….
Hi!
What is difference between navigating records in while loop and cursor ?
It depends on what you want to do
Can you give us more informations on what you are trying to do?
thanks………
is there a instruction like foreach row???
It is possible only in a cursor and not in while loop
nice article.
but how can we iterate through each record obtained from the select statement in the stored procedure?
thanks in advance
You can use a cursor. But I think you can avoid a cursor using a set based approach. Let us know what you want to do with each row
Thanks
How to give a static prefix for the number incremented
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
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?
Why do you want to get the names one by one?
You can write a SELECT statement that brings you all the names
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)
WHERE
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
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
select @str=coalesce(@str+”,”)+user_tree where app_no=’090001′
Thank You Very Much…
nice loop
nice helped me alot, but i am looking to iterate the loop no of rows in a table can any body help me
@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.
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.
If you want to randomly show 50000 rows, use
Select top 50000 col…. from your_table
order by newid()
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
Hello Sir,
Can we use while loop in stored procedures??
If so please explain how to use?
Thanks,
Anjaneyulu V
@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
willu please give me simple store
procedure programs
Thanks and Regards
Umar
Nice one
Nice article
Thanks alot !!!
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.
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
I think
while @val
should be
while @val=1
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.
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
Sir, i want to writ a query parent child relation for mlm business
Read about Expanding hierarchies in SQL Server help file
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.
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
Thank you 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
)
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
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.
@Jeffrey
To SUM the grades: SELECT Name, SUM(grade) FROM demo GROUP BY Name;
To concatenate a field, use XML. Pinal has an example here: http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/
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
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”’ ”’
”’ ”’
Third try the charm I guess, sorry the text is like this without spaces.
: & # x 0 D ;
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.
@Jeffrey
Actually, i do not know.
[didn't see your replies until just now]
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
@Ravish Dhingra
What is the problem? Just UPDATE the underlying TABLE and the VIEW will show the new data.
select ROW_NUMBER() OVER (ORDER BY DetailID ASC) AS ROWID,* from trnInvoiceTicket
What is the equivalent of the ROW_NUMBER() here in WHILE Loop?
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
How are you executing the procedure? Post the code you have used. Also expalin what you are trying to do with the code
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.
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.
You need post some sample data with expected result to help you
hi
i want to iterate through Columns and update tham with any random value in specified range throug query how can i do this?
It would be helpful if you post some sample data with expected result
I see keyword “return” is also working instead of “break” keyword.
Return will not execute any statements at the end of while loop
Thanks its vary useful.
sir please give an example of while statements using geany?
The SQL Server help file has explanation as well as code examples for While loop
|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. :)
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
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
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
insert into temp(id)
SELECT id FROM Firm f
WHERE not EXISTS
(SELECT * FROM SBEFirms s WHERE f.id = s.id )
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)
Thanks a lot………….
thnks………..its very helpfull
welcome
hi
sir i need to a sample of a loop in after insert trigger
plz
tnx a lot
Can you explain why you want this?
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
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
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.’
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
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
??????
I need complete information
hello ,
i need to write a funtion that shows , by book name show me the author name.
could u plz help me.
no i cant help u
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.
Jason
Can you please post, your required output format.
Its very difficult to understand.
~IM.
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
Hello Sir,
Thank you for your post. Its very useful.
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!
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
Thank u sir.
It helped me a lot.
Mw tanya gan caranya buat bintang-bintang dari SQL Server 2005 gmna :
Contoh :
*
* *
* * *
* * * *
* * * * *
* * * * * *
* * * * * * *
Mksh gan
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
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
Pinal Dave thanks a lot for the example..
Fernando
from Bolivia
how to print * using sql query(traingle shape)
please give query here
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.
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.
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!!
thank you very much
thank you very much, you save me live.
–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
i want program in for..loop ..give some example
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…
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
try using UNION function with above query and other query which will give only start date
you just copy all your sample in msdn you – oink oink.
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
Nice way
Genial !! muchas gracias.
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?”
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
Can we replace column values of a table with a series of values using While statement?
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
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
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….
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.
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.