动态sql语句基本语法
exec sp_executesql要求SQL语句是nvarchar型,execute则无此要求,所以前面没有错
select * from dbo.btype
begin
declare @strWhere nvarchar(100)
set @strWhere='typeId>00002'
declare @tblName varchar(100)
set @tblName='btype'
declare @strSQL nvarchar(100)
declare @RowCount int
declare @RowSum int
set @strSQL = ' from [' + @tblName + '] where '+@strWhere
set @strSQL = N'select @RowCount=count(*),@RowSum=sum(Convert(int,typeId)) ' + @strSQL
EXEC sp_executesql @strSQL,N'@RowCount int OUTPUT,@RowSum int output',@RowCount OUTPUT,@RowSum output
select @RowCount,@RowSum
end
ALTER procedure P_BizDraftInfo_PageData
(
@TableNames VARCHAR(200), --表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR(200)='*', --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT=15, --每页记录数
@CurrentPage INT=0, --当前页,0表示第1页
@Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by
@Order VARCHAR(200) = '', --排序,可以为空,为空默认按主键升序排列,不用填 order by
@RowCount int = 0 output, --返回查询记录总数
@BillName varchar(20),
@EntID varchar(50)
)
AS
declare @Filter varchar(200)
if(@BillName<>'All')
set @Filter=' EntID='''+@EntID +'''AND BillType='''+@BillName+''''
else
set @Filter=' EntID='''+@EntID+''''
execute P_GetPaginationData2 @TableNames,'',@PrimaryKey,@Fields,@PageSize,@CurrentPage,@Filter,@Group,@Order, @RowCount output
RETURN
begin
declare @RowCount int --返回查询记录总数
declare @zhje varchar(100)
declare @se varchar(100)
declare @jshj varchar(100)
declare @SqlWhere varchar(100)
declare @EntID varchar(100)
set @EntID='E001'
set @SqlWhere=' EntID='''+@EntID+''''
declare @sSql nvarchar(300)
set @sSql='from V_GoodsInAccount where'+ @SqlWhere
set @sSql=N'select @zhje=sum(Qty),@se=sum(Qty),@jshj=sum(Qty),@RowCount=count(*)'+@sSql
EXEC sp_executesql @sSql,N'@zhje varchar(100) output,@se varchar(100) output,@jshj varchar(100) output,@RowCount int OUTPUT',
@zhje output,@se output,@jshj output,@RowCount OUTPUT
select @zhje,@se,@jshj,@RowCount
end
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
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) -- 注意此处改为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)
--如何将exec执行结果放入变量中?
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
select * from dbo.btype
begin
declare @strWhere nvarchar(100)
set @strWhere='typeId>00002'
declare @tblName varchar(100)
set @tblName='btype'
declare @strSQL nvarchar(100)
declare @RowCount int
declare @RowSum int
set @strSQL = ' from [' + @tblName + '] where '+@strWhere
set @strSQL = N'select @RowCount=count(*),@RowSum=sum(Convert(int,typeId)) ' + @strSQL
EXEC sp_executesql @strSQL,N'@RowCount int OUTPUT,@RowSum int output',@RowCount OUTPUT,@RowSum output
select @RowCount,@RowSum
end
ALTER procedure P_BizDraftInfo_PageData
(
@TableNames VARCHAR(200), --表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR(200)='*', --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT=15, --每页记录数
@CurrentPage INT=0, --当前页,0表示第1页
@Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by
@Order VARCHAR(200) = '', --排序,可以为空,为空默认按主键升序排列,不用填 order by
@RowCount int = 0 output, --返回查询记录总数
@BillName varchar(20),
@EntID varchar(50)
)
AS
declare @Filter varchar(200)
if(@BillName<>'All')
set @Filter=' EntID='''+@EntID
else
set @Filter=' EntID='''+@EntID+''''
execute P_GetPaginationData2 @TableNames,'',@PrimaryKey,@Fields,@PageSize,@CurrentPage,@Filter,@Group,@Order, @RowCount output
RETURN
begin
declare @RowCount int --返回查询记录总数
declare @zhje varchar(100)
declare @se varchar(100)
declare @jshj varchar(100)
declare @SqlWhere varchar(100)
declare @EntID varchar(100)
set @EntID='E001'
set @SqlWhere=' EntID='''+@EntID+''''
declare @sSql nvarchar(300)
set @sSql='from V_GoodsInAccount where'+ @SqlWhere
set @sSql=N'select @zhje=sum(Qty),@se=sum(Qty),@jshj=sum(Qty),@RowCount=count(*)'+@sSql
EXEC sp_executesql @sSql,N'@zhje varchar(100) output,@se varchar(100) output,@jshj varchar(100) output,@RowCount int OUTPUT',
@zhje output,@se output,@jshj output,@RowCount OUTPUT
select @zhje,@se,@jshj,@RowCount
end
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
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) -- 注意此处改为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)
--如何将exec执行结果放入变量中?
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