将新行作为文本插入到现有表中的受约束的过程 [英] strored procedure to insert a new row as a text into the existing table

问题描述

大家好，

我有以下存储的proc:

Hi dear All,

I have the following stored proc:

ALTER PROCEDURE  [dbo].[BINDMONTHSFORDAY] --7
   (
           @Day  BIGINT
   )
AS
SET NOCOUNT ON
--DECLARE @xyz varchar(50)
DECLARE @CurrentDate DATETIME
DECLARE @FirstDate DATETIME
DECLARE @LastDate DATETIME
DECLARE @AuditTotal BIGINT

DECLARE @Today DATETIME
DECLARE @Months TABLE 
(
--xyz varchar(50),
NewDate DATETIME,
AuditTotal BIGINT
)
DECLARE @Year BIGINT
IF (@Day < 1 OR @Day > 31)
BEGIN
   RAISERROR(''Invalid day specified for date function'', 16, 1)
   RETURN
END
SET @Today = GETDATE()
SET @Year = DATEPART(yyyy, @Today )
SET @FirstDate = CAST(CAST(1 AS varchar) + ''-'' + CAST(@Day AS varchar) + ''-'' + CAST(@Year AS varchar) AS DATETIME)
SET @LastDate = CAST(CAST(12 AS varchar) + ''-'' + CAST(@Day AS varchar) + ''-'' + CAST(@Year AS varchar) AS DATETIME)
SET @CurrentDate = @FirstDate
While @CurrentDate <= @LastDate
Begin
Insert Into @Months Values(@CurrentDate,@AuditTotal)
	IF (DATEPART(dd, @CurrentDate) != @Day)
	BEGIN
	Set @CurrentDate = DateAdd(m,1,@CurrentDate)
	SET @CurrentDate = CAST(CAST(DATEPART(mm, @CurrentDate) AS varchar) + ''-'' + CAST(@Day AS varchar) + ''-'' + CAST(@Year AS varchar) AS DATETIME)
	END
	ELSE
	BEGIN
	Set @CurrentDate = DateAdd(m,1,@CurrentDate)
	END
	
update @Months set AuditTotal=(select COUNT(A.Uid)  FROM AuditFlags A WHERE A.CPPMatch IS NOT NULL AND
    CONVERT(NVARCHAR(50),A.CreatedOn,103)= convert( NVARCHAR(50), B.NewDate, 103 ) AND HoldStatus=0) from @Months
		
End
--insert into @Months 
--select ''Grand Total'',sum(AuditTotal) from @Months 
select convert( varchar(50), NewDate, 103 ) as ''NewDate'',AuditTotal AS ''Total Audited'' FROM @Months

RETURN @@ERROR
SET NOCOUNT OFF

--------

在这里，由于该列是日期时间类型，因此我无法在"NewDate"的所有行下方插入另一行"GRAND TOTAL".
任何人都建议我，如何插入行"GRAND TOTAL"..

--------

Here,since the column is datetime type, I am not able to insert one more row ''GRAND TOTAL'' that is to be displayed below all the rows of ''NewDate''.
Any one please suggest me, how to insert the row ''GRAND TOTAL''..

推荐答案

检查粗体.

Check the bold.

DECLARE @Months TABLE 
(
--xyz varchar(50),
NewDate DATETIME,
AuditTotal BIGINT,
Grand_Total varchar(50)
)
--set the grand total value.
Insert Into @Months Values(@CurrentDate,@AuditTotal,@Grand_Total)
--if you also require update use in update statement.
select convert( varchar(50), NewDate, 103 ) as 'NewDate',AuditTotal AS 'Total Audited',Grand_Total FROM @Months

这篇关于将新行作为文本插入到现有表中的受约束的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！