SQLSERVER 存储过程里执行sql字符串并返回结果

-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 停用/启用12个月销售目标
--exec [Proc_Create_SalespPlans] "'1485507540488949761','1485507420875788289','1485507282077880320'",1

CREATE PROCEDURE [dbo].[Proc_Create_SalespPlans]
@userId varchar(200),
@state int
AS

BEGIN

declare @num int
declare @year_now int
declare @month int
declare @sqlstr nvarchar(225)
set @year_now = DateName(year,GetDate())
print @userId

set @sqlstr = 'select @num=count(*) from [dbo].[CmsPlan] where CreateUserId in ('+@userId+')'
EXEC sp_executesql @sqlstr,N'@num int out',@num OUTPUT

print @num

set @month = 1

if @num = 0
BEGIN

while @month<13
BEGIN
INSERT INTO [dbo].[CmsPlan] ([Id],[CreateUserId],[CreateUserName],[OrgId],[OrgName],[Year],[Month],[state])

SELECT NEWID() [Id],[User].Id [CreateUserId],[User].Name [CreateUserName],[Org].Id [OrgId],[Org].Name [OrgName],@year_now,@month,@state
FROM [User]
INNER JOIN Relevance ON [User].Id=Relevance.FirstId AND Relevance.[Key]='UserOrg'
LEFT JOIN Org ON Relevance.SecondId=Org.Id AND Org.[Status]=0
WHERE [User].[Status]=0 AND Org.Name='业务部' and [User].Name!='admin' and [User].Id = @userId
set @month += 1
print @month
END

END
ELSE

BEGIN
set @sqlstr='update [dbo].[CmsPlan] set state = '+cast(@state as varchar)+' where CreateUserId in ('+@userId+')'
exec sp_executesql @sqlstr
END

END

巴特西

SQLSERVER 存储过程里执行sql字符串并返回结果

最新文章

热门文章

巴特西

SQLSERVER 存储过程里 执行sql字符串 并返回结果

最新文章

热门文章

SQLSERVER 存储过程里执行sql字符串并返回结果