Sql例子Sp_ExecuteSql 带参数

Declare @i int, @projectCount int
Declare @Sqlstr NVarchar(400) 
--参数
Declare @projectid Varchar(20) 
Declare @SubmitOrNot Varchar(20)
declare @NotSubmitReason Varchar(1000)
declare @NotSubmitNote Varchar(1000)
Declare @usestatus Varchar(10)
Declare @ProjectName Varchar(200)
--循环变量起始
set @i = 0
--得到所有的项目
select @projectCount  = count(projectid) from t_project_manage
print @projectcount
--开始循环每一行
while @i < @projectCount
begin
    Set @Sqlstr = 'Select top 1 @projectid = projectid, @projectName=projectName, @Usestatus=usestatus,@NotSubmitReason=NotSubmitReason, @NotSubmitNote=NotSubmitNote
                 from V_ProjectWeeklyReportNotSubmit Where projectid not in 
                (Select top ' + Str(@i) + 'projectid from V_ProjectWeeklyReportNotSubmit where SubmitOrNot = 0 or SubmitOrNot is null)'
    Exec Sp_ExecuteSql @Sqlstr,N'@projectid Varchar(500) OutPut,@ProjectName Varchar(200) OutPut,@usestatus Varchar(10) OutPut, 
                                 @NotSubmitReason Varchar(1000) OutPut, @NotSubmitNote Varchar(1000) OutPut',
                                 @projectid Output, @ProjectName Output,@usestatus Output, 
                                 @NotSubmitReason Output, @NotSubmitNote Output
      Select @projectid,@ProjectName,@usestatus, @NotSubmitReason, @NotSubmitNote --一行一行把职工号显示出来
      begin
      --插入未提交表
          insert into [dbo].[T_Project_Weekly_Report_NotSubmit]     
          ([ProjectID] ,[WeekStartDate] ,[SubmitDate] ,[NotSubmitReason] ,[NotSubmitNote])
          values
          (@projectid, dateadd(day, -7, getdate()), getdate(), @NotSubmitReason, @NotSubmitNote)
      end
      --print @projectid
    set @i = @i+1
end

 

转载于:https://www.cnblogs.com/ustcyc/p/3661605.html