--

方法1查询表改为动态

select

*

from

sysobjects

exec

(

'

select ID,Name from sysobjects

'

)

exec

sp_executesql N

'

select ID,Name from sysobjects

'

--

多了一个N为unicode

--

方法2:字段名，表名，数据库名之类作为变量时,用动态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

--

会报错

declare

@s

nvarchar

(

1000

)

--

改为nvarchar

set

@s

=

N

'

select

'

+

@FName

+

'

from sysobjects where

'

+

@FName

+

'

=5

'

exec

sp_executesql

@s

--

成功

--

方法3:输入参数

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

--

方法4:输出参数

declare

@i

int

,

@s

nvarchar

(

1000

)

set

@s

=

'

select @i=count(1) from sysobjects

'

--

用exec

exec

(

'

declare @i int

'

+

@s

+

'

select @i

'

)

--

把整个语句用字符串加起来执行

--

用sp_executesql

exec

sp_executesql

@s

,N

'

@i int output

'

,

@i

output

--

此处输出参数要加上N

select

@i

--

方法5:输入输出

--

用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

=

'

declare @con int select @con=count(1) from sysobjects where ID>

'

+

rtrim

(

@i

)

+

'

select @con

'

exec

(

@s

)