Dynamic statement Syntax: exec \ sp_executesql syntax

-- Dynamic statement syntax
/* **************************************** *
Dynamic statement Syntax: exec \ sp_executesql syntax

Organized by: Roy)

Date: 2008.06.06
**************************************** ***/
Dynamic statement Syntax:

-- Method 1 change the query table to dynamic
Select * From Sysobjects
Exec ( ' Select ID, name from sysobjects ' )
Exec Sp_executesql n ' Select ID, name from sysobjects ' -- N is Unicode.

-- Method 2: use dynamic SQL
Declare @ Fname Varchar ( 20 )
Set @ Fname = ' ID '
Exec ( ' Select ' + @ Fname + ' From sysobjects where ' + @ Fname + ' = 5 ' )

Declare @ S Varchar ( 1000 )
Set @ S = N ' Select ' + @ Fname + ' From sysobjects where ' + @ Fname + ' = 5 '
Exec Sp_executesql @ S -- An error is reported.

Declare @ S Nvarchar ( 1000 ) -- Change to nvarchar
Set @ S = N ' Select ' + @ Fname + ' From sysobjects where ' + @ Fname + ' = 5 '
Exec Sp_executesql @ S -- Successful

-- Method 3: Enter Parameters

Declare @ I Int , @ S Nvarchar ( 1000 )
Set @ I = 5
Exec ( ' Select ID, name from sysobjects where id = ' + @ I )

Set @ S = ' Select ID, name from sysobjects where id = @ I '
Exec Sp_executesql @ S , N ' @ I int ' , @ I -- N is required for the input parameter.

-- Method 4: Output Parameters

Declare @ I Int , @ S Nvarchar ( 1000 )
Set @ S = ' Select @ I = count (1) From sysobjects '

-- Use Exec
Exec ( ' Declare @ I int ' + @ S + ' Select @ I ' ) -- Add the entire statement to the string for execution.

-- Use sp_executesql
Exec Sp_executesql @ S , N ' @ I int output ' , @ I Output -- N is added to the output parameter.
Select @ I

-- Method 5: Input and Output

-- Use sp_executesql
Declare @ I Int , @ Con Int , @ S Nvarchar ( 1000 )
Set @ I = 5
Select @ S = ' Select @ con = count (1) From sysobjects where ID> @ I '
Exec Sp_executesql @ S , N ' @ Con int output, @ I int ' , @ Con Output, @ I
Select @ Con

-- Use Exec
Declare @ I Int , @ S Nvarchar ( 1000 )
Set @ I = 5
Select @ S = ' Declare @ con int select @ con = count (1) From sysobjects where ID> ' + Rtrim ( @ I ) + ' Select @ con '
Exec ( @ S )