34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @StartDate CHAR(7)='2014-01'
DECLARE @EndDate CHAR(7) ='2014-02'
DECLARE @Dates DATETIME = CAST(@StartDate+'-01'AS DATETIME)
DECLARE @CreateTableSql NVARCHAR(4000)
IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL DROP TAbLE #Test
CREATE TABLE #Test
(
Name varchar(7)
)
WHILE @Dates<=CAST(@EndDate+'-01'AS DATETIME)
BEGIN
PRINT CONVERT(varchar(50),@Dates,23)
SET @CreateTableSql = N'ALTER TABLE #Test ADD ['+CONVERT(varchar(50),@Dates,23)+' 周'+Ltrim(DATEPART(weekday,@Dates))+'] VARCHAR(200) NULL'
EXEC (@CreateTableSql)
SET @Dates=DATEADD(Day,1,@Dates)
END
SELECT * FROM #Test
BEGIN TRAN
SELECT 1 AS ID INTO #
RAISERROR(N'Error',16,1)
IF @@ERROR>0
BEGIN
ROLLBACK TRAN
SELECT * FROM #
END
COMMIT TRAN
/*
消息 50000,级别 16,状态 1,第 3 行
Error
消息 208,级别 16,状态 0,第 7 行
对象名 '#' 无效。
*/
CREATE PROCEDURE P1
AS
DECLARE @StartDate CHAR(7)='2014-01'
DECLARE @EndDate CHAR(7) ='2014-02'
DECLARE @Dates DATETIME = CAST(@StartDate+'-01'AS DATETIME)
DECLARE @CreateTableSql NVARCHAR(4000)
IF OBJECT_ID('Test','U') IS NOT NULL DROP TAbLE Test
CREATE TABLE Test
(
Name varchar(7)
)
WHILE @Dates<=CAST(@EndDate+'-01'AS DATETIME)
BEGIN
PRINT CONVERT(varchar(50),@Dates,23)
SET @CreateTableSql = N'ALTER TABLE Test ADD ['+CONVERT(varchar(50),@Dates,23)+' 周'+Ltrim(DATEPART(weekday,@Dates))+'] VARCHAR(200) NULL'
EXEC (@CreateTableSql)
SET @Dates=DATEADD(Day,1,@Dates)
END
SELECT * FROM Test
GO
EXEC P1--调用方法