22,209
社区成员
发帖
与我相关
我的任务
分享
declare @TempStr nvarchar(300) , @PLArray nvarchar(10) , @TotalPoint int ,@Luck0 int,@Luck1 int,@Luck2 int,@Luck3 int,@Luck4 int,@Luck5 int,@Luck6 int,@Luck7 int,@Luck8 int select @TotalPoint=sum(TotalPoint),@Luck0=sum([Luck0]),@Luck1=sum([Luck1]),@Luck2=sum([Luck2]),@Luck3=sum([Luck3]),@Luck4=sum([Luck4]),@Luck5=sum([Luck5]),@Luck6=sum([Luck6]),@Luck7=sum([Luck7]),@Luck8=sum([Luck8]) from UserList where NumID=10 if @Luck0=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck0 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck1=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck1 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck2=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck2 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck3=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck3 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck4=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck4 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck5=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck5 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck6=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck6 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck7=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck7 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' if @Luck8=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck8 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' update ShowData set ShowStr=@TempStr where NumID=10
create table UserList(ID int,NumID int,TotalPoint int,Luck0 int,Luck1 int,Luck2 int,
Luck3 int,Luck4 int,Luck5 int,Luck6 int,Luck7 int,Luck8 int)
go
CREATE PROCEDURE AddData
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000)
set @s1='declare @TempStr nvarchar(300) , @PLArray nvarchar(10) , @TotalPoint int '
set @s2='@TotalPoint=sum(TotalPoint)'
set @s3=' '
select
@s1=@s1+',@'+Name+' int',
@s2=@s2+',@'+Name+'=sum('+quotename(Name)+')',
@s3=@s3+' if @'+Name+'=0 set @PLArray= ''--'' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@'+Name+' as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ '', '' '
from
syscolumns
where
ID=object_id('UserList') and left(Name,4)='Luck'
set @s2='select '+@s2+' from UserList where NumID=10 '
--print @s1
--print @s2
--print @s3
print @s1+' '+@s2+' '+@s3+' update ShowData set ShowStr=@TempStr where NumID=10 '
exec (@s1+' '+@s2+' '+@s3+' update ShowData set ShowStr=@TempStr where NumID=10 ')
--这一段显示出来要执行的语句,可注释它
print @s1+' '+@s2+' '+@s3+' update ShowData set ShowStr=@TempStr where NumID=10 '
--显示出来要执行的语句
declare @TempStr nvarchar(300) , @PLArray nvarchar(10) , @TotalPoint int ,@Luck0 int,@Luck1 int,@Luck2 int,@Luck3 int,@Luck4 int,@Luck5 int,@Luck6 int,@Luck7 int,@Luck8 int select @TotalPoint=sum(TotalPoint),@Name=sum([Luck0]),@Name=sum([Luck1]),@Name=sum([Luck2]),@Name=sum([Luck3]),@Name=sum([Luck4]),@Name=sum([Luck5]),@Name=sum([Luck6]),@Name=sum([Luck7]),@Name=sum([Luck8]) from UserList where NumID=10 if @Luck0=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck0 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck1=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck1 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck2=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck2 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck3=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck3 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck4=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck4 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck5=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck5 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck6=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck6 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck7=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck7 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' @Luck8=0 set @PLArray= '--' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@Luck8 as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ ', ' update ShowData set ShowStr=@TempStr where NumID=10
create table UserList(ID int,NumID int,TotalPoint int,Luck0 int,Luck1 int,Luck2 int,
Luck3 int,Luck4 int,Luck5 int,Luck6 int,Luck7 int,Luck8 int)
go
CREATE PROCEDURE AddData
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000)
set @s1='declare @TempStr nvarchar(300) , @PLArray nvarchar(10) , @TotalPoint int '
set @s2='@TotalPoint=sum(TotalPoint)'
set @s3='if '
select
@s1=@s1+',@'+Name+' int',
@s2=@s2+',@Name=sum('+quotename(Name)+')',
@s3=@s3+'@'+Name+'=0 set @PLArray= ''--'' else set @PLArray=cast(cast(@TotalPoint as float)/cast(@'+Name+' as float) as decimal(38,2)) set @TempStr=@TempStr+@PLArray+ '', '' '
from
syscolumns
where
ID=object_id('UserList') and left(Name,4)='Luck'
set @s2='select '+@s2+' from UserList where NumID=10 '
--print @s1
--print @s2
--print @s3
print @s1+' '+@s2+' '+@s3+' update ShowData set ShowStr=@TempStr where NumID=10 '
exec (@s1+' '+@s2+' '+@s3+' update ShowData set ShowStr=@TempStr where NumID=10 ')
动态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