strored procedure to insert a new row as a text into the existing table

Question

3.00/5 (2 votes)

See more:

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

--------

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'..

Posted 6-Mar-11 22:47pm

Raj.rcr

Updated 7-Mar-11 1:11am

v9

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

m@dhu · Answer 1 · 2011-03-06T23:05:00

Solution 1

Check the bold.

SQL

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

Posted 6-Mar-11 23:05pm

m@dhu

Comments

Raj.rcr 7-Mar-11 5:21am

No, I want 'NewDate' as the 'GRAND TOTAL'. That means, one more row called 'GRAND TOTAL' as a last row of the 'NewDate' column. sorry, I guess, my question was bit confusing.

m@dhu 7-Mar-11 5:38am

No I don't think it is possible to insert text into datetime field.

Raj.rcr 7-Mar-11 5:48am

Yep, u r right.. I heard the same from my ofc mate.. But, how to display the Total row? Is there any other idea?