Saturday, February 22, 2014

How can I use ROW_NUMBER() if the column name is not identified?

I got a question from my friend.

Question: How can I use ROW_NUMBER() if the column name is not identified?
Answer: There is many ways to achieve it practically.

Example1: 
--Normal Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY @@ROWCOUNT), * FROM myTable

Example2: 
--Normal Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), * FROM myTable

Example3:  
--Random Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY NEWID()), * FROM myTable

Saturday, February 1, 2014

WHILE LOOP with CONTINUE and BREAK keywords

WHILE loop enables to execute any SQL statement or block of statement repeatedly as long as the specified condition is true. The execution can be controlled by two keywords BREAK and CONTINUE by putting inside the loop as per user's need.

BREAK keyword: This keyword is use to stop execution if user's specified condition got true.
CONTINUE keyword: This keyword is use to skip execution of below statement. User can specify condition as per need.

Example1: WHILE loop

DECLARE @val INT = 100;
WHILE (@val <= 500)
BEGIN
       PRINT @val
       SET @val = @val + 100

END

ResultSet:
100
200
300
400
500

Example2: WHILE loop with BREAK

DECLARE @val INT = 100;
WHILE (@val <= 500)
BEGIN
       PRINT @val
       SET @val = @val + 100
       IF @val>350
       BREAK;
END

ResultSet:
100
200
300

Example3: WHILE loop with CONTINUE 

DECLARE @val INT = 100;
WHILE (@val <= 500)
BEGIN
       PRINT @val
       SET @val = @val + 100
       CONTINUE;
       IF @val>350
       BREAK;
END

ResultSet:
100
200
300
400
500