Normal Parameter Vs Output Parameter in Sql?

Step 1:- Create Temp table

create table #EmployeeTable

(

EmployeeId int identity,

Employee_Name varchar(50)

)

insert into #EmployeeTable

select 'Ram' union all

select 'Shyam' union all

select 'Ghanshyam' union all

select 'Seeta' union all

select 'Radha'

select * from #EmployeeTable

Step 2:- Normal Parameter

declare @Query nvarchar(max)

declare @EName varchar(50)

set @Query = ''

set @Query = 'Select @Name = Employee_Name

from #EmployeeTable where EmployeeId=3'

exec sp_executesql @Query, N'@Name varchar(20)',@EName

select @EName

You can get the result with null.

Step 2:- OutputParameter

declare @Query nvarchar(max)

declare @EName varchar(50)

set @Query = ''

set @Query = 'Select @Name = Employee_Name

from #EmployeeTable where EmployeeId=3'

exec sp_executesql @Query, N'@Name varchar(20) output',@EName output

select @EName

Note:-

sp_executesql used specially

1. When we are using variable in the select query to store the value. ex. @Name

2. it’s more helpful when we are using dynamic query.