SQL SERVER TUTORIAL IN DEPTH

The blog is to help users about sql server

Others

LightBlog

Breaking

Sunday, 27 October 2013

October 27, 2013

SQL SERVER INSTALLATION PROBLEMS AND LOGIN PROBLEMS

My system is 32 bit + windows 7

Installing SQL SERVER 2008 R2

WHAT IS R2 NOW ?
The "R2" suffix in Microsoft-ese stands for "Release 2".

1)



STEP 2: INSTALL FIRST PART. 

Then go to services 

RUN-->SERVICES.MSC. You must notice these things.





















INSTALL THE SECOND PART :

After that you ip,1433. login ....you are done... enjoy :)









0
By at No comments:

Sunday, 13 October 2013

GO SCRIPT
October 13, 2013

GO SCRIPT


SCRIPTS
--A SQL SCRIPT IS A .SQL FILE (OR OTHER) THAT CONTAINS SQL COMMANDS TO EXECUTE.
--WRITE SOME SQL COMMANDS
--FILE-->SAVE [SAVE SOMEWHER LETS SAY DESKTOP]. YOU CAN OPEN THAT LATER AND EXECUTE.
--SO SCRIPT CONTAINS SERIES OF STATEMENTS
--THE STATEMENTS CAN BE LIKE
--SELECT * FROM EMPLOYEE ;
--USE OF SEMICOLUMN IS OPTIONAL
--NOW THE SEMICOLUMN INDICATES THE TERMINATION OF THE STATEMENT. WE RARELY USE THIS SEMICOLUMN.
--WE USE SEMICOLUMN IN COMMON TABLE EXPRESSION


GO


IT IS A SPECIAL COMMAND THAT IS A BATCH TERMINATOR.
A BATCH IS A SET OF SQL COMMANDS THAT GET SENT TO SQL SERVER IN ONE NETWORK PACKET


àLETS UNDERSTAND THIS CLEARLY

SELECT * FROM EMPLOYEE
GO
SELECT * FROM FRIENDS
GO






àSQL SERVER UNDERSTANDS AS BELOW

SELECT * FROM EMPLOYEE
GO
OK THIS IS TAKEN AS ONE BATCH. THEN ITS EXECUTED

THEN THE NEXT BATCH STARTS
SELECT * FROM FRIENDS
GO


NOW THIS WILL CLEAR THE CONCEPT..I AM EXECUTING THE BELOW STATEMENT IN NEW QUERY WINDOW

SELECT * FROM EMPLOYEE
GO
SELECT *
FROM FRIENDS
WHERE NAME LIKE '%SIBA%'
GO


SELECT * FROM EMPLOYEE
GO
SELECT *
FROM FRIENDS
GO
WHERE NAME LIKE '%SIBA%'

O/P
(9 row(s) affected)

(4 row(s) affected)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.







EXPLANATION
àIT EXECUTES THE FIRST BATCH
SELECT * FROM EMPLOYEE
GO

àTHEN IT EXECUTES THE SECOND BATCH
SELECT *
FROM FRIENDS
GO

àTHEN IT FINDS
WHERE NAME LIKE '%SIBA%' [SO WE GET ERROR MESSAGE LIKE :Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'. ]

àBUT WAIT IT SHOLWS US LINE NUMBER 1 AS ERROR.BUT ACTUALLY ITS NOT LINE NUMBER 1.

àSQL SERVER COMPLETES BOTH BATCH SUCCESSFULLY AND THEN IT FINDS THE ERROR THAT IS LINE NUMBER 1[LINE NUMBER IN REFERENCE TO BATCH].BUT BELOW BELOW IN MANAGEMENT STUDIO IT  SHOWS ACTUAL LINE[16].




PARSE

AFTER EXECUTE A TICK SYMBOL IS THERE. THE SYMBOL IS CALLED PARSE





SELECT * FROM EMPLOYEE
GO
SELECT *
FROM FRIENDS
GO
WHER NAME LIKE '%SIBA%'

àCLICK ON PARSE

 Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'LIKE'.

àIT SHOWS LINE NUMBER 1. AGAIN BECAUSE OF TWO BATCHES ARE COMPLETED..AND THEN ERROR



SELECT * FROM EMPLOYEE
SELECT *
FROM FRIENDS
WHER NAME LIKE '%SIBA%'

-àCLICK ON PARSE
-Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'NAME'.

àNO GO STATEMENT SO LINE NUMBER 4


=== J J J J J J J J HOPE YOU ENJOYED IT J J J J J J J J ====
                      
                            SEE YOU
















0
By at No comments:
Add Default Constraint
October 13, 2013

Add Default Constraint


-------ADD A DEFAULT CONSTRAINT------------------

CREATE TABLE GENDER
(
GENDERID INT PRIMARY KEY NOT NULL,
GENDER VARCHAR(10)
)

INSERT INTO GENDER VALUES(1,'MALE')
INSERT INTO GENDER VALUES(2,'FEMALE')
INSERT INTO GENDER VALUES(3,'UNKNOWN')

SELECT * FROM GENDER

--------------------------------------------

CREATE TABLE PERSON
(
NAME VARCHAR(44),
GENDERID INT FOREIGN KEY REFERENCES GENDER(GENDERID)
)
INSERT INTO PERSON VALUES('ANURAG',1)
INSERT INTO PERSON VALUES('SIBA',1)
INSERT INTO PERSON VALUES('SABITA',2)
INSERT INTO PERSON VALUES('MUKUA',3)

SELECT * FROM PERSON

--LETS INSERT A RECORD INTO PERSON
INSERT INTO PERSON(NAME) VALUES('NIBEDITA')

SELECT * FROM PERSON

--LOOK AT THE NIBEDITA RECORD...WE FIND THAT IT'S NULL. WE NEED A DEFAULT CONSTRAINT..WHENEVER WE DONT KNOW ABOUT THE GENDER IT SHOULD AUTOMATICALLY MAKE IT 3

--ADD DEFAULT CONSTRAINT

ALTER TABLE PERSON
ADD CONSTRAINT DF_PERSON_GENDERID
DEFAULT 3 FOR GENDERID

--WE ARE GIVING A MEANINGFUL NAME LIKE : DF FOR DEFAULT,,,THEN TABLE NAME PERSON,,,THEN COLUMNNAME GENDERID

--LETS INSERT ANOTHER RECORD

INSERT INTO PERSON(NAME) VALUES('ANITA')

SELECT * FROM PERSON
O/P
ANURAG            1
SIBA              1
SABITA            2
MUKUA             3
NIBEDITA          NULL
ANITA             3
--COOOL :)

--LETS DO A TWIST NOW...


INSERT INTO PERSON(NAME,GENDERID) VALUES('JAKAKA',NULL)

--WILL THE DEFAULT FIRE FOR 'JAKAKA' ???????????????

SELECT * FROM PERSON

--ANSWR IS NO

--------IF I WANT TO ADD A NEW COLUMN ,,,WITH DEFAULT

--LETS DO IT

ALTER TABLE PERSON
ADD COUNTRYID INT
CONSTRAINT DF_PERSON_COUNTRYID DEFAULT 0

SELECT * FROM PERSON

--COOL
--O/P
NAME                    GENDERID         COUNTRYID
ANURAG                        1           NULL
SIBA                          1           NULL
SABITA                        2           NULL
MUKUA                         3           NULL
NIBEDITA                   NULL          NULL
ANITA                         3           NULL
JAKAKA                      NULL          NULL


--DROP CONSTRAINT NAME

ALTER TABLE PERSON
DROP CONSTRAINT DF_PERSON_COUNTRYID

--TO CHECK WHETHER YOUR CONSTRAINT IS DROPPED OR NOT ...
--OBJECT EXPLORER-->DATABASE NAME-->TABLES-->PERSON TABLE-->CONSTRAINT



SELECT * FROM PERSON


0
By at No comments:

Monday, 7 October 2013

October 07, 2013

CURSOR IN DETAIL AND THE TRICK TO USE IT AS CUMMULATIVE WAY IN REAL TIME


--LETS TALK ABOUT BRIEFLY ABOUT CURSOR--

--1)WHAT IS CURSOR ?
--ANSWER->A CURSOR JUST POINT ONE SINGLE RECORD AT A POINT. THEN IT MOVES THROUGH THE RECORDS ONE ROW AT A TIME UNTIL YOU REACH THE END.
--2)ADV-->IT GIVES YOU MUCH FINER LEVEL OF CONTROL OVER THE INDIVIDUAL RECORDS IN A DATASET.
--3)DISADVANTAGE--> IT WORKS SLOWLY..INEVITABLY SLOWER THAN SET BASED OPERATION


--HOW TO WRITE CURSOR?

CREATE TABLE

CREATE TABLE #TEMP
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FILM NAME] VARCHAR(100)
)

INSERT INTO #TEMP
SELECT 'SHOLAY'
UNION ALL SELECT 'DDLJ'
UNION ALL SELECT 'KKHH'
UNION ALL SELECT 'SOLDIER'
UNION ALL SELECT 'KAHO NA PYAR HAI'
UNION ALL SELECT 'KOI MIL GAYA'
UNION ALL SELECT 'KRISH'
UNION ALL SELECT 'DHOOM 2'
UNION ALL SELECT 'KRISH 2'
UNION ALL SELECT 'REFUGEE'
UNION ALL SELECT 'ABCD'
UNION ALL SELECT 'CHENNAI EXPRESS'
UNION ALL SELECT 'HOW MANY TIMES IN MUMBAI'

SELECT * FROM #TEMP























CURSOR SYNTAX

--NOW OUR TABLE IS CREATED...NOW IF WE DO SELECT STATEMENT WE GET A SET BASED OPERATION...WE GET ALL THE RECORDS..NOW LETS DO THAT WITH A CURSOR

--SYNTAX

--    DECLARE
--      FOR SELECT
--         OPEN CURSOR
--          
             PROCESS[DO THE THINGS HERE]
--        
           CLOSE CURSOR
--     DEALLOCATE CURSOR


--EXECUTE THIS

EXAMPLE 1

DECLARE  HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR


CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--O/P-- COMMAND EXECUTED SUCCESSFULLY

EXAMPLE 2

DECLARE  HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR

FETCH NEXT FROM HINDIFILM_CURSOR

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--O/P SHOLAY--[THIS IS WORKING LIKE A TOP FUNCTION ISN'T IT :) FETCHING THE TOP ROW THATS IT]

EXAMPLE 3

--EXECUTE THIS[INTRODUCED ORDER BY]

DECLARE  HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM NAME] FROM #TEMP ORDER BY ID DESC
OPEN HINDIFILM_CURSOR

FETCH NEXT FROM HINDIFILM_CURSOR

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--O/P HOW MANY TIMES IN MUMBAI
FETCH STATUS

--EXECUTE THIS[INTRODUCED @@FETCH_STATUS]

DECLARE  HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM NAME] FROM #TEMP ORDER BY ID DESC
OPEN HINDIFILM_CURSOR

FETCH NEXT FROM HINDIFILM_CURSOR

     WHILE @@FETCH_STATUS=0--IT MEANS IF IT IS ZERO ..THEN OUR CURSOR IS ABLE TO FETCH THE NEXT ROW...SO WHEN IT REACHES THE END
     FETCH NEXT FROM HINDIFILM_CURSOR

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--O/P ALL THE THIRTEEN RECORDS...TOOK 2 SECONDS...HERE ONLY 13 RECORDS...IF THE COUNT INCREASES THEN THE TIME WILL ALSO INCREASE























SELECT * FROM #TEMP--TOOK NOT EVEN 1 SECOND ...SO YOU GOT IT THAT CURSOR IS LITTLE SLOW ...

SCROLL

--------------------------------SCROLL CURSOR------------------------------------
DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT [FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR

FETCH FIRST FROM HINDIFILM_CURSOR

     WHILE @@FETCH_STATUS=0
     FETCH NEXT FROM HINDIFILM_CURSOR

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--IT WORKS..

--O/P IS ALL THE THIRTEEN RECORDS FROM FIRST TO LAST...YOU CAN USE FIRST/LAST/PRIOR WITH SCROLL CURSOR ONLY























SCROLL CONTINUED


DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT [FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR

FETCH LAST FROM HINDIFILM_CURSOR--LAST IS INTRODUCED

     WHILE @@FETCH_STATUS=0
     FETCH PRIOR FROM HINDIFILM_CURSOR--PRIOR IS INTRODUCED

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--O/P IT GIVES ALL THE RECORDS ,,,BUT FROM LAST TO FIRST..



ABSOLUTE àRELATIVE

--------------------------ABSOLUTE-----------------------------------------------

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR

FETCH ABSOLUTE 3 FROM HINDIFILM_CURSOR--ABSOLUTE IS INTRODUCED

     WHILE @@FETCH_STATUS=0
     FETCH RELATIVE 3 FROM HINDIFILM_CURSOR--RELATIVE IS INTRODUCED

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR






















--ITS NOT NECESSARILY YOU HAVE TO WRITE RELATIVE. IF YOU WANT TO MOVE FROM 3RD RECORD ONE BY ONE..MEANS 3RD,4TH,5TH     AND SO ON..THEN YOU CAN WRITE IN TWO WAYS

--1)SIMPLY MAKE IT AS : FETCH NEXT FROM HINDIFILM_CURSOR
--2)MAKE IT AS : FETCH RELATIVE 1 FROM HINDIFILM_CURSOR

ABSOLUTEàRELATIVE EXAMPLE 2


--------------------NEGATIVE ----------------------------------------------------

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR

FETCH ABSOLUTE -4 FROM HINDIFILM_CURSOR--ABSOLUTE IS INTRODUCED

     WHILE @@FETCH_STATUS=0
     FETCH RELATIVE -3 FROM HINDIFILM_CURSOR--RELATIVE IS INTRODUCED

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR



















--EXPLANATION: IT STARTS CALCULATION LIKE 13,12,11,10 ,,,IT PICKS 10.

VALUES TO VARIABLE IN CURSOR


--------------------------------READING THE VALUES INTO VARIABLE-----------------

DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR

FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME


     WHILE @@FETCH_STATUS=0
     FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME

CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--O/P COMMAND COMPLETED SUCCESSFULLY


EXECUTING THE VARIABLES INSIDE CURSOR AND HOW PRINT WORKS


-----------------------------------------EXECUTING THE VARIABLES-----------------

DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME
 
        PRINT '================================================='
        PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
        PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
        PRINT '<=================================================>'
 
     WHILE @@FETCH_STATUS=0
     BEGIN--ADDED
    
  
              IF (@FILMNAME='SHOLAY')
              BEGIN
              UPDATE #TEMP SET [FILM NAME]=@FILMNAME +' ' +'==>OLD IS GOLD' WHERE [FILM NAME]=@FILMNAME
              END
          ELSE IF(@ID=5)
          BEGIN
          UPDATE #TEMP SET [FILM NAME]=@FILMNAME + ' '+'==> THE FIVTH MOVIE' WHERE ID=@ID
          END
          ELSE
          BEGIN 
            UPDATE #TEMP SET [FILM NAME]=@FILMNAME +' ' +'==>NORMAL HIT MOVIE' WHERE [FILM NAME]=@FILMNAME
          END
     FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME
     END--ADDED
     SELECT * FROM #TEMP
CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR


--O/P










































--CLICK ON MESSAGE JUST ADJACENT TO THE RESULT.--ONE THING WE NOTICE IS THAT WE GET THIS RESULT BELOW ,,,AND THEN WE GET 1 ROW(S) AFFECTED FOR ALL RECORDS
  

--SO I JUST MEAN TO TELL YOU HOW CURSOR WORKS... NEXT EXAMPLE WE WILL PUT THE PRINT INSIDE WHILE CONDITION ...YOU WILL NOTICE A DIFFERENCE


PRINT INSIDE WHILE LOOP


DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME

 
     WHILE @@FETCH_STATUS=0
     BEGIN
    
        PRINT '================================================='
        PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
        PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
        PRINT '<=================================================>'
              IF (@FILMNAME='SHOLAY')
              BEGIN
              UPDATE #TEMP SET [FILM NAME]=@FILMNAME +' ' +'==>OLD IS GOLD' WHERE [FILM NAME]=@FILMNAME
              END
          ELSE IF(@ID=5)
          BEGIN
          UPDATE #TEMP SET [FILM NAME]=@FILMNAME + ' '+'==> THE FIVTH MOVIE' WHERE ID=@ID
          END
          ELSE
          BEGIN 
            UPDATE #TEMP SET [FILM NAME]=@FILMNAME +' ' +'==>NORMAL HIT MOVIE' WHERE [FILM NAME]=@FILMNAME
          END
     FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME
     END
     SELECT * FROM #TEMP
CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--EXECUTE AND SEE THE MESSAGE JUST NEAR THE RESULT TAB
--HOPE YOU GOT IT....










































ENCAPSULATING CURSOR INSIDE A STORED PROCEDURE

LAST BUT NOT THE LEAST HOW WE WILL ENCAPSULATE IT IN A STORED PROCDEDURE
IT'S VERY SIMPLE----


CREATE PROCEDURE CURSOR_PROC
AS
BEGIN

DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME

 
     WHILE @@FETCH_STATUS=0
     BEGIN
    
        PRINT '================================================='
        PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
        PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
        PRINT '<=================================================>'
              IF (@FILMNAME='SHOLAY')
              BEGIN
              UPDATE #TEMP SET [FILM NAME]=@FILMNAME +' ' +'==>OLD IS GOLD' WHERE [FILM NAME]=@FILMNAME
              END
          ELSE IF(@ID=5)
          BEGIN
          UPDATE #TEMP SET [FILM NAME]=@FILMNAME + ' '+'==> THE FIVTH MOVIE' WHERE ID=@ID
          END
          FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME
     END
     SELECT * FROM #TEMP
CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR
END

--EXEC CURSOR_PROC
--SO OUR CURSOR IS WRAPPED INSIDE STORED PROCEDURE..THERE ARE TWO WAYS TO EXECUTE THE STORED PRODEDURE

--1) EXECUTE PRODUDURENAME/ EXEC PROCEDURENAME
--2) SELECT THE PROCEDURE NAME AND HIT F5.[MEANS SELECT THIS AND HIT F5 -->CURSOR_PROC ]























CALLING A STORED PROCEDURE INSIDE THE CURSOR



--LETS CALL A STORED PROCEDURE INSIDE A CURSOR
CURSOR

DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)

DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME

 
     WHILE @@FETCH_STATUS=0
     BEGIN
    
        PRINT '================================================='
        PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
        PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
        PRINT '<=================================================>'
          
          EXEC PROC_INSIDECURSOR @ID,@FILMNAME –THE STORED PROCEDURE IS WRITTEN BELOW..WE ARE PASSING THE CURSOR PARAMETERS TO THE STORED PROCEDURE
          
          FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME
     END
     SELECT * FROM #TEMP
CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--I M CREATING SIMPLE STORED PROCEDURE TO HAVE A BETTER UNDERSTANDING.

PROCEDURE 2

CREATE PROCEDURE PROC_INSIDECURSOR
(
@ID INT,
@FILMNAME VARCHAR(MAX)
)
AS
BEGIN

IF(SUBSTRING(@FILMNAME,1,1)='S')
BEGIN
  PRINT 'THE FILM STARTS WITH S'
END

ELSE
  PRINT 'THE FILM DOESNT START WITH S'
END

--O/P CLICK ON MESSAGE JUST ADJACENT TO THE RESULT. YOU WILL GET TO KNOW.
SELECT THE CURSOR AND EXECUTE IT.



















=================================================================================
SCROLL TYPE IN CURSOR [FORWARD_ONLY]

--AS WE HAVE EARLIER NOTICED THAT 
SCROLL AFTER CURSOR ALLOWS US 
1)FETCH FIRST
2)FETCH LAST
3)FETCH PRIOR

--BUT FORWARD_ONLY ALLOWS ONLY FETCH NEXT.

DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)

DECLARE  HINDIFILM_CURSOR1 CURSOR FORWARD_ONLY
FOR
SELECT ID,[FILM NAME] FROM #TEMP
OPEN HINDIFILM_CURSOR1
FETCH FIRST FROM HINDIFILM_CURSOR1 INTO @ID,@FILMNAME

 
     WHILE @@FETCH_STATUS=0
     BEGIN
    
      PRINT '================================================='
        PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
        PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
      PRINT '<=================================================>'
          
          EXEC PROC_INSIDECURSOR @ID,@FILMNAME
          
          FETCH NEXT FROM HINDIFILM_CURSOR1 INTO @ID,@FILMNAME
     END
     SELECT * FROM #TEMP
CLOSE HINDIFILM_CURSOR1
DEALLOCATE HINDIFILM_CURSOR1

--I M CREATING SIMPLE STORED PROCEDURE TO HAVE A BETTER UNDERSTANDING.

CREATE PROCEDURE PROC_INSIDECURSOR
(
@ID INT,
@FILMNAME VARCHAR(MAX)
)
AS
BEGIN

IF(SUBSTRING(@FILMNAME,1,1)='S')
BEGIN
  PRINT 'THE FILM STARTS WITH S'
END

ELSE
  PRINT 'THE FILM DOESNT START WITH S'
END













----ERROR --> Msg 16911, Level 16, State 1, Line 9
--fetch: The fetch type first cannot be used with forward only cursors.
 FETCH FIRST >< FORWARD ONLY




One scenario i faced in the real time project where i needed cummulative . Then how I tackled using cursor is explained below using a simple example.





















CUMMULATIVE IN CURSOR CONTINUED

DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE @DIR VARCHAR(100)
DECLARE @RELEASE DATETIME,@AWARD INT ,@TOTALAWARD INT

SET @TOTALAWARD=0


DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR SELECT ID,[FILM NAME],DIRECTOR,[RELEASE DATE],[NATIONAL AWARD] FROM #TEMP1
FOR UPDATE OF [CUMMULATIVE NATIONAL AWARD]
OPEN HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD

 
     WHILE @@FETCH_STATUS=0
     BEGIN
    
      PRINT '================================================='
     
      SET @TOTALAWARD+=@AWARD
      PRINT 'TOTAL AWARDS: ' + CAST(@TOTALAWARD AS VARCHAR(100))
        
          UPDATE #TEMP1 SET [CUMMULATIVE NATIONAL AWARD]=@TOTALAWARD
        
          FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD
      END
     SELECT * FROM #TEMP1
CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--IF I EXECUTE THIS WHAT HAPPENS....OOPS WE GET A STRANGE O/P
--O/P
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156

--Now this is logical. Let me explain you what exactly is happening.
--first time the column gets updated to 12.
--second time the column gets updated to 24. 
--it continues till thirteen times. And we are updating the column of the table.


--LETS SET NULL TO OUR CUMMULATIVE NATIONAL AWARD AGAIN AND TRY ADDING WHERE CLAUSE

UPDATE #TEMP1 SET [CUMMULATIVE NATIONAL AWARD]=NULL

 ===============================================================================

CUMMULATIVE IN CURSOR CONTINUED [WHERE CLUASE TRICK]

-------------------------------CHANGE THE CURSOR A LITTLE BIT--------------------




DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE @DIR VARCHAR(100)
DECLARE @RELEASE DATETIME,@AWARD INT ,@TOTALAWARD INT

SET @TOTALAWARD=0


DECLARE  HINDIFILM_CURSOR CURSOR SCROLL
FOR SELECT ID,[FILM NAME],DIRECTOR,[RELEASE DATE],[NATIONAL AWARD] FROM #TEMP1
FOR UPDATE OF [CUMMULATIVE NATIONAL AWARD]
OPEN HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD

 
     WHILE @@FETCH_STATUS=0
     BEGIN
    
      PRINT '================================================='
     
      SET @TOTALAWARD+=@AWARD
      PRINT 'TOTAL AWARDS: ' + CAST(@TOTALAWARD AS VARCHAR(100))
        
         UPDATE #TEMP1 SET [CUMMULATIVE NATIONAL AWARD]=@TOTALAWARD
         WHERE CURRENT OF HINDIFILM_CURSOR
        
          FETCH NEXT FROM HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD
      END
     SELECT * FROM #TEMP1
CLOSE HINDIFILM_CURSOR
DEALLOCATE HINDIFILM_CURSOR

--THIS WORKS COOL
--O/P
--CUMMULATIVE NATIONAL AWARD
--12
--24
--36
--48
--60
--72
--84
--96
--108
--120
--132
--144
--156


J J J J SO THIS ENDS OUR CURSOR TUTORIAL..HOPE YOU ENJOYED IT...KEEP LEARNING THE THINGS..GOOD BYE J J J J

0
By at No comments:

 test