October 27, 2013 at 9:11 am
I have this code that works ok the way it is but in instead of the in the message area it saying Car Due in 5 days I want the date to show that its due on.
USE [Bills]
GO
/****** Object: StoredProcedure [dbo].[usp_Bills_Plus] Script Date: 10/27/2013 10:03:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_Bills_Plus]
as
begin
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--catch block
--block here if needed
begin try
begin Transaction;
DECLARE @TodaysDate date = getdate(),
@PhoneNumber nvarchar (max),
@Phone2 nvarchar (max),
@bill varchar(50) ,
@Date date,
@body nvarchar(MAX),-- = ' Bill Due '
@txtattnet varchar(25)
DECLARE @CurrentDay int = DAY(GETDATE()+ 5)--,@CurrentMonth int = MONTH(GETDATE())
DECLARE @BillDate TABLE(ID int IDENTITY(1,1), PhoneNumber nvarchar(MAX), Bill varchar(50))
------check phone carriers ---------
--set Identity_insert [@BillDate] on
INSERT @BillDate
SELECT PhoneNumber, Bill from dbo.Bills where Day(Date) = @CurrentDay
DECLARE @NumberOfBills smallint = (SELECT COUNT(*) from @BillDate)
DECLARE @MinID int
WHILE @NumberOfBills > 0
BEGIN
SET @MinID = (SELECT MIN(ID) From @BillDate)
SET @PhoneNumber = (SELECT PhoneNumber from @BillDate where ID = @MinID)
set @bill = (Select Bill from @BillDate where ID = @MinID)
--set @Date = (Select DATE from @BillDate where ID = @MinID)
SET @body = @bill + ', ' + ' Due in 5 days test'()
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Bill Due',
@recipients = @PhoneNumber,
--@blind_copy_recipients = ***@*****.com; ***@*****.com',
@body = @body,
@profile_name ='gmail';
DELETE FROM @BillDate where ID = @MINID
SET @NumberOfBills = @NumberOfBills -1
END
October 27, 2013 at 7:21 pm
SET @body = @bill + ', ' + CONVERT(VARCHAR(10), DATEADD(day, 5, GETDATE()), 110);
Use whatever CONVERT format code floats your boat (I chose 110).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 29, 2013 at 6:26 am
Thanks I keep putting DATEADD(day, +5, GETDATE()) not just the 5
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply