NOCOUNT In SQL Server

This Statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the store procedure or directly SQL statement.You Can view this message in the management studio in the message tab of the result panel.

When it is ON-    The number of affected rows will not be returned
When it is OFF-- The number of affected row will be returned

@@ROWCOUNT- is used to get a number of rows affected. either SET NOCOUNT ON or OFF  
@@ROWCOUNT-- Is alway updated with the number of rows affected


Example

Create Table for performing NOCOUNT Statement

create table tbl_NocuntDemo
  (
  number int
  )

When NOCOUNT is OFF

SET NOCOUNT OFF
insert into tbl_NocuntDemo values (1),(2),(3),(4)

Output

It return number of rows affected in table in message tab pane

When NOCUNT is ON

SET NOCOUNT ON
insert into tbl_NocuntDemo values (5),(6),(7),(8)

Output

it does not return row affected in a table but write "Command(s) completed successfully" in message tab pane

so Microsoft think about this error and add a @@ROWCOUNT statement
@@ROWCOUNT is returned number of row affected in table either NOCOUNT ON or OFF

Use @@ROWCOUNT when NOCOUNT is ON

SET NOCOUNT ON
insert into tbl_NocuntDemo values (9),(10),(11),(12)
select @@ROWCOUNT as NoOFRowAffected

Output 
It return @@ROWCOUNT as NoOFRowAffected number of row affected in message
NoOFRowAffected  = 4

Use @@ROWCOUNT when NOCOUNT is OFF

SET NOCOUNT OFF
insert into tbl_NocuntDemo values (13),(14),(15),(16)
select @@ROWCOUNT as NoOFRowAffected

Output 

It return @@ROWCOUNT as NoOFRowAffected number of row affected in message
NoOFRowAffected  = 4


select * from tbl_NocuntDemo

Trigger

Trigger Is One Kind Of StoreProcedure that automatically Execute when DML or DDL Statement Associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements.   
*1 After Trigger (For Trigger)

This Trigger fire After SQL server completes the execution of the action successfully that fired it.

Example: - If you insert/Update/Delete record on table trigger associated that command and then change the respective table but when insert/Update/Delete fail After Trigger is not Fired

After Insert Trigger
After Update Trigger
After Delete Trigger

**2 Instead Of Trigger

This Trigger Fire Before SQL Server completes the execution of the action.Example: - If you insert/Update/Delete record on table trigger associated that command and then change the respective table but when insert/Update/Delete fail Instead Trigger is Fired.

Instead, Insert Trigger
Instead, Update Trigger
Instead, Delete Trigger

---First Create Table tblEmployee2
create table tblEmployee2
(
Employee_Id int,
Employee_Name nvarchar(50),
Employee_Salary decimal(10,2)
)

---Insert Record Into Table tblEmployee2
Insert into tblEmployee2 values (1,'Ben',2000)
Insert into tblEmployee2 values (2,'Clark',1200)
Insert into tblEmployee2 values (3,'John',3500)
Insert into tblEmployee2 values (4,'Sam',4000)
Insert into tblEmployee2 values (5,'Ponting',5000)

Now Create Table tblEmployee2_Audit for logging/Backup purpose of Table tblEmployee2 
------Create Table tblEmployee2_Audit

create table tblEmployee2_Audit
(
Employee_Id int,
Employee_Name nvarchar(50),
Employee_Salary decimal(10,2),
Audit_Action nvarchar(50),
Audit_Timestamp datetime
)

After Insert Trigger
---create trigger on tblEmployee2 for Insert statement

create trigger trgAfterInsert on tblEmployee2
for insert
as 
declare @Employee_Id int,
@Employee_Name nvarchar(50),
@Employee_Salary decimal(10,2),
@Audit_Action varchar(100);

select @Employee_Id= i.Employee_Id from inserted i;
select @Employee_Name= i.Employee_Name from inserted i;
select @Employee_Salary= i.Employee_Salary from inserted i;

set @Audit_Action = 'Inserted Record, After Trigger';

insert into tblEmployee2_Audit(Employee_Id,Employee_Name,Employee_Salary,Audit_Action,Audit_Timestamp)
values (@Employee_Id,@Employee_Name,@Employee_Salary,@Audit_Action,GETDATE());

Print 'After Trigger Is Fired ';


Now Try To Insert Data in tblEMployee2 Table

insert into tblEMployee2(Employee_Id, Employee_Name, Employee_Salary)  values (6,'Pam',5200)


---------------**********----------------

---After Update Trigger------

--Create trigger on tblEMployee2 for update statement
Create trigger trgAfterUpdate on tblEMployee2
for update
as
declare @Employee_Id int,
@Employee_Name nvarchar(50),
@Employee_Salary decimal(10,2),
@Audit_Action varchar(100);

select @Employee_Id= i.Employee_Id from inserted i;
select @Employee_Name= i.Employee_Name from inserted i;
select @Employee_Salary= i.Employee_Salary from inserted i;
if update(Employee_Name)
         set @Audit_Action = 'Update Record---After update trigger';
else
        set @Audit_Action = 'Update Record --After update trigger';

Insert into tblEmployee2_Audit(Employee_Id,Employee_Name,Employee_Salary,Audit_Action,Audit_Timestamp)
values (@Employee_Id,@Employee_Name,@Employee_Salary,@Audit_Action,GETDATE());

Print 'After Update Trigger Fired';

Now try to update data in tblEMployee2 table
update tblEMployee2 set Employee_Name='Paul' where Employee_Id = 2

-------******************-------------------

select * from tblEmployee2
select * from tblEmployee2_Audit

Functions In SQL Server*

We cover user defined function in this session

User Defined Function
--------*1 Scalar-Valued Function
-----------**2 Inline Table Valued Function
-----------------***3 Multi-Line Valued Function


Function Calling Syntax
--------Select OwnerName.FunctionName()-----1st type
--------Select DatabaseName.OwnerName.FunctionName()-----2nd type

Here OwnerName is Database Owner


Create Table For Perform Function On it.
create table tblEmployee1
(
Id int,
Name nvarchar(50),
Salary int 
)

Insert Value in Table tblEmployee1

insert into tblEmployee1 values (1,'John',4000)
insert into tblEmployee1 values (2,'Sara',4500)
insert into tblEmployee1 values (3,'Clark',3000)
insert into tblEmployee1 values (4,'Hyden',5000)
insert into tblEmployee1 values (5,'Ponting',8000)


-----*1 Scalar-Valued Function------------
Scalar-Valued Function Return Single Column Value only or return Single Value

Creating Scalar function for GetFullname of employee
Create function FnScalar_GetEmployeeName
(
@firstName varchar(50)
)
returns varchar(50)
as
begin
return  (select @firstName)
end

Calling Scaler Function Function
select dbo.FnScalar_GetEmployeeName(Name) from tblEmployee1


---------*******************-------------

----**2 Inline Valued Function-------
User Define inline Valued Function Return Table Variable As a result of action

Create Inline valued Function
create function FnInline_GetEmployeeName()
RETURNS TABLE
AS
RETURN (SELECT * FROM tblEmployee1)

Calling Inline Function

SELECT * FROM FnInline_GetEmployeeName()

---------*******************-------------

---***3 MultiLine Valued Function------

Create MultiLine Valued Function

Create Function For Id, orginalName, And Salary of tblEmpoyee1
Create Function FnMultiline_GetEmoloyeeDetail()
returns @Employee Table
(
Id int,
Name varchar(50),
Salary int
)
as
Begin
    Insert into @Employee Select e.Id,e.Name,e.Salary from tblEmployee1 e;
-----Now Update Salary of Clark
Update @Employee set Salary = 5000 Where Id =1;
-----It Will Update @Employee Table Runtime only Orginal Database Table
return
End

Calling  MultiLine Valued Function

Select * from FnMultiline_GetEmoloyeeDetail()

---------*******************-------------

Different Between Store Procedure and Function
1 Function Used with Select and Where clause, but StoreProdcedure  Not
Like
        Select dbo.FnScalar_GetEmployeeName(Name) as Employee Name From tblEmployee1

2 Try-Catch blog Is not in function, but in Store Procedure have

3 Function only input Parameter but in StoreProcedure both input/output parameter

4 Function Can not call StoreProdcedure, but in StoreProdcedure we call Function

5 Function Must Return Value, but StoreProdcedure is optional