-- Create a test environment
If object_id ('tb', 'U') is not null drop table TB
Go
Create Table TB
(
Id int identity,
Code varchar (10 ),
Name varchar (20 ),
Constraint pk_tb primary key (ID)
)
Go
Insert TB
Select '001', 'Finance author' Union all
Select '002 ', 'department of trade' Union all
Select '003 ', 'technical Department' Union all
Select '004 ', 'marketing Department'
Go
-- Obtain the result set in dynamic statement 1.
Declare @ SQL nvarchar (4000)
Declare @ wheresql varchar (1000)
Set @ wheresql = 'and name = ''Technology Department '''
Set @ SQL = 'select * from TB where 1 = 1' + @ wheresql
Exec (@ SQL)
-- Result
/*
ID code name
-----------------------------------------
3 003 Technology Department
*/
-- Dynamic Statement 2 Input and Output Parameters
Declare @ ID int
Set @ SQL = 'select @ ID = ID from TB where name = @ name'
Exec sp_executesql @ SQL, n' @ ID int output, @ name varchar (20) ', @ ID output, 'technical Department'
Select @ ID
-- Result
/*
-----------
3
*/