以下是我的完整语句
CREATE PROCEDURE ReturnHistory @StartDateTime datetime,@EndDateTime datetime,@TagList varchar(500)
as
DECLARE @T table(MYID int IDENTITY(1,1) ,Month_int int)--临时表,
DECLARE @i int,@StartMonth int,@EndMonth int,@ThisMonth int,@CurMonth int
DECLARE @sql varchar(5000),@char_Month varchar(2),@tablename varchar(20),@tempsql Varchar(500)Set @ThisMonth=DATEPART(mm, GetDate())
Set @StartMonth=DATEPART(mm, @StartDateTime)
Set @EndMonth=DATEPART(mm, @EndDateTime)SET @i=1
WHILE @i<=12
BEGIN
INSERT INTO @T(Month_int) VALUES(@i)
SET @i=@i+1
END--如果是本月
IF (@StartMonth=@EndMonth) and (@ThisMonth=@StartMonth)
begin
Set @sql='Select dateandtime,'+Rtrim(@TagList)+' From FloatTable where dateandtime >=''' +cast(@StartDateTime as varchar(40))+''' and dateandtime <='''+cast(@EndDateTime as varchar(40))+''' group by dateandtime'
Exec(@SQL)
Return
end--如果本月以前的时间
IF (@EndMonth<@ThisMonth)
begin
Set @SQL=' '
Set @CurMonth= @StartMonth
While @CurMonth<=@EndMonth
begin
If @CurMonth<10
set @char_Month='0'+Cast(@CurMonth as Varchar(1))
Else
set @char_Month=Cast(@CurMonth as Varchar(2))Set @TableName='History_'+@char_Month
Set @tempsql='Select dateandtime,'+@TagList+' From '+@TableName+' where dateandtime >=''' +cast(@StartDateTime as varchar(40))+''' and dateandtime <='''+cast(@EndDateTime as varchar(40))+''' group by dateandtime'set @SQL= @SQL+Rtrim(@tempsql)if @CurMonth<@EndMonth
Set @SQL= @SQL+' UNION ALL '
set @CurMonth= @CurMonth+1
end
Exec(@SQL)
Return
end我发现总不能得到正确 @SQL,总是被截除一块
CREATE PROCEDURE ReturnHistory @StartDateTime datetime,@EndDateTime datetime,@TagList varchar(500)
as
DECLARE @T table(MYID int IDENTITY(1,1) ,Month_int int)--临时表,
DECLARE @i int,@StartMonth int,@EndMonth int,@ThisMonth int,@CurMonth int
DECLARE @sql varchar(5000),@char_Month varchar(2),@tablename varchar(20),@tempsql Varchar(500)Set @ThisMonth=DATEPART(mm, GetDate())
Set @StartMonth=DATEPART(mm, @StartDateTime)
Set @EndMonth=DATEPART(mm, @EndDateTime)SET @i=1
WHILE @i<=12
BEGIN
INSERT INTO @T(Month_int) VALUES(@i)
SET @i=@i+1
END--如果是本月
IF (@StartMonth=@EndMonth) and (@ThisMonth=@StartMonth)
begin
Set @sql='Select dateandtime,'+Rtrim(@TagList)+' From FloatTable where dateandtime >=''' +cast(@StartDateTime as varchar(40))+''' and dateandtime <='''+cast(@EndDateTime as varchar(40))+''' group by dateandtime'
Exec(@SQL)
Return
end--如果本月以前的时间
IF (@EndMonth<@ThisMonth)
begin
Set @SQL=' '
Set @CurMonth= @StartMonth
While @CurMonth<=@EndMonth
begin
If @CurMonth<10
set @char_Month='0'+Cast(@CurMonth as Varchar(1))
Else
set @char_Month=Cast(@CurMonth as Varchar(2))Set @TableName='History_'+@char_Month
Set @tempsql='Select dateandtime,'+@TagList+' From '+@TableName+' where dateandtime >=''' +cast(@StartDateTime as varchar(40))+''' and dateandtime <='''+cast(@EndDateTime as varchar(40))+''' group by dateandtime'set @SQL= @SQL+Rtrim(@tempsql)if @CurMonth<@EndMonth
Set @SQL= @SQL+' UNION ALL '
set @CurMonth= @CurMonth+1
end
Exec(@SQL)
Return
end我发现总不能得到正确 @SQL,总是被截除一块
PRINT @SQL
Exec(@SQL)这样看看你打印出来的是否正确,varchar最大的长度是8000,不是4000。
SQL Query Analyzer-->Tools-->Option(CTRL+SHIFT+O)-->Results
下Maximum characters per column: 8000