April 7, 2011 at 6:53 am
Hi,
When I am trying to create this below procedure code I am getting errors. The problem is with using @startdate and @enddate inside the single quotes. Can anyone tell me why this is and how to correct it
USE [KRMREPORT]
GO
/****** Object: StoredProcedure [dbo].[GetSendMissingRCReport_krm9] Script Date: 04/07/2011 14:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================================================
-- Ajantasa-Realtime Oy
-- Marko Soivio
-- 2008-11-26
-- Copyright (C) KONE Corporation 2008
-- J-P Hienonen
-- Retrieve missing routine call count of all companies and e-mail to recipients.
-- ================================================================================
Alter PROCEDURE [dbo].[GetSendMissingRCReport_march] (@startdate datetime, @enddate datetime)
AS
BEGIN
SET NOCOUNT ON;
DECLARE@cText varchar(8000),
@cHlpvarchar(50),
@cSubjectvarchar(255),
@cDatevarchar(50)
SET @cDate='2008-10-31 11:00'
--**KDK**
--KRM all
SELECT @cHlp='KDK KRM All:' + char(9) + KRMAll.C
FROM OPENROWSET('SQLOLEDB','10.32.44.80';'KRMQuery';'KRM001',
'SELECT
convert(varchar(50),Count(*)) AS C
FROM
Equipment AS E INNER JOIN SiteInformations AS SI
ON E.SiteID=SI.SiteID
WHERE
SI.CompanyCode=''KED''
ANDE.RemoteMonitoringType_FK=''KNXM''
ANDE.RifGeneratedID IS NOT NULL
ANDE.RoutineCallCheckDisabled=0
FOR BROWSE
') AS KRMAll
SET @cText = @cHlp + char(10)
--KRM missing
SELECT @cHlp='KDK KRM Missing:' + char(9) + KRMMissing.C
FROM OPENROWSET('SQLOLEDB','10.32.44.80';'KRMQuery';'KRM001',
'SELECT
convert(varchar(50),Count(*)) AS C
FROM
KRM.dbo.MissingRoutineCall AS MRC INNER JOIN KRM.dbo.Equipment AS E
ON MRC.EquipmentNumber_FK=E.EquipmentNumber AND E.RemoteMonitoringType_FK=''KNXM'' INNER JOIN SiteInformations AS SI
ON E.SiteID=SI.SiteID
WHERE
SI.CompanyCode=''KED''
ANDMRC.CreateDate> '@startdate' and mrc.createdate< '+@enddate+'
FOR BROWSE
') AS KRMMissing
SET @cText = @cText + @cHlp + char(10)
--KNX all
SELECT @cHlp='KDK KNX All:' + char(9) + KNXAll.C
FROM OPENROWSET('SQLOLEDB','10.32.44.80';'KRMQuery';'KRM001',
'SELECT
convert(varchar(50),Count(*)) AS C
FROM
Equipment AS E INNER JOIN SiteInformations AS SI
ON E.SiteID=SI.SiteID
WHERE
SI.CompanyCode=''KED''
ANDRemoteMonitoringType_FK=''KNX3''
ANDRoutineCallCheckDisabled=0
FOR BROWSE
') AS KNXAll
SET @cText = @cText + @cHlp + char(10)
--KNX missing
SELECT @cHlp='KDK KNX Missing:' + char(9) + KNXMissing.C
FROM OPENROWSET('SQLOLEDB','10.32.44.80';'KRMQuery';'KRM001',
'SELECT
convert(varchar(50),Count(*)) AS C
FROM
KRM.dbo.MissingRoutineCall AS MRC INNER JOIN KRM.dbo.Equipment AS E
ON MRC.EquipmentNumber_FK=E.EquipmentNumber AND E.RemoteMonitoringType_FK=''KNX3'' INNER JOIN SiteInformations AS SI
ON E.SiteID=SI.SiteID
WHERE
SI.CompanyCode=''KED''
ANDMRC.CreateDate> '+@startdate+' and mrc.createdate< '+@enddate+'
FOR BROWSE
') AS KNXMissing
SET @cText = @cText + @cHlp + char(10) + char(10)
SET @cHlp = 'Missing RC count ' + convert(varchar(10),GetDate(),112)
EXEC msdb.dbo.sp_send_dbmail
@profile_name='KRM'
,@recipients='naveen.ekkaldevi@kone.com'
,@body = @cText
--,@query=@STR
,@subject=@cHlp
END
Thanks in Advance
Naveen
April 7, 2011 at 7:11 am
you need to cast your dates as a varchar in order to concatenate with a string.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 7, 2011 at 7:21 am
ANDMRC.CreateDate> '+convert(varchar(20),@startdate)' and mrc.createdate< '+convert(varchar(20),@enddate)'
I tried with abovebut sillit fails
April 7, 2011 at 7:31 am
what is the error message?
you would likely what something like this.
CreateDate> '''+convert(varchar(20),@startdate)'''
you would need three single quotes so that the resulted string would include the string date in single quote marks.
so
CreateDate> '''+convert(varchar(20),@startdate)'''
would equal
CreateDate> '04/01/11'
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 7, 2011 at 7:41 am
when tried with this getting the error Incorrect syntax near '+'.
ANDMRC.CreateDate > '''+convert(varchar(20),@startdate)''' and mrc.createdate < '''+convert(varchar(20),@enddate)'''
April 7, 2011 at 7:47 am
you need a + on both sides of the variable.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply