EXEC & EXEC sp_executesql

EXEC and EXEC sp_executesql for Searching..

DECLARE @sql as nvarchar(100)
DECLARE @paraDOB datetime
SET @paraDOB = '1/1/1981'
SET @sql=N''
SET @sql=N'SELECT * FROM EmpMast'
SET @sql=@sql + ' WHERE DOB >= @paraDOB'
exec sp_executesql @sql,N'@paraDOB datetime',@paraDOB

If our str is over 4000, we can’t use sp_executesql.

DECLARE @sql as varchar(100)
DECLARE @paraDOB datetime
SET @paraDOB = '1/1/1981'
SET @sql=''
SET @sql='SELECT * FROM EmpMast'
SET @sql=@sql + ' WHERE DOB >= ''' + CONVERT(NVARCHAR,@paraDOB) + ''''
EXEC (@sql) --( ) is important.

When we use sp_executesql, we can pass hidden parameter. sp_executesql’s performance is better than EXEC.

[ some people pass all parameter if they use sp_executesql. Actually, we don’t need to pass all parameter ]