Can Views take the Input Parameters

One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views. 

The answer is NO. It is not possible at any version of the SQL server as the view is not build for that purpose.

But we have others ways to do that, and we can do it very easily by table value functions.  Here in this article I am trying to demonstrate it easily by taking an example.

-- My base table

CREATE TABLE my_Emplyee

      (ID         INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,

       EMP_NAME   VARCHAR(50)  NULL,

       SAL        DECIMAL(20,2)NOT NULL)

      

-- Inserting so records on it

INSERT INTO  my_Emplyee

             (EMP_NAME,  SAL)

VALUES('Sukamal jana', 40000),

        ('Manisankar Year', 30000),

        ('Tuhin Shina', 40000),    

        ('Sabgram jit', 30000),  

        ('Subrata Kar', 20000),   

        ('Debojit Ganguli', 20000) 

       

-- Display records

SELECT * FROM my_Emplyee

Output

ID    EMP_NAME          SAL

1     Sukamal jana      40000.00

2     Manisankar Year   30000.00

3     Tuhin Shina       40000.00

4     Sabgram jit       30000.00

5     Subrata Kar       20000.00

6     Debojit Ganguli   20000.00

I am again mentioned that the parameters cannot be passed in views.

Now I am moving to the solution of that by table value function and passing some parameters to get the desired result set.

IF OBJECT_ID (N'fn_EMP_VIEW') IS NOT NULL

   DROP FUNCTION dbo.fn_EMP_VIEW

GO

CREATE FUNCTION dbo.fn_EMP_VIEW

                (@p_Sal DECIMAL(20,2))

RETURNS TABLE

AS RETURN

(

      SELECT *

      FROM   my_Emplyee

      WHERE  SAL>=@p_Sal

)

GO            

             

We want to display the employee details that have salary 40,000 or more than that.

-- Execute it

SELECT *

FROM   dbo.fn_EMP_VIEW(40000)

Output

ID    EMP_NAME          SAL

1     Sukamal jana      40000.00

3     Tuhin Shina       40000.00

Hope you like it.

Posted by: MR. JOYDEEP DAS

  

IDENTITY Columns Violation

We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble.

First take a quick look about the identity columns and how it works. Then we are going to discuss about the problem and how to solve it programmatically.

Definition of Identity Columns

An IDENTITY column contains a value for each row, generated automatically by Adaptive Server that uniquely identifies the row within the table.

Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement. IDENTITY columns cannot be updated and do not allow nulls.

You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:

CREATE TABLE  table_name

             (column_name  INT NOT NULL IDENTITY(1,1))

The Problem comes for Identity

One of my friends told me that, he has a problem for IDENTITY columns. He told me that when he make the INSERT entry into table objects, he never check the uniqueness of the records for that the primary key violation error occurs. He just shows the error.

The problem is the table objects contain identity columns. The value of the identity columns increases each time, even the PK violation exists.

The problem is mentioned by T-SQL statements

Step-1 [ Create the Base Table with IDENTITY columns ]

-- Table defination

DROP TABLE tbl_Example

CREATE TABLE tbl_Example

    (

       ROLL     INT          NOT NULL PRIMARY KEY,

       SNAME    VARCHAR(50)  NULL,

       SCLASS   INT          NULL,

       ROWNUM   INT          NOT NULL IDENTITY(1,1)

    ) 

Step-2 [ Normal Insertions of Values ]

INSERT INTO tbl_Example

            (ROLL,          

                   SNAME,   

                   SCLASS)

VALUES (1, 'JOYDEEP', 1)                     

INSERT INTO tbl_Example

            (ROLL,          

             SNAME,   

             SCLASS)

VALUES (2, 'SUKAMAL', 1)

Step-3 [ See the Output ]

SELECT * FROM tbl_Example

-- Output

ROLL  SNAME       SCLASS      ROWNUM

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

1     JOYDEEP     1           1

2     SUKAMAL     1           2

Step-4 [ Make an Invalid entry that generate an error ]

-- Violation of Primary Key

INSERT INTO tbl_Example

            (ROLL,          

             SNAME,   

             SCLASS)

VALUES (2, 'RAJESH', 1)

-- Output

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__tbl_Exam__44C28DB623BDA346'.

Cannot insert duplicate key in object 'dbo.tbl_Example'.

The statement has been terminated.

Step-5 [ Now Make the Correction entry after correction of data ]

-- Correction of Entry

INSERT INTO tbl_Example

            (ROLL,          

             SNAME,   

             SCLASS)

VALUES (3, 'RAJESH', 1)

Step-6 [ Look the INDENTITY columns has Increased it gives 4 instead of 3 ]

SELECT * FROM tbl_Example

--Output

ROLL        SNAME       SCLASS      ROWNUM

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

1           JOYDEEP     1           1

2           SUKAMAL     1           2

3           RAJESH      1           4

Note that: It is the Normal behavior of the IDENTITY columns. It maintains the uniqueness. Here we are going to break it depends on our needs and it is not good for development. However I am just demonstrating it that we can do it if needed.

Here I am going to make a stored procedure to demonstrate it.

CREATE PROCEDURE my_proc

    (

       @param_roll   INT,

       @param_name   VARCHAR(50),

       @param_calss  INT

    )

AS

   DECLARE @v_NOREC INT

   BEGIN

          BEGIN TRY

            BEGIN TRANSACTION

                  INSERT INTO tbl_Example

                             (ROLL,    

                                            SNAME,   

                                            SCLASS)

                          VALUES (@param_roll, 

                                  @param_name,

                                  @param_calss)           

            COMMIT TRANSACTION

          END TRY

          BEGIN CATCH

            ROLLBACK TRANSACTION

            PRINT 'ERROR EXISTS'

            SELECT @v_NOREC=COUNT(*) FROM tbl_Example

            DBCC CHECKIDENT (tbl_Example, reseed, @v_NOREC)

          END CATCH

   END   

Now we execute it and see the result step by step.

   -- Execution-1

   EXECUTE my_proc

           @param_roll   = 1,

           @param_name   = 'JOYDEEP',

           @param_calss  = 1

          

   SELECT * FROM tbl_Example

 

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   -- Execution-2

   EXECUTE my_proc

           @param_roll   = 2,

           @param_name   = 'SUKAMAL',

           @param_calss  = 1

          

  

   SELECT * FROM tbl_Example

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   2        SUKAMAL     1           2

   -- Execution-3 [ Primary Key Error ]

   EXECUTE my_proc

           @param_roll   = 2,

           @param_name   = 'SANGRAM',

           @param_calss  = 1

   0 row(s) affected)

    ERROR EXISTS

    Checking identity information: current identity value '3', current column value '2'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

          

   SELECT * FROM tbl_Example

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   2        SUKAMAL     1           2

      -- Execution-4       

   EXECUTE my_proc

           @param_roll   = 3,

           @param_name   = 'SANGRAM',

           @param_calss  = 1   

          

   SELECT * FROM tbl_Example

   ROLL     SNAME       SCLASS      ROWNUM

   1        JOYDEEP     1           1

   2        SUKAMAL     1           2

   3        SANGRAM     1           3  

    

Hope you like it.

Posted by: MR. JOYDEEP DAS

CROSS APPLY on SQL 2005

A new feature of Microsoft SQL Server 2005 is "CROSS  APPLY". It restricted "INNER JOIN" between a table (outer query) and a table-valued function (common usage), or derived table from correlated subquery. The table-valued function is evaluated only for the parameter values supplied by the outer query.

The result of the CROSS APPLY quay can be achieved by using temporary table, CTE or Table Variable. But CROSS APPLY provides a very powerful solution in a single query.

Let's takes an example, which can help us to build the concepts of CROSS APPLY.  Our motto is to supply the result of outer query as a parameters value of table value function.

[ Outer Query]  à result of outer query à [ Parameters value of Table valued Function]

Step-1 [ Create the Table Objects ]

CREATE TABLE People_details

(

   PersonID   int         NOT NULL,

   MotherID   int         NULL,

   FatherID   int         NULL,

   Name       varchar(50) NOT NULL,

   CONSTRAINT PK_People PRIMARY KEY(PersonID),

)

GO

Step-2 [ Insert Values Into Table Objects ]

INSERT INTO People_details

VALUES(1  , NULL, NULL, 'Rajesh Das')

INSERT INTO People_details

VALUES(2  , NULL, NULL, 'Raja Barma')

INSERT INTO People_details

VALUES(3  , NULL, NULL, 'Anand Kimmel')

INSERT INTO People_details

VALUES(4  , NULL, NULL, 'Sajan Kimmel')

INSERT INTO People_details

VALUES(5  , 2, 1,       'Joga Benavides')

INSERT INTO People_details

VALUES(6  , 3, 4,       'Giban Kimmel')

INSERT INTO People_details

VALUES(7  , 5, 6,       'Kalyan Hemenway')

INSERT INTO People_details

VALUES(8  , 5, 6,       'Dinesh Kimmel')

INSERT INTO People_details

VALUES(9  , 5, 6,       'Deb Kimmel')

INSERT INTO People_details

VALUES(10 , 5, 7,       'Rohit Benavides')

INSERT INTO People_details

VALUES(11 , 5, 7,       'Nitin Benavides')

INSERT INTO People_details

VALUES(12 , 5, 6,       'Jana Kimmel')

INSERT INTO People_details

VALUES(13 , 5, 6,       'Palu Kimmel')

INSERT INTO People_details

VALUES(14 , NULL, NULL, 'Logar Kimmel')

INSERT INTO People_details

VALUES(15 , NULL, NULL, 'David Benavides')

INSERT INTO People_details

VALUES(16 , 14, 13,     'Alex Kimmel')

INSERT INTO People_details

VALUES(17 , 14, 13,     'Noah Kimmel')

SELECT * FROM People_details

Step-3 [ The T-SQL Statements that I used in Function ]

SELECT p1.Name as MyName,

       p2.Name AS Mother,

       p3.Name As Father

FROM   People_details p1

       LEFT JOIN People_details p2 ON p1.MotherID = p2.PersonID

       LEFT JOIN People_details p3 ON p1.FatherID = p3.PersonID

Step-4 [ Create the Function ]

CREATE FUNCTION [fnGetParents](@PersonID int)

RETURNS @Parents TABLE

(

   [PersonID] [int] PRIMARY KEY NOT NULL,

   [Self]     [varchar](25),

   [Mother]   [varchar](25) NULL,

   [Father]   [varchar](25) NULL

)

AS

BEGIN

   INSERT INTO @Parents

   SELECT

      p1.PersonID,

      p1.Name AS [Self],

      p2.[Name] AS Mother,

      p3.[Name] AS Father

   FROM

      People_details p1

      INNER JOIN People_details p2 ON p1.MotherID = p2.PersonID

      INNER JOIN People_details p3 ON p1.FatherID = p3.PersonID

   WHERE

      p1.PersonID = @PersonID;

   RETURN;

END;

Step-5 [ This Query give you an error that's why we have to use CROSS APPLY ]

SELECT p1.PersonID, p1.Name, dbo.fnGetParents(p1.PersonID)

FROM People_details p1

Step-6 [ The CROSS APPLY ]

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father

FROM People_details p1

CROSS APPLY fnGetParents(p1.PersonID)p2

Analyzing the Step-6 that is the CROSS APPLY

[ Outer Query]  à result of outer query à [ Parameters value of Table valued Function]

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father

FROM People_details p1

CROSS APPLY fnGetParents(p1.PersonID)p2

Lets brake down the query into parts to understand it properly as above definition of CROSS APPLY says.

Here the Outer Query is

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father

FROM People_details p1

The Result of the Outer Query is

p1.PersonID

Parameters value of the Table valued Function is

fnGetParents(p1.PersonID)p2

I hope you now you understand it and thanking you to provide your valuable time on it.

Posted by: MR. JOYDEEP DAS