Dynamic SQL statement 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 '-- an additional N is Unicode

-- Method 2: when the field name, table name, and database name are used as variables, dynamic SQL is used.
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: input 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 added to the input parameter here.

-- Method 4: Output Parameters

Declare @ I int, @ s nvarchar (1000)
Set @ s = 'select @ I = count (1) From sysobjects'

-- Exec
Exec ('Clare @ I int' + @ s + 'select @ I ') -- adds the entire statement to the string for execution.

-- Use sp_executesql
Exec sp_executesql @ s, n' @ I int output', @ I output -- N must be added to the output parameter here.
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

-- Exec
Declare @ I int, @ s nvarchar (1000)
Set @ I = 5
Select @ s = 'Clare @ con int select @ con = count (1) From sysobjects where ID> '+ rtrim (@ I) + 'select @ con'
Exec (@ s)

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/04/24/4106390.aspx