SUBQUERIES IN SQL SERVER

• A QUERY IN THE WHERE CLASS IS A KNOWN AS SUBQUERY OR NESTED QUERY OR INNER QUERY
• SUB QUERY WOULD BE EXECUTED FIRST ,THEN THE RESULT PASSED TO OUTER QUERY ,FINALLY OUTER QUERY WILL BE EXECUTED.
• MAX 32 LEVELS OF NESTING IS ALLOWED IN SUBQUERY

SYNTAX:

                    SELECT COLUMN_NAME [, COLUMN_NAME 
                    FROM   TABLE1 [, TABLE2 ]

                    WHERE  COLUMN_NAME  [COPARISION_OPERATOR]

                    (SELECT COLUMN_NAME [, COLUMN_NAME ]

                     FROM TABLE1 [, TABLE2 ]

                     [WHERE])

---ABOVE BLUE COLOR CODE IS SUB QUERY OR "INNER QUERY"

----PURPLE  COLOR CODE IS 'OUTER QUERY'

• SUBQUERIES CAN BE USED WITH THE SELECT, INSERT, UPDATE, AND DELETE STATEMENTS ALONG WITH THE OPERATORS LIKE =, <, >, >=, <=,  BETWEEN ETC.

COMPARISION OPERATOR:

"=,<,>,<=,>="      WHEN SUBQUERY RETURNS A SINGLE VALUE.(SINGLE COLUMN)
"="  ------ EQUALS
"<"---------LESS THAN
">"--------GREATER THAN
"<="------LESS THAN OR EQUAL TO
">="------GREATER THAN OR EQUAL TO
"< >"-----NOT EQUAL TO
"!="-----NOT EQUAL TO
"!<"----NOT LESS THAN
"!>"---NOT LESS THAN

THE RESULT OF A COMPARISON OPERATOR HAS THE BOOLEAN DATA TYPE. THIS HAS THREE VALUES: TRUE, FALSE, AND UNKNOWN. EXPRESSIONS THAT RETURN A BOOLEAN DATA TYPE ARE KNOWN AS BOOLEAN EXPRESSIONS.

EXAMPLES:

SELECT * FROM EMP WHERE SAL= (SELECT MAX(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL= (SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL<=(SELECT AVG(SAL) FROM EMP )

SELECT * FROM EMP WHERE SAL>=(SELECT AVG(SAL) FROM EMP)

TOP2 SAL:

SELECT * FROM EMP WHERE SAL=(

SELECT  MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP))

TOP3 SAL

SELECT * FROM EMP WHERE SAL=(

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP

WHERE SAL<(SELECT MAX(SAL) FROM EMP)))

IN & NOT IN:  WHEN SUBQUERY RETURNS MULTIPLE VALUES.(SINGLE COLUMN)

TOP 2 SAL:

SELECT  * FROM EMP WHERE  SAL=(   SELECT MIN(SAL) FROM EMP
                                       WHERE SAL IN(SELECT DISTINCT TOP 2 SAL FROM EMP))

TOP 3 SAL:

SELECT  * FROM EMP WHERE  SAL=(   SELECT MIN(SAL) FROM EMP
                                       WHERE SAL IN(SELECT DISTINCT TOP 3 SAL FROM EMP))

• IF THE VALUE OF TEST_EXPRESSION IS EQUAL TO ANY VALUE RETURNED BY SUBQUERY OR IS EQUAL TO ANY EXPRESSION FROM THE COMMA-SEPARATED LIST, THE RESULT VALUE IS TRUE;OTHERWISE, THE RESULT VALUE IS FALSE.

• USING NOT IN NEGATES THE SUBQUERY VALUE OR EXPRESSION.

RISTRICTIONS IN SUB QUERIES:

• SUBQUERIES MUST BE ENCLOSED WITHIN PARENTHESES.
• A SUBQUERY MUST INCLUDE A SELECT CLAUSE AND A FROM CLAUSE
• A SUBQUERY CAN INCLUDE OPTIONAL WHERE, GROUP BY, AND HAVING CLAUSES.
• YOU CAN INCLUDE AN ORDER BY CLAUSE ONLY WHEN A TOP CLAUSE IS INCLUDED.
• THE BETWEEN OPERATOR CANNOT BE USED WITH A SUBQUERY; HOWEVER, THE BETWEEN OPERATOR CAN BE USED WITHIN THE SUBQUERY.

MERGE STATEMENT IN SQL SERVER

• SQL MERGE STATEMENT WAS INTRODUCED IN SQL SERVER 2008.
• MERGE PERFORMS INSERT, UPDATE, OR DELETE OPERATIONS ON A TARGET TABLE BASED ONTHE RESULTS OF A JOIN WITH A SOURCE TABLE.

       

  SYNTAX:

    

                  MERGE  <HINT>  [INTO]   <TARGET-TABLE_NAME>

                  USING <SOURCE_TABLE_VIEW_OR_QUERY>

                   ON (<CONDITION>)

                  WHEN MATCHED THEN <UPDATE_CLAUSE>

                  WHEN NOT MATCHED THEN <INSERT_CLAUSE>;

---CONDITION MATCHES UPDATE WILL PERFOM THAT PARTICULAR RECORD.

---CONDITION NOT MATCHED INSERT RECORD IN A TABLE

---DELETE IS A OPTINAL.WHEN WE DELETE IN MATCHED CONDITION.

IMPORTANT NOTES:

•    SEMICOLON IS MANDATORY AFTER THE MERGE STATEMENT.

• WHEN THERE IS A MATCH CLAUSE USED ALONG WITH SOME CONDITION, IT HAS TO BE SPECIFIED FIRST AMONGST ALL OTHER WHEN MATCH CLAUSE. 

USES:

   

• USEFUL IN BOTH OLTP AND DATA WAREHOUSE ENVIRONMENTS 

          OLTP: MERGING RECENT INFORMATION FROM EXTERNAL SOURCE

            DW: INCREMENTAL UPDATES OF FACT, SLOWLY CHANGING DIMENSIONS.

EXAMPLES:

SOURCE TABLE:

CREATE TABLE STUDENT_DETAILS

(

       STU_ID INTEGER PRIMARY KEY,

       STU_NAME VARCHAR(15)

)

TARGET TABLE:

CREATE TABLE STUDENT_TOTAL_MARKS

(

      STU_ID INTEGER REFERENCES STUDENTDETAILS,

        STU_MARKS INTEGER

)

INSERT SOURCE TABLE DATA            INSERT TARGET TABLE DATA

STU_ID     STU_NAME                                             STU_ID    STU _MARKS   

    1                    SMITH                                                  1                   230

    2                    ALLEN                                                  2                   255

    3                     JONES                                                   3                  200

    4                   MARTIN

    5                    JAMES

In our example we will consider three main conditions while we merge this twotables.

     1.DELETE THE RECORDS WHOSE MARKS ARE MORE THAN 250.

     2.UPDATE MARKS AND ADD 25 TO EACH AS INTERNALS IF RECORDS EXIST.

      3.INSERT THE RECORDS IF RECORD DOES NOT EXISTS

MERGE  STUDENT_TOTAL_MARKS AS A

USING  (SELECT STU_ID,STU_NAME FROM STUDENT_DETAILS) AS B

ON A.STU_ID=B.STU_ID

WHEN MATCHED AND STU_MARKS>250 THEN DELETE

WHEN MATCHED THEN UPDATE  SET STU_MARKS=STU_MARKS+25

WHEN NOT MATCHED INSERT INTO (STU_ID,STU_MARKS)

VALUES(B.STU_ID,25);

OUTPUT:

IMPLEMENTING OUTPUT CLASS IN MERGE:


CREATE TABLE  BOOKINVENTORY  -- TARGET
(
  TITLEID INT NOT NULL PRIMARY KEY,
  TITLE NVARCHAR(100) NOT NULL,
  QUANTITY INT NOT NULL
    CONSTRAINT QUANTITY_DEFAULT_1 DEFAULT 0
);

CREATE TABLE   BOOKORDER  -- SOURCE
(
  TITLEID INT NOT NULL PRIMARY KEY,
  TITLE NVARCHAR(100) NOT NULL,
  QUANTITY INT NOT NULL
    CONSTRAINT QUANTITY_DEFAULT_2 DEFAULT 0
);
INSERT BOOKINVENTORY VALUES  (1, 'THE CATCHER IN THE RYE', 6),
                                                                       (2, 'PRIDE AND PREJUDICE', 3),
                                                                       (3, 'THE GREAT GATSBY', 0),
                                                                       (5, 'JANE EYRE', 0),
                                                                       (6, 'CATCH 22', 0),
                                                                       (8, 'SLAUGHTERHOUSE FIVE', 4);

INSERT BOOKORDER VALUES  (1, 'THE CATCHER IN THE RYE', 3),
                                                             (3, 'THE GREAT GATSBY', 0),
                                                             (4, 'GONE WITH THE WIND', 4),
                                                             (5, 'JANE EYRE', 5),
                                                             (7, 'AGE OF INNOCENCE', 8)


CAPTURE INSERT,UPDATE,DELETE:

DECLARE @MERGEOUTPUT TABLE
(
  ACTIONTYPE NVARCHAR(10),
  DELTITLEID INT,
  INSTITLEID INT,
  DELTITLE NVARCHAR(50),
  INSTITLE NVARCHAR(50),
  DELQUANTITY INT,
  INSQUANTITY INT
);

MERGE BOOKINVENTORY  AS  A
USING BOOKORDER AS B
ON BI.TITLEID = BO.TITLEID
WHEN MATCHED AND
  BI.QUANTITY + BO.QUANTITY = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE
  SET BI.QUANTITY = BI.QUANTITY + BO.QUANTITY
WHEN NOT MATCHED BY TARGET THEN
  INSERT (TITLEID, TITLE, QUANTITY)
  VALUES (BO.TITLEID, BO.TITLE,BO.QUANTITY)
WHEN NOT MATCHED BY SOURCE
  AND BI.QUANTITY = 0 THEN
  DELETE
OUTPUT
    $ACTION,
    DELETED.TITLEID,
    INSERTED.TITLEID,
    DELETED.TITLE,
    INSERTED.TITLE,
    DELETED.QUANTITY,
    INSERTED.QUANTITY
  INTO @MERGEOUTPUT;

SELECT * FROM BOOKINVENTORY;

SELECT * FROM @MERGEOUTPUT

OUTPUT PARAMETER IN SQL SERVER

• OUTPUT PARAMETERS ARE NORMAL STORED PROCEDURE PARAMETERS THAT ARE MARKED WITH THE KEYWORD OUTPUT(OUT for short)

• BESIDES USING INPUT PARAMETERS, STORED PROCEDURES CAN ALSO RETURN OUTPUT PARAMETERS AND RETURN VALUES. OUTPUT PARAMETERS BEHAVE SIMILARLY TO INPUT PARAMETERS, BUT HAVE TO BE DECLARED WITH THE OUTPUT KEYWORD.
•   
•   OUTPUT PARAMETERS ALLOWS YOU TO CAPTURE OUTPUT OF THE STORED PROCEDURE  IN  LOCAL VARIABLE

•  OUTPUT CLAUSE RETURNS THE INFORMATION OF ALLTHE ROWS AFFECTED BY THE INSERT, DELETE,UPDATE& MERGE STATEMENTS 

EXAMPLES:

CREATE SAMPLE TABLE:

CREATE  TABLE TBL_OUTPUT_CLAUSE(ID INT IDENTITY,NAME VARCHAR(30),GENDER VARCHAR(30),EMAIL VARCHAR(50))

DATA INSER INTO TABLE:

INSERT INTO TBL_OUTPUT_CLAUSE VALUES('BABJI','MALE','BABJI.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('BHAVANI','FEMALE','BHAVANI.MM@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('MANJU','MALE','MANJU.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('BHARATH','MALE','BHARATHREDDY407@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('LAKSHMI','FEMALE','LAKSHMI.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('SWATHI','FEMALE','SWATHI.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('JAIPAL','MALE','JAIPAL.REDDY1@GMAIL.COM')


             1. BY USING OUTPUT CLAUSE TO INSERT IDENTITY VALUE

CREATE PROC USP_OUTPUT_CLAUSE(@ID INT OUT,@NAME VARCHAR(30),@GENDER VARCHAR(30),@EMAIL VARCHAR(50))
AS
BEGIN
      INSERT INTO TBL_OUTPUT_CLAUSE (NAME,GENDER,EMAIL)                                                 VALUES(@NAME,@GENDER,@EMAIL)

      SET @ID=@@IDENTITY

     SELECT * FROM TBL_OUTPUT_CLAUSE


END

  DECLARE LOCAL PARAMETER  WITH SAME DATATYPE 
               OF OUTPUT PARAMETER DATATYPE


DECLARE @OUT_ID INT

EXEC USP_OUTPUT_CLAUSE @ID=@OUT_ID OUT ,@NAME='NAVEEN',@GENDER='MALE',@EMAIL='NAVEEN.KUMAR@GMAIL.COM'

PRINT @OUT_ID



----OUTPUT: 8


2.COUNT  MALES AND FEMALE IN A TABLE BY USING OUTPUT PARAMETER


CREATE PROC USP_OUTPUT_CLAUSE1(@GENDER VARCHAR(30),@EMP_COUNT INT OUT)
AS
BEGIN
            SELECT @EMP_COUNT=COUNT(ID)  FROM  TBL_OUTPUT_CLAUSE  WHERE                         GENDER=@GENDER


END

• FIRST INITIALISE A VARIABLE OF SAME DATATYPE AS THAT OF THE OUTPUT PARAMETER. HERE WE HAVE DECLARED @OUT_COUNT INTEGER VARIABLE.
• THEN PASS THE @OUT_COUNT VARIABLE TO THE STORED PROCEDURE, YOU HAVE TO SPECIFY THE OUTPUT KEYWORD. IF YOU DON’T SPECIFY THE OUTPUT KEYWORD, THE VARIABLE WILL BE NULL.

DECLARE @OUT_COUNT  INT

EXEC USP_OUTPUT_CLAUSE @GENDER='MALE',@EMP_COUNT=@OUT_COUNT OUT

PRINT @OUT_COUNT

OUTPUT: 4

• IF YOU DONT SPECIFY THE OUTPUT KEYWORD, WHILE EXECUTING THE STORED PROCEDURE, THE @OUT_COUNT VARIABLE WILL BE NULL. IN THE EXAMPLE BELOW, WE HAVE NOT SPECIFIED OUTPUT KEYWORD. SO WHILE EXECUTING THE STORED PROCEDURE, A MESSAGE OF @OUT_COUNT IS NULL IS PRINTED.

DECLARE @OUT_COUNT  INT

EXEC USP_OUTPUT_CLAUSE @GENDER='MALE',@EMP_COUNT=@OUT_COUNT OUT

IF (@OUT_COUNT IS NULL)

   PRINT '@OUT_COUNT IS NULL'

ELSE

   PRINT '@OUT_COUNT IS NOT NULL'

OUTPUT: @OUT_COUNT IS NOT NULL

             3. CAPTURE INSERT,DELETE ,UPDATE BY USING OUTPUT PARAMETER

EX:

CREATE TABLE QQ(ID INT,NAME VARCHAR(50),SAL MONEY)

INSERT INTO QQ VALUES(1,'BABJI',10000)
INSERT INTO QQ VALUES(2,'MANJU',5400)
INSERT INTO QQ VALUES(3,'KAMAL',2000)

SELECT * FROM QQ


CREATE TABLE LOG_QQ_INSERT (ID INT,NAME VARCHAR(50),SAL MONEY)
CREATE TABLE LOG_QQ_DELETE (ID INT,NAME VARCHAR(50),SAL MONEY)


SELECT * FROM LOG_QQ_DELETE
SELECT * FROM LOG_QQ_INSERT

CAPTURE INSERT LOG BY USING OUTPUT CLAUSE:
     

INSERT INTO QQ (ID,NAME,SAL)
OUTPUT INSERTED.* INTO LOG_QQ_INSERT VALUES(4,'NAVEEN',3000)

                                        [OR]


 INSERT INTO QQ(ID,NAME,SAL)

OUTPUT INSERTED.ID,INSERTED.NAME,INSERTED.SAL INTO LOG_QQ_INSERT  VALUES(5,'NAVEEN',3000)

-BY USING TABLE VARIBLE WITH OUTPUT PARAMETER

--------DECLARE @QQ_INSERTED TABLE VARIBLE

DECLARE  @QQ_INSERT TABLE(ID INT,NAME VARCHAR(50),SAL MONEY)

--------INSERT NEW ROW INT QQ TABLE

INSERT INTO QQ
OUTPUT INSERTED.* INTO @QQ_INSERT VALUES(6,'GGG',2999)

-------VIEW INSERTED ROW INTO QQ TABLE

SELECT * FROM QQ

-------VIEW THE OUTPUT ROW IN @QQ_INSERTED

SELECT * FROM @QQ_INSERTED


NOTE: WE RUN ABOVE 4 QUERIES AT A TIME

LINK:
https://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/



CAPTURE DELETE RECORDS BY USING OUT PARAMETER:


DELETE FROM QQ 

OUTPUT DELETED.* INTO LOG_QQQ_DELETE WHERE ID=2
                     
                                  [OR]

DELETE FROM QQ 

OUTPUT DELETED.ID,DELETED.NAME,DELETED.SAL INTO LOG_QQQ_DELETE WHERE ID=3


-BY USING TABLE VARIBLE WITH OUTPUT PARAMETER

--------DECLARE @QQ_DELETED TABLE VARIBLE

DECLARE  @QQ_DELETED TABLE(ID INT,NAME VARCHAR(50),SAL MONEY)

--------INSERT NEW ROW INT QQ TABLE

DELETE FROM QQ 

OUTPUT DELETED.* INTO  @QQ_DELETED WHERE ID=2

-------VIEW DELETED ROW INTO QQ TABLE

SELECT * FROM QQ

-------VIEW THE OUTPUT ROW IN @QQ_DELETED

SELECT * FROM @QQ_DELETED


EX:
CREATE TABLE DBO.BOOKS
(
  BOOKID INT NOT NULL PRIMARY KEY,
  BOOKTITLE  NVARCHAR(50) NOT NULL,
  MODIFIEDDATE DATETIME NOT NULL
);
INSERT INTO BOOKS VALUES(1,'GVGHVS','07-17-1990')
INSERT INTO BOOKS VALUES(2,'GDGAS','07-07-1990')
INSERT INTO BOOKS VALUES(3,'BHVH','08-17-1990')


SELECT * FROM BOOKS

DECLARE @DeleteOutput3 table
(
  BookID int,
  BookTitle nvarchar(50),
  ModifiedDate datetime
);

-- delete row in Books table
DELETE Books
OUTPUT
    DELETED.BookID,
    DELETED.BookTitle,
    DELETED.ModifiedDate
  INTO @DeleteOutput3
OUTPUT
    DELETED.BookID,
    DELETED.BookTitle
WHERE BookID = 1;

-- view updated row in Books table
SELECT * FROM Books;

-- view output row in @DeleteOutput3 variable

SELECT * FROM @DeleteOutput3;

CHECK OUTPUT:


CAPTURE UPDATE RECORDS BY USING OUTPUT PARAMETER:


---CAPTURE ONLY DELETE DATA

UPDATE QQ SET NAME='JAIPAL',SAL=6777
OUTPUT DELETED.* INTO LOG_QQ_DELETE WHERE ID=1

----CAPTURE ONLY INSERT DATA

UPDATE QQ SET NAME='JAIPAL',SAL=6777
OUTPUT INSERTED.* INTO LOG_QQ_DELETE WHERE ID=1

---CAPTURE DELETE AND INSERT DATA 

CREATE TABLE QQ_DELETE_INSERT(ID INT,OLD_NAME  VARCHAR(50), NEW_NAME                                                                  VARCHAR(50),OLD_SAL MONEY,NEW_SAL MONEY)

UPDATE QQ SET NAME='JAIPAL',SAL=5000
OUTPUT  INSERTED.ID,
        DELETED.NAME,
         INSERTED.NAME,
         DELETED.SAL,
         INSERTED.SAL   INTO QQ_DELETE_INSERT  WHERE ID=1

CHECK:   SELECT * FROM QQ
                   SELECT * FROM QQ_DELETE_INSERT


CREATE TABLE DBO.BOOKSS
(
  BOOKID INT NOT NULL PRIMARY KEY,
  BOOKTITLE NVARCHAR(50) NOT NULL,
  MODIFIEDDATE DATETIME NOT NULL
);
INSERT INTO BOOKSS VALUES(1,'BABJI','07-17-1990')
INSERT INTO BOOKSS VALUES(2,'MANJU','07-07-1990')
INSERT INTO BOOKSS VALUES(3,'KAMAL','08-17-1990')

SELECT * FROM BOOKSS


-- DECLARE @UPDATEOUTPUT1 TABLE VARIABLE

DECLARE @UPDATEOUTPUT1 TABLE
(
  OLDBOOKID INT,
  NEWBOOKID INT,
  BOOKTITLE NVARCHAR(50),
  OLDMODIFIEDDATE DATETIME,
  NEWMODIFIEDDATE DATETIME
);

-- UPDATE ROW IN BOOKS TABLE

UPDATE BOOKSS
SET
  BOOKID = 105,
  MODIFIEDDATE = GETDATE()
OUTPUT
    DELETED.BOOKID,
    INSERTED.BOOKID,
    INSERTED.BOOKTITLE,
    DELETED.MODIFIEDDATE,
    INSERTED.MODIFIEDDATE
  INTO @UPDATEOUTPUT1
WHERE BOOKTITLE = 'BABJI';

-- VIEW UPDATED ROW IN BOOKS TABLE

SELECT * FROM BOOKSS;

-- VIEW OUTPUT ROW IN @UPDATEOUTPUT1 VARIABLE

SELECT * FROM @UPDATEOUTPUT1;



example:

-- DECLARE @UPDATEOUTPUT1 TABLE VARIABLE

DECLARE @UPDATEOUTPUT2 TABLE
(
  OLDBOOKID INT,
  NEWBOOKID INT,
  BOOKTITLE NVARCHAR(50),
  OLDMODIFIEDDATE DATETIME,
  NEWMODIFIEDDATE DATETIME,
  DIFFINSECONDS INT
);

-- UPDATE ROW IN BOOKS TABLE

UPDATE BOOKSS
SET
  BOOKID = BOOKID + 1,
  MODIFIEDDATE = GETDATE()
OUTPUT
    DELETED.BOOKID,
    INSERTED.BOOKID,
    INSERTED.BOOKTITLE,
    DELETED.MODIFIEDDATE,
    INSERTED.MODIFIEDDATE,
    DATEDIFF(SS, DELETED.MODIFIEDDATE, INSERTED.MODIFIEDDATE)
  INTO @UPDATEOUTPUT2
WHERE BOOKTITLE = 'KAMAL';

-- VIEW UPDATED ROW IN BOOKS TABLE

SELECT * FROM BOOKSS;

-- VIEW OUTPUT ROW IN @UPDATEOUTPUT2 VARIABLE


SELECT * FROM @UPDATEOUTPUT2;

TABLE VALUE PARAMETERS(TABLE TYPE) WITH EXAMPLE

• Table-Valued Parameters is a new feature introduced in SQL SERVER 2008.

•  In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.
• scope is batch.

• Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

CREATE SAMPLE TABLE :

     

CREATE TABLE DEPARTMENT

(

      DEPARTMENTID  INT  PRIMARY KEY,

       DEPARTMENTNAME    VARCHAR(30)

)

GO

          1. CREATE A TABLE TYPE AND DEFINE THE TABLE STRUCTURE:

CREATE TYPE DEPTTYPE AS TABLE

(

        DEPTID INT, 

        DEPTNAME VARCHAR(30)

);

GO

   2.DECLARE A STORED PROCEDURE THAT HAS A PARAMETER OF TABLE TYPE:

CREATE PROCEDURE INSERTDEPARTMENT

 (@INSERTDEPT_TVP DEPTTYPE READONLY)

AS

BEGIN

              INSERT INTO DEPARTMENT(DEPARTMENTID,DEPARTMENTNAME)

              SELECT * FROM @INSERTDEPT_TVP;

END

NOTES:  

•  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
•   You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

 3. DECLARE A TABLE TYPE VARIABLE AND REFERENCE THE TABLE TYPE.

DECLARE @DEPARTMENTTVP AS DEPTTYPE;

4. USING THE INSERT STATEMENT AND OCCUPY THE VARIABLE.

INSERT INTO @DEPARTMENTTVP(DEPTID,DEPTNAME)

VALUES (1,'ACCOUNTS'),

                 (2,'PURCHASE'),

                  (3,'SOFTWARE'),

                   (4,'STORES'),

                  (5,'MAARKETING');

5. WE CAN NOW PASS THE VARIABLE TO THE PROCEDURE AND EXECUTE.

EXEC INSERTDEPARTMENT  @DEPARTMENTTVP

GO

----WE EXEC 3,4,5 STEPS AT ATIME.

check  : select * from department 

ADVANTAGE:

                          Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

EXAMPLES:

BY USING TABLE VALUE PARAMETER INSERT ,DELETE ,UPDATE:


CREATE TABLE TBL_NAME1(ID INT,NAME VARCHAR(10),GENDER BIT,ADDRESS VARCHAR(10))

CREATE TYPE TY_NAME1 AS TABLE (ID INT,NAME VARCHAR(10),GENDER BIT,ADDRESS VARCHAR(10),FLAG  INT)

GO
CREATE PROC USP_NAME1 (@TY  TY_NAME1  READONLY)
AS
BEGIN
      INSERT INTO TBL_NAME1
      SELECT ID,NAME,GENDER,ADDRESS FROM @TY WHERE FLAG=0

  DELETE FROM TBL_NAME1 WHERE ID IN (SELECT ID FROM @TY WHERE FLAG=1)

     UPDATE TBL_NAME1   SET NAME=B.NAME,GENDER=B.GENDER,                                  ADDRESS=B.ADDRESS  FROM TBL_NAME1 A INNER JOIN @TY B   ON A.ID=B.ID
    WHERE FLAG=2

 DECLARE @TY TY_NAME1
 INSERT INTO  @TY  VALUES(1,'CC',0,'BNR',0)
 EXEC USP_NAME1 @TY

 DECLARE @TY TY_NAME1
 INSERT INTO  @TY  VALUES(1,'CC',0,'nnnn',1)
 EXEC USP_NAME1 @TY

 DECLARE @TY TY_NAME1
 INSERT INTO  @TY  VALUES(2,'CCC',0,'BNGR',2)
 EXEC USP_NAME1 @TY

----BASED ON FLAG VALUE INSERT,DELETE,UPDATE WILL PERFORM


ONLY CHECK: SELECT * FROM TBL_NAME1


LIMITATIONS OF TABLE VALUE PARAMETERS:

• YOU CANNOT PASS TABLE-VALUED PARAMETERS TO CLR USER-DEFINED FUNCTIONS.
•   TABLE-VALUED PARAMETERS CAN ONLY BE INDEXED TO SUPPORT UNIQUE OR PRIMARY KEY CONSTRAINTS.SQL SERVER DOES NOT MAINTAIN STATISTICS ON TABLE-VALUED PARAMETERS.
•  TABLE-VALUED PARAMETERS ARE READ-ONLY IN TRANSACT-SQL CODE. YOU CANNOT UPDATE THE COLUMN VALUES IN THE ROWS OF A TABLE-VALUED PARAMETER AND YOU CANNOT INSERT OR DELETE ROWS. TO MODIFY THE DATA THAT IS PASSED TO A STORED PROCEDURE OR PARAMETERIZED STATEMENT IN TABLE-VALUED PARAMETER, YOU MUST INSERT THE DATA INTO A TEMPORARY TABLE OR INTO A TABLE VARIABLE.
•       YOU CANNOT USE ALTER TABLE STATEMENTS TO MODIFY THE DESIGN OF TABLE-VALUED PARAMETERS

       

      

FIND EACH LETTER ASCII VALUE IN A GIVEN STRING

                                                   SIMPLE WAY

DECLARE @STR VARCHAR(MAX)='' 

SET @STR='FHJDGFHJSGDFJVFHHBHJSDF'

 ---PRINT @STR

 DECLARE @LEN INT

 CREATE TABLE #TEMP(ID INT IDENTITY ,STR_POSI VARCHAR(10),ASCII_VALUE INT)

 

 SELECT @LEN=LEN(@STR)

 DECLARE @I INT=0

 WHILE (@I<=@LEN)

 BEGIN

 

 INSERT INTO #TEMP SELECT STR_POSI=SUBSTRING(@STR,@I,1),ASCII_VALUE=ASCII(SUBSTRING(@STR,@I,1))

 

 SET @I=@I+1

 

 END

SELECT * FROM #TEMP

                                      IN STORED PROCEDURE

CREATE PROC USP_ASCII_VALUE

AS

BEGIN

   

      DECLARE @I INT=0

      DECLARE @LEN INT

    CREATE TABLE #TEMP(ID INT IDENTITY ,STR_POSI VARCHAR(10),ASCII_VALUE INT)

 

 SELECT @LEN=LEN(@STR)

 WHILE (@I<=@LEN)

 BEGIN

 

 INSERT INTO #TEMP SELECT   STR_POSI=SUBSTRING(@STR,@I,1),                                                                                                 ASCII_VALUE=ASCII(SUBSTRING(@STR,@I,1))

 

 SET @I=@I+1

 

 END

 SELECT * FROM #TEMP

EXEC  USP_ASCII_VALUE @STR='GVFDYVYbdagrugeurgwelL'

                               BY USING CTE TO FIND ASCII VALUE

DECLARE @STR VARCHAR(8000);

SET @STR = 'QOTHOBNSLFDNOWEAVNIVMNQPWF';

;WITH

TENS      (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 

                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 

                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS T1 CROSS JOIN TENS T2 CROSS JOIN TENS T3),

MILLIONS  (N) AS (SELECT 1 FROM THOUSANDS T1 CROSS JOIN THOUSANDS T2),

TALLY     (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

SELECT N, 

       LETTERASPOSITION = SUBSTRING(@STR, N, 1),

       ASCIIATPOSITION  = ASCII(SUBSTRING(@STR, N, 1))

  FROM TALLY 

 WHERE N <= DATALENGTH(@STR)

 ORDER BY N;

PRINTING ASCII VALUES BY USING STORED PROCEDURES

ASCII VALUES: ASCII  VALUES RANGE FROM   "0 TO 255"

SPECIAL CHARACTERS: RENGE 32 TO 64  LIKE !,

SMALL ALPHABETS: RANGE FROM 96 TO 122

CAPITAL ALPHABETS:RANGE FROM 64 TO 90

CHAR( ):Converts an int ASCII code to a character..   
             
                  SYNTAX:  CHAR(integer-expression)
          
 EX: SELECT   CHAR(9)  ------TAB
         SELECT  CHAR(10) -----LINE FEED
          SELECT   CHAR(13)  ----CARRIAGE RETURN
          SELECT   CHAR(39)-----SINGLE COTATION

--GENRALLY WE R USING THIS CHAR() FUNCTION S IN DYNAMIC SQL  CONCATINATION .

ASCII( ): Returns the ASCII code value of the leftmost character of a character expression.

       SYNTAX:  ASCII(char_expression)

EX:SELECT ASCII(32) ------------51



                                   PRINTING ALL ASCII VALUES FROM 0 TO 255


CREATE PROC USP_PRINT_ALL_ASCII_VALUES
AS
BEGIN
    DECLARE @I INT=1
    DECLARE @N INT=@I+255

WHILE(@I<=@N)
BEGIN
     PRINT  CHAR (@I)
     SET @I=@I+1
END
END

EXEC USP_PRINT_ALL_ASCII_VALUES

--------ASCII VALUES IN SELECT LIST

 CREATE PROC USP_PRINT_ALL_ASCII_VALUES
AS
BEGIN
            DECLARE @I INT=1
             DECLARE @N INT=@I+255
             SELECT @N
WHILE(@I<=@N)
BEGIN
             SELECT   CHAR (@I) AS ASCII_VALUE,ASCII(@I) AS ASCII_CODE-VALUE
             SET @I=@I+1
END
END


                                    PRINTING ASCII SMALL ALPHABETES 


 CREATE PROC USP_PRINT_SMALL_ALPHA_ASCII_VALUES
AS
BEGIN
              DECLARE @I INT=97
              DECLARE @N INT=@I+25

WHILE(@I<=@N)
BEGIN
            PRINT  CHAR (@I)
            SET @I=@I+1
END
END


                                       PRINTING ASCII CAPITAL LETTERS


 CREATE PROC USP_PRINT_CAPITAL_ASCII_VALUES
AS
BEGIN
           DECLARE @I INT=65
           DECLARE @N INT=@I+25

WHILE(@I<=@N)
BEGIN
          PRINT  CHAR (@I)
           SET @I=@I+1
END
END


                                          PRINTING ASCII NUMBERS 0-9


CREATE PROC USP_PRINT_NUM_ASCII_VALUES
AS
BEGIN
           DECLARE @I INT=48
           DECLARE @N INT=@I+9

WHILE(@I<=@N)
BEGIN
           PRINT  CHAR (@I)
           SET @I=@I+1
END
END


                                  PRINT SPECIAL CHAR 'S ASCII VALUES


 CREATE PROC USP_PRINT_ALL_ASCII_VALUES
AS
BEGIN
    DECLARE @I INT=32
    DECLARE @N INT=@I+32

WHILE(@I<=@N)
BEGIN
     PRINT  CHAR (@I)
     SET @I=@I+1
END
END


                     






       

PALINDROME FORM TO GIVEN STRING BY USING STRING FUNCTIONS

DECLARE @STR VARCHAR(50)='BABJI'
DECLARE @LEN INT
DECLARE @A  INT=0
DECLARE @Q VARCHAR(30)=''
SELECT @LEN=LEN(@STR)

WHILE(@A<=@LEN)
BEGIN
          SELECT @Q=@Q+SUBSTRING(@STR,LEN(@STR)-@A,1)
       
         SET @A=@A+1
       
       
      END
         PRINT( @Q)

OUTPUT : IJBAB


BY USING STORED PROCEDURE TO GET PALINDROME FORM:


CREATE PROC USP_PALINDOM(@STR VARCHAR(30))
AS
BEGIN

          DECLARE @LEN INT
          DECLARE @A  INT=0
          DECLARE @Q VARCHAR(30)=''
          SELECT @LEN=LEN(@STR)

WHILE(@A<=@LEN)
BEGIN
           
              SELECT @Q=@Q+SUBSTRING(@STR,LEN(@STR)-@A,1)
       
               SET @A=@A+1
       
END
         PRINT( @Q)

END

EXEC USP_PALINDROME @SRT='BABJI'

OUTPUT: IJBAB

NOTE:  By using string function REVERSE (@STR) to get the palindrom form.

SELECT REVERSE('BABJI')

OUTPUT:  IJBAB

GIVEN STRING IS PLINDROM OR NOT:


CREATE PROC USP_PALINDOM(@STR VARCHAR(30))
AS
BEGIN

          DECLARE @LEN INT
          DECLARE @A  INT=0
          DECLARE @Q VARCHAR(30)=''
          SELECT @LEN=LEN(@STR)

WHILE(@A<=@LEN)
BEGIN
         
              SELECT @Q=@Q+SUBSTRING(@STR,LEN(@STR)-@A,1)
     
               SET @A=@A+1
     
END
         PRINT( @Q)
         
              IF(@Q=@STR)
              BEGIN
                       
                            PRINT 'PALINDROME'
                            PRINT 'THANK YOU'
               END

               ELSE

                         PRINT 'NOT A PALINDROME'
                         PRINT 'THANK YOU'

END

EXEC USP_PALINDROME @SRT='BABJI'

OUTPUT:  IJBAB
                    NOT A PALINDROME
                     THANK YOU

EXEC USP_PALINDROME @SRT='MADAM'

OUTPUT: MADAM
                   PALINDROME
                   THANK YOU



  

GETTING INSERT PROCEDURE TO ANY TABLE IN A DATABASE BY USING DYNAMIC SQL

CREATE  PROCEDURE USP_CREATEINSERTPROC (@TBLNAME VARCHAR(100))
AS
BEGIN
DECLARE @QRY NVARCHAR(MAX)=''

SET @QRY='CREATE PROCEDURE USP_INSERT_'+@TBLNAME+'('
SELECT @QRY=@QRY+'@'+COLUMN_NAME+' '+DATA_TYPE+CASE WHEN DATA_TYPE='VARCHAR' THEN '('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100))+')' ELSE ''END
+',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TBLNAME

SET @QRY=STUFF(@QRY,LEN(@QRY),1,'')+')'

SET @QRY=@QRY+CHAR(13)+'AS'+CHAR(13)+'BEGIN'

SET @QRY=@QRY+CHAR(13)+'INSERT INTO '+@TBLNAME +' VALUES ('
SELECT @QRY=@QRY+'@'+COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TBLNAME

SET @QRY=STUFF(@QRY,LEN(@QRY),1,'')+')'

SET @QRY=@QRY+CHAR(13)+'END'
EXEC( @QRY)
END


EXEC USP_CREATEINSERTPROC 'DEPT'
-------SELECT         TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPT'

DROP PROCEDURE USP_INSERT_EMP

CREATE PROCEDURE USP_INSERT_EMP(@EMPNO NUMERIC,@ENAME VARCHAR(10),@JOB VARCHAR(9),@MGR NUMERIC,@HIREDATE DATETIME,@SAL NUMERIC,@COMM NUMERIC,@DEPTNO NUMERIC)
AS
BEGIN
INSERT INTO EMP VALUES (@EMPNO,@ENAME,@JOB,@MGR,@HIREDATE,@SAL,@COMM,@DEPTNO)
END

SELECT * FROM EMP
EXEC USP_INSERT_EMP 20,'RAM','TST',25,'1980-12-17 00:00:00.000',5000,NULL,50

MATCHED DATA INSERTED INTO ANOTHER TABLE BY USING LOOP

CREATE PROC USP_LOOP

 AS

BEGIN

DECLARE @TBL_A TABLE(ID INT,NAME VARCHAR(10)) 

INSERT INTO @TBL_A VALUES(1,'A'),(2,'B'),(3,'C')

DECLARE @I INT,@X INT=1,@Y INT=1

SELECT @I=MAX(ID) FROM @TBL_A

DECLARE @TBL_B TABLE(ID INT,NAME VARCHAR(10))

INSERT INTO @TBL_B VALUES(1,'A'),(2,'B'),(3,'C')

DECLARE @J INT

SELECT @J=MAX(ID) FROM @TBL_B

DECLARE @TBL_C TABLE(ID INT,NAME VARCHAR(10))

DECLARE @NAME VARCHAR(10), @NAME1 VARCHAR(10)

WHILE(@X<=@I)

BEGIN

SELECT @NAME=NAME FROM @TBL_A WHERE ID=@X 

PRINT @NAME

WHILE(@Y<=@J)

BEGIN

SELECT @NAME1=NAME FROM @TBL_B WHERE ID=@Y 

    IF(@NAME=@NAME1)

BEGIN

INSERT INTO @TBL_C(ID,NAME) SELECT ID,NAME FROM @TBL_A WHERE ID=@X

BREAK

END

SET @Y=@Y+1

END

SET @Y=1

SET @X=@X+1

END

SELECT * FROM @TBL_A

SELECT * FROM @TBL_B

SELECT * FROM @TBL_C

END 

EXEC USP_LOOP

OTHER WAYS:

CREATE TABLE TBL_1(ID INT,NAME VARCHAR(30))

INSERT INTO TBL_1 VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')

SELECT * FROM TBL_1

CREATE TABLE TBL_2(ID INT,NAME VARCHAR(30))

INSERT INTO TBL_2 VALUES(1,'AAA'),(2,'B'),(3,'CC'),(4,'D')

CREATE TABLE TBL_MATCH(ID INT,NAME VARCHAR(30))

-MATCHED DATA BY USING CTE

;WITH X AS(SELECT A.ID,B.NAME FROM TBL_1 A INNER JOIN TBL_2 B ON A.NAME=B.NAME AND A.ID=B.ID )

INSERT INTO TBL_MATCH SELECT * FROM X 

UNMATCHED DATA

SELECT V.ID,V.NAME FROM TBL_1  A INNER JOIN TBL_2  B ON A.NAME<>B.NAME

CREATE TABLE TBL_UNMATCH(ID INT,NAME VARCHAR(30))

MATCHED AND UNMATCHED DATA BY USING PROCEDURE

CREATE PROC USP_MAT

AS 

BEGIN

INSERT INTO TBL_MATCH   SELECT * FROM TBL_1

   INTERSECT

   SELECT * FROM TBL_2

   

   INSERT INTO TBL_UNMATCH   SELECT * FROM TBL_1

   EXCEPT

   SELECT * FROM TBL_2

   

   

   END

   EXEC USP_MAT 

SQL SERVER PROFILER STEP BY STEP

• SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.

•  SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features will be supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined.  

• Trace Capture: Extended Events graphical user interface in SQL Server Management                              Studio
• Trace Replay:Distributed Replay

• We can do the following using SQL Server Profiler:
  • Create a trace
  • Watch the trace results as the trace runs
  • Store the trace results in a table
  • Start, stop, pause, and modify the trace results as necessary
  • Replay the trace results

• Use SQL Server Profiler to monitor only the events in which you are interested.

Benefits of SQL Server Profiler:

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. SQL Server Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem.
• Finding and diagnosing slow-running queries.
• Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
• Monitoring the performance of SQL Server to tune workloads. For information about tuning the physical database design for database workloads, see Database Engine Tuning Advisor.
• Correlating performance counters to diagnose problems.

SQL Server Profiler also supports auditing the actions performed on instances of SQL Server. Audits record security-related actions for later review by a security administrator