Tuesday 30 June 2015

Scope of Temp (#) Table within Stored Procedure

Introduction

I personally do not like the system of nesting Temporary table within Different Stored procedure. But lot of developer prefers it. Here in this article we are going to demonstrate it.

Nested Stored Procedure With Temporary Table
First we understand the structure.



Hope you understand it by the above pictorial diagram. The above diagram is in correct format.

Here I am showing another diagram bellow



The second diagram represents showing the Wrong implementation. So the Temporary table must present in the main calling stored procedure, the first pictorial diagram represent that.

Now make some Practical Work

The correct format:

-- 1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
    CREATE TABLE #T1
         (
           IDNO   INT,
             EMPNAME VARCHAR(50)
         );

      EXEC [dbo].[Procedure2];
      EXEC [dbo].[Procedure3];
END
GO

-- 2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
    INSERT INTO #T1
         (IDNO, EMPNAME)
    VALUES(1, 'Joydeep Das'),(2, 'Deepasree Das');
END
GO

-- 2
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
    SELECT * FROM #T1;
END
GO

-- Executing
EXEC [dbo].[Procedure1];

Output:

IDNO        EMPNAME
----------- --------------------------------------------------
1           Joydeep Das
2           Deepasree Das

Now the Wrong Format:

-- 1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
      EXEC [dbo].[Procedure2];
      EXEC [dbo].[Procedure3];
      EXEC [dbo].[Procedure4];
END
GO

-- 2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
    CREATE TABLE #T1
         (
           IDNO   INT,
             EMPNAME VARCHAR(50)
         );
END
GO

-- 3
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
    INSERT INTO #T1
         (IDNO, EMPNAME)
      VALUES(1, 'Joydeep Das'),(2, 'Deepasree Das');
   
END
GO

-- 4
CREATE PROCEDURE [dbo].[Procedure4]
AS
BEGIN
    SELECT * FROM #T1;
END
GO

-- Executing
EXEC [dbo].[Procedure1];

Output:

Msg 208, Level 16, State 0, Procedure Procedure3, Line 6
Invalid object name '#T1'.
Msg 208, Level 16, State 0, Procedure Procedure4, Line 6
Invalid object name '#T1'.



Hope you like it.




Posted by: MR. JOYDEEP DAS

Sunday 28 June 2015

Is PRIMARY KEY Required NOT NULL

Introduction
As we all know that the PRIMARY key is the combination of UNIQUE and NOT NULL.
We got a funny example that we want to share it with you.

Example
Trying to create a table in this manner

CREATE TABLE temp_Table
   (
      EmpID     INT         PRIMARY KEY,
      EmpName   VARCHAR(50) NOT NULL
   );

Look here with EmpID I am not mentioned any NOT NULL constraint. So the question is the Primary Key is created or NOT?

The answer is YES

But now I am trying to create PRIMARY KEY by using ALTER statement.

CREATE TABLE temp_Table
   (
      EmpID     INT,
      EmpName   VARCHAR(50) NOT NULL
   );

ALTER TABLE temp_Table
ADD CONSTRAINT PK_EmpID  PRIMARY KEY(EmpID);

Here the Question is same. Is the PRIMARY KEY is Created or NOT?
Answer is NO

It gives Error:

Msg 8111, Level 16, State 1, Line 20
Cannot define PRIMARY KEY constraint on nullable column in table 'temp_Table'.
Msg 1750, Level 16, State 0, Line 20
Could not create constraint or index. See previous errors.


So, query in mind is that when we create the first example without NOT NULL the Primary Key is created but in case of Second Example it is not Created Why?

Explanation
When we are trying to create our first Example

CREATE TABLE temp_Table
   (
      EmpID     INT         PRIMARY KEY,
      EmpName   VARCHAR(50) NOT NULL
   );

Primary Key is created when the table is created, so in this case no need of NOT NULL in our syntax.

sp_columns temp_Table

index_name
index_description
index_keys
PK__temp_Tab__AF2DBA794975D443
clustered, unique, primary key located on PRIMARY
EmpID

In our Second Example the Table is created first with NULL definition of the columns EmpID.


CREATE TABLE temp_Table
   (
      EmpID     INT,
      EmpName   VARCHAR(50) NOT NULL
   );


SELECT name, isnullable
FROM   sys.syscolumns WHERE id=OBJECT_ID('temp_Table')

name
isnullable
EmpID
1
EmpName
0


So it is not possible to create Primary Key in columns which have NULL values. This reason the error came.

Msg 8111, Level 16, State 1, Line 20
Cannot define PRIMARY KEY constraint on nullable column in table 'temp_Table'.
Msg 1750, Level 16, State 0, Line 20
Could not create constraint or index. See previous errors.
     



Hope you like it.




Posted by: MR. JOYDEEP DAS



Friday 26 June 2015

Working With Merge and Identity column – A Practical Scenario

Introduction

As we all know about the Identity columns and Merge statement. We are not going to discuss any boring theoretical tropics related to it. Better we are discussing here with a practical scenario of merging records. Hope all of you must enjoy it and it will be informative.

The Scenario
We have a Table with Identity Columns named #tbl_TempStudentRecords. The table details are mentioned bellow.

Column Name
SrlNo
StudentName
StudentClass
StudentSection

We have another table named #tbl_TempStudrntMsrks. The table details are mentioned bellow.

Column Name
SrlNo
StubjectName
MarksObtain

What we want to do is, we have another set of table called tbl_StudentDetails mentioned bellow.

Column Name
StdRoll  (PK)
StudentName
StudentClass
StudentSection

Another table named tbl_StudentMarks

Column Name
IdNo  (PK)
StdRoll  (FK) References  [tbl_StudentDetails].[StdRoll]
SubjectName
MarksObtain

Her we can insert records very easily in tbl_StudentDetails from #tbl_TempStudentRecords very easily. But the main problem is the IDENTITY columns in the Table named [tbl_StudentDetails].[ StdRoll].

When we insert records the Identity columns values generate automatically.
When we are trying to insert records into the table named tbl_StudentMarks from Table named #tbl_TempStudrntMsrks we have to provide the StdRoll values, which is the Foreign Key References to the Table named [tbl_StudentDetails].[ StdRoll].
Think one minute with the case scenario. Hope you can understand the problem. Now we have to solve it and we are not using any LOOP for that and NOT even any DDL operation to change the structure of base table. We are just using the SET BASED operation to make performance high.

How to Solve it

Step – 1 [ Create the Base Table First ]

IF OBJECT_ID('tempdb..#tbl_TempStudentRecords')IS NOT NULL
   BEGIN
       DROP TABLE #tbl_TempStudentRecords;
   END
GO

CREATE TABLE #tbl_TempStudentRecords
   (
    SrlNo         BIGINT           NOT NULL,
    StudentName   VARCHAR(50)      NOT NULL,
    StudentClass  INT              NOT NULL,
    StudentSection CHAR(1)          NOT NULL
   );
GO

IF OBJECT_ID('tempdb..#tbl_TempStudrntMsrks')IS NOT NULL
   BEGIN
       DROP TABLE #tbl_TempStudrntMsrks;
   END
GO

CREATE TABLE #tbl_TempStudrntMsrks
    (
        SrlNo           BIGINT      NOT NULL,
        StubjectName    VARCHAR(50) NOT NULL,
        MarksObtain     INT         NOT NULL
       );
GO

IF OBJECT_ID(N'[dbo].[tbl_StudentDetails]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_StudentDetails];
   END     
GO

CREATE TABLE [dbo].[tbl_StudentDetails]
  (
    StdRoll       BIGINT     NOT NULL IDENTITY(100,1) PRIMARY KEY,
    StudentName   VARCHAR(50) NOT NULL,
    StudentClass  INT        NOT NULL,
    StudentSection CHAR(1)    NOT NULL
  );
GO

IF OBJECT_ID(N'[dbo].[tbl_StudentMarks]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_StudentMarks];
   END     
GO

CREATE TABLE [dbo].[tbl_StudentMarks]
  (
    IdNo          BIGINT      NOT NULL IDENTITY(1,1) PRIMARY KEY,
    StdRoll       BIGINT      NOT NULL, 
    SubjectName   VARCHAR(50) NOT NULL,
    MarksObtain   INT         NOT NULL
  );
GO

ALTER TABLE [dbo].[tbl_StudentMarks]
ADD CONSTRAINT FK_StdRoll_tbl_StudentMarks
FOREIGN KEY(StdRoll)
REFERENCES [dbo].[tbl_StudentDetails](StdRoll);

Step – 2 [ Inserting Records in Temp Table ]

INSERT INTO #tbl_TempStudentRecords
  (SrlNo, StudentName, StudentClass, StudentSection)
VALUES(1, 'Joydeep Das', 1, 'A'),
      (2, 'Preeti Sharma', 1, 'A'),
      (3, 'Deepasree Das', 1, 'A');

INSERT INTO #tbl_TempStudrntMsrks
   (SrlNo, StubjectName, MarksObtain)
VALUES  (1, 'Bengali', 50),
        (1, 'English', 70),
        (1, 'Math', 80),
        (2, 'Bengali', 0),
        (2, 'English', 70),
        (2, 'Math', 80),
        (3, 'Bengali', 20),
        (3, 'English', 90),
        (3, 'Math', 95);


Step – 3 [ Now Solve it By MERGE Statement ]

BEGIN
      DECLARE @MappingTable TABLE
       ([NewRecordID] BIGINT,
        [OldRecordID] BIGINT)

      MERGE [dbo].[tbl_StudentDetails] AS target
      USING (SELECT [SrlNo] AS RecordID_Original
                 ,[StudentName]
                 ,[StudentClass]
                         ,[StudentSection]
               FROM  #tbl_TempStudentRecords
           ) AS source
      ON (target.StdRoll = NULL)
      WHEN NOT MATCHED THEN
      INSERT ([StudentName], [StudentClass], [StudentSection])
      VALUES (source.[StudentName],source.[StudentClass],  
              source.[StudentSection])
      OUTPUT inserted.[StdRoll], source.[RecordID_Original]
      INTO   @MappingTable;

      --- Now Map table is ready and we can use it ---
      INSERT INTO [dbo].[tbl_StudentMarks]
          (StdRoll, SubjectName, MarksObtain)
      SELECT b.NewRecordID, a.StubjectName, a.MarksObtain
      FROM   #tbl_TempStudrntMsrks AS a
             INNER JOIN @MappingTable AS b
                  ON a.SrlNo = b.OldRecordID;
END
GO

Step – 2 [ Observation ]

SELECT * FROM [dbo].[tbl_StudentDetails];
GO

SELECT * FROM [dbo].[tbl_StudentMarks];
GO

StdRoll                 StudentName                   StudentClass     StudentSection
100                         Joydeep Das                      1                              A
101                         Preeti Sharma                    1                              A
102                         Deepasree Das                   1                              A


IdNo      StdRoll                 SubjectName                         MarksObtain
1              100                         Bengali                                 50
2              100                         English                                  70
3              100                         Math                                      80
4              101                         Bengali                                  0
5              101                         English                                  70
6              101                         Math                                      80
7              102                         Bengali                                  20
8              102                         English                                  90
9              102                         Math                                      95





Hope you like it.







Posted by: MR. Joydeep Das