1 :普通SQL语句可以用
exec
执行
Select
*
from
tableName
exec
(
'select * from tableName'
)
exec
sp_executesql N
'select * from tableName'
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
declare
@fname
varchar
(20)
set
@fname =
'FiledName'
Select
@fname
from
tableName
exec
(
'select '
+ @fname +
' from tableName'
)
当然将字符串改成变量的形式也可
declare
@fname
varchar
(20)
set
@fname =
'FiledName'
declare
@s
varchar
(1000)
set
@s =
'select '
+ @fname +
' from tableName'
exec
(@s)
exec
sp_executesql @s
declare
@s Nvarchar(1000)
set
@s =
'select '
+ @fname +
' from tableName'
exec
(@s)
exec
sp_executesql @s
3. 输出参数
declare
@num
int
, @sqls nvarchar(4000)
set
@sqls=
'select count(*) from tableName'
exec
(@sqls)
declare
@num
int
, @sqls nvarchar(4000)
set
@sqls=
'select @a=count(*) from tableName '
exec
sp_executesql @sqls,N
'@a int output'
,@num
output
select
@num
1 :普通SQL语句可以用
Exec
执行 例:
Select
*
from
tableName
Exec
(
'select * from tableName'
)
Exec
sp_executesql N
'select * from tableName'
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
错误:
declare
@fname
varchar
(20)
set
@fname =
'FiledName'
Select
@fname
from
tableName
正确:
Exec
(
'select '
+ @fname +
' from tableName'
)
当然将字符串改成变量的形式也可
declare
@fname
varchar
(20)
set
@fname =
'FiledName'
declare
@s
varchar
(1000)
set
@s =
'select '
+ @fname +
' from tableName'
Exec
(@s)
exec
sp_executesql @s
如下:
declare
@s Nvarchar(1000)
set
@fname =
'FiledName'
set
@s =
'select '
+ @fname +
' from tableName'
Exec
(@s)
exec
sp_executesql @s
3. 输入或输出参数
(1)输入参数:
declare
@QueryString nvarchar(1000)
declare
@paramstring nvarchar(200)
declare
@input_id
int
set
@QueryString=
'select * from tablename where id=@id'
set
@paramstring=
'@id int'
set
@input_id =1
exec
sp_executesql @querystring,@paramstring,@id=@input_id
若有多个参数:
declare
@QueryString nvarchar(1000)
declare
@paramstring nvarchar(200)
declare
@input_id
int
declare
@input_name
varchar
(20)
set
@QueryString=
'select * from tablename where id=@id and name=@name'
set
@paramstring=
'@id int,@name varchar(20)'
set
@input_id =1
set
@input_name=
'张三'
exec
sp_executesql @querystring,@paramstring,@id=@input_id,@
name
=@input_name
(2)输出参数
declare
@num
int
, @sqls nvarchar(4000)
set
@sqls=
'select count(*) from tableName'
exec
(@sqls)
declare
@QueryString nvarchar(1000)
declare
@paramstring nvarchar(200)
declare
@output_result
int
set
@QueryString=
'select @totalcount=count(*) from tablename'
set
@paramstring=
'@totalcount int output'
exec
sp_executesql @querystring,@paramstring,@totalcount=@output_result
output
select
@output_result
当然,输入与输出参数可以一起使用,大家可以自己去试一试。
另外,动态语句查询的结果集要输出的话,我只想到以下用临时表的方法,不知各位有没有更好的方法.
IF object_id(
'[tempdb].[dbo].#tmp'
)
IS
NOT
NULL
drop
table
#tmp
select
*
into
#tmp
from
tablename
where
1=2
declare
@QueryString nvarchar(1000)
set
@QueryString=
'select * from tablename '
insert
into
#tmp(field1,field2,...)
exec
(@querystirng)