Question and Answers in Sql Server - Part 4

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.

16 What are types of stored procedures present in sqlserver and how to create it ?

      Pre defined and user defined , Pre defined means already present in SQL SERVER. User defined means user creating the stored procedure.

Pre Defined Stored Procedures :

  

sp_rename             -- use to rename a object

sp_stored_procedures  -- use to find the stored procedure objects

sp_tables             -- use to find the tables

sp_depends            -- use to find the dependency of a object

sp_helptext           -- use to get the text of a compiled object

sp_addlinkedserver    -- use to add the linked server

sp_addlinkedsrvlogin  -- use to add the login for linked server

sp_who                -- use to find the object running in database and there id

   

User  Defined : 

Click here to read the User defined stored procedure

To exec the SP : Sometimes we dont need to specify the all input parameter just alone execute the stored procedure because it takes default value.

    EXEC sys.sp_tables @table_name = N'', -- nvarchar(384)

        @table_owner = N'', -- nvarchar(384)

        @table_qualifier = NULL, -- sysname

        @table_type = '', -- varchar(100)

        @fUsePattern = NULL -- bit

   

    EXEC sys.sp_tables

    EXEC sys.sp_addlinkedserver @server = 'lnksrv', -- sysname

        @srvproduct = N'', -- nvarchar(128)

        @provider = N'SQLNCLI', -- nvarchar(128)

        @datasrc = N'sqlexpress', -- nvarchar(4000)

        @location = N'', -- nvarchar(4000)

        @provstr = N'', -- nvarchar(4000)

        @catalog = NULL -- sysname

   

    EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = 'lnksrv', -- sysname

        @useself = '', -- varchar(8)

        @locallogin = NULL, -- sysname

        @rmtuser = 'raj', -- sysname

        @rmtpassword = 'pwd' -- sysname

   

17.  What are types of Function present in sqlserver ?

          There are two category first  System- defined and user defined. In that we have three sub categories based on the return type. 

1. Scalar value function
2. Table value function - Inline Table value function , Multi statement Table value function
3. Aggregate function

         

System defined function:

let we see some of the predefined functions 

   -- check value constraint

   SELECT COALESCE(NULL,NULL,2)  -- return the first non null value from the expression if any empty value present at first returns 0

   SELECT ISNULL('raj','rajesh')

   SELECT NULLIF(0,2)

  

   -- Convert function

   SELECT CONVERT(VARCHAR(10),GETDATE(),126)

   SELECT CAST('1' AS INT)

  

   -- string functions

   SELECT REPLACE('ramesh','m','j')

   SELECT SUBSTRING('rajesh',1,3)

   SELECT LEN('rajesh')

   SELECT UPPER('raj')

   SELECT LOWER('jak')  

  

   --trim function

   SELECT LTRIM(' rajesh')

   SELECT RTRIM('rajesh ')

  -- Error function

  SELECT ERROR_LINE() 

  SELECT ERROR_MESSAGE()

  SELECT ERROR_NUMBER()

  SELECT ERROR_PROCEDURE()

  SELECT ERROR_SEVERITY()

  SELECT ERROR_STATE()

   -- Date time function

   SELECT GETDATE()

   SELECT DATEADD(dd,2,GETDATE())

   SELECT DATEDIFF(dd,GETDATE(),GETDATE()+5)

   SELECT DATENAME(mm,GETDATE())

   SELECT DATEPART(mm,GETDATE())

   SELECT DAY(GETDATE())

  

   -- Aggregate functions

   SELECT COUNT(1) FROM employee

   SELECT MAX(salary)FROM employee

   SELECT MIN(salary)FROM employee

   SELECT AVG(salary)FROM employee

   

User defined Function :

Click here to read the implementation of function

  

18. What is a Trigger ?

Click here to read about trigger.