|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can't run SP with GETDATE()the "Failed Domains" based on the beginning date and end date. I'd like to simply run this sp with getdate()-2 and getdate-1, but when I run this: Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate() I get the error Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ')'. The Stored Procedure: CREATE PROC PROC_SuccessCountByDate @begdate datetime, @enddate datetime as select count(domain_) as 'Failed Domain Count', T1m.domain_ from Table1 T1 join Table2 T2 on T2.memberid = T1.memberid_ where T2.completionstatusid in (301, 303) and T2.finalattempt > @begdate and T2.finalattempt < @enddate group by T1.domain_ having count(domain_)>5 order by 'Failed Domain Count' desc Declare @Today datetime
Declare @Yesterday datetime SET @Today = Getdate() SET @Yesterday = DATEADD(dd,-1,Getdate()) Exec dbo.PROC_SuccessCountByDate @Yesterday,@Today HTH, Jens SUessmeyer. Show quote "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag news:058453F4-9FE1-440E-ADB0-67D284B6957C@microsoft.com... > I'm having a problem running this stored procedure that is supposed to > count > the "Failed Domains" based on the beginning date and end date. > > I'd like to simply run this sp with getdate()-2 and getdate-1, but when I > run this: > > Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate() > > I get the error > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near ')'. > > > The Stored Procedure: > > CREATE PROC PROC_SuccessCountByDate > @begdate datetime, > @enddate datetime > > as > select count(domain_) as 'Failed Domain Count', T1m.domain_ > from Table1 T1 > join Table2 T2 > on T2.memberid = T1.memberid_ > where T2.completionstatusid in (301, 303) > and T2.finalattempt > @begdate > and T2.finalattempt < @enddate > group by T1.domain_ > having count(domain_)>5 > order by 'Failed Domain Count' desc > This is why I love this community; so many willing to help. Thanks. All of
you had the same solution. Thanks again Show quote "Jens Süßmeyer" wrote: > Declare @Today datetime > Declare @Yesterday datetime > > SET @Today = Getdate() > SET @Yesterday = DATEADD(dd,-1,Getdate()) > > Exec dbo.PROC_SuccessCountByDate @Yesterday,@Today > > HTH, Jens SUessmeyer. > > "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag > news:058453F4-9FE1-440E-ADB0-67D284B6957C@microsoft.com... > > I'm having a problem running this stored procedure that is supposed to > > count > > the "Failed Domains" based on the beginning date and end date. > > > > I'd like to simply run this sp with getdate()-2 and getdate-1, but when I > > run this: > > > > Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate() > > > > I get the error > > Server: Msg 170, Level 15, State 1, Line 1 > > Line 1: Incorrect syntax near ')'. > > > > > > The Stored Procedure: > > > > CREATE PROC PROC_SuccessCountByDate > > @begdate datetime, > > @enddate datetime > > > > as > > select count(domain_) as 'Failed Domain Count', T1m.domain_ > > from Table1 T1 > > join Table2 T2 > > on T2.memberid = T1.memberid_ > > where T2.completionstatusid in (301, 303) > > and T2.finalattempt > @begdate > > and T2.finalattempt < @enddate > > group by T1.domain_ > > having count(domain_)>5 > > order by 'Failed Domain Count' desc > > > > > Perhaps one time there will be an issue which you can also solve in here...
Show quote :-) "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag news:0BA224B0-F049-4E46-8BBD-6113DCCAF216@microsoft.com... > This is why I love this community; so many willing to help. Thanks. All > of > you had the same solution. > > Thanks again > > "Jens Süßmeyer" wrote: > >> Declare @Today datetime >> Declare @Yesterday datetime >> >> SET @Today = Getdate() >> SET @Yesterday = DATEADD(dd,-1,Getdate()) >> >> Exec dbo.PROC_SuccessCountByDate @Yesterday,@Today >> >> HTH, Jens SUessmeyer. >> >> "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag >> news:058453F4-9FE1-440E-ADB0-67D284B6957C@microsoft.com... >> > I'm having a problem running this stored procedure that is supposed to >> > count >> > the "Failed Domains" based on the beginning date and end date. >> > >> > I'd like to simply run this sp with getdate()-2 and getdate-1, but when >> > I >> > run this: >> > >> > Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate() >> > >> > I get the error >> > Server: Msg 170, Level 15, State 1, Line 1 >> > Line 1: Incorrect syntax near ')'. >> > >> > >> > The Stored Procedure: >> > >> > CREATE PROC PROC_SuccessCountByDate >> > @begdate datetime, >> > @enddate datetime >> > >> > as >> > select count(domain_) as 'Failed Domain Count', T1m.domain_ >> > from Table1 T1 >> > join Table2 T2 >> > on T2.memberid = T1.memberid_ >> > where T2.completionstatusid in (301, 303) >> > and T2.finalattempt > @begdate >> > and T2.finalattempt < @enddate >> > group by T1.domain_ >> > having count(domain_)>5 >> > order by 'Failed Domain Count' desc >> > >> >> >> i want to make SP that enters values into my table, which has the default
value of the date column to getdate so, if i didn't enter a value it will take the default value of the column how can i describe this in the sp declaration ? my sp is as follows : ----------------------------------------------------------- create procedure add_new_abstract @abs_name nvarchar(20) , @id_reg nvarchar(10), @id_topic nvarchar(10), @id_stat nvarchar(10), @abst nvarchar(20) , @job_id nvarchar(20), @date_abs datetime as insert abstract values (cast(cast(rand()*9285 as int)as nvarchar(10)),@abs_name,@id_reg,@id_topic,@id_stat,@abst,@job_id,@date_abs) go -------------------------------------- so, when i execute it with the getdate() it raise the error specified in this discussion and when i neglect it so that it takes the value as the default value of the column , it asks for it so, how can i solve this ? thanx for ur help -- Show quoteregards Maidoo. "Jens Süßmeyer" wrote: > Perhaps one time there will be an issue which you can also solve in here... > :-) > > > "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag > news:0BA224B0-F049-4E46-8BBD-6113DCCAF216@microsoft.com... > > This is why I love this community; so many willing to help. Thanks. All > > of > > you had the same solution. > > > > Thanks again > > > > "Jens Süßmeyer" wrote: > > > >> Declare @Today datetime > >> Declare @Yesterday datetime > >> > >> SET @Today = Getdate() > >> SET @Yesterday = DATEADD(dd,-1,Getdate()) > >> > >> Exec dbo.PROC_SuccessCountByDate @Yesterday,@Today > >> > >> HTH, Jens SUessmeyer. > >> > >> "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag > >> news:058453F4-9FE1-440E-ADB0-67D284B6957C@microsoft.com... > >> > I'm having a problem running this stored procedure that is supposed to > >> > count > >> > the "Failed Domains" based on the beginning date and end date. > >> > > >> > I'd like to simply run this sp with getdate()-2 and getdate-1, but when > >> > I > >> > run this: > >> > > >> > Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate() > >> > > >> > I get the error > >> > Server: Msg 170, Level 15, State 1, Line 1 > >> > Line 1: Incorrect syntax near ')'. > >> > > >> > > >> > The Stored Procedure: > >> > > >> > CREATE PROC PROC_SuccessCountByDate > >> > @begdate datetime, > >> > @enddate datetime > >> > > >> > as > >> > select count(domain_) as 'Failed Domain Count', T1m.domain_ > >> > from Table1 T1 > >> > join Table2 T2 > >> > on T2.memberid = T1.memberid_ > >> > where T2.completionstatusid in (301, 303) > >> > and T2.finalattempt > @begdate > >> > and T2.finalattempt < @enddate > >> > group by T1.domain_ > >> > having count(domain_)>5 > >> > order by 'Failed Domain Count' desc > >> > > >> > >> > >> > > > On Fri, 10 Jun 2005 10:59:27 -0700, maidoo wrote:
>i want to make SP that enters values into my table, which has the default Hi maidoo,>value of the date column to getdate >so, if i didn't enter a value it will take the default value of the column >how can i describe this in the sp declaration ? Change your proc like this: create procedure add_new_abstract @abs_name nvarchar(20) , @id_reg nvarchar(10), @id_topic nvarchar(10), @id_stat nvarchar(10), @abst nvarchar(20) , @job_id nvarchar(20), @date_abs datetime = null as insert abstract values (cast(cast(rand()*9285 as int)as nvarchar(10)),@abs_name,@id_reg,@id_topic,@id_stat,@abst,@job_id, COALESCE(@date_abs, CURRENT_TIMESTAMP)) go Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) create procedure add_new_abstract
@abs_name nvarchar(20) , @id_reg nvarchar(10), @id_topic nvarchar(10), @id_stat nvarchar(10), @abst nvarchar(20) , @job_id nvarchar(20), @date_abs datetime as SET @date_abs = ISNULL(@date_abs, GETDATE()) insert abstract values (cast(cast(rand()*9285 as int)as nvarchar(10)),@abs_name,@id_reg,@id_topic,@id_stat,@abst,@job_id,@date_abs) go -- Show quote-------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "maidoo" <maidooa***@msn.com> wrote in message news:B70444AF-0A65-416E-91DB-05E4D911809E@microsoft.com... >i want to make SP that enters values into my table, which has the default > value of the date column to getdate > so, if i didn't enter a value it will take the default value of the column > how can i describe this in the sp declaration ? > > my sp is as follows : > ----------------------------------------------------------- > create procedure add_new_abstract > > @abs_name nvarchar(20) , > @id_reg nvarchar(10), > @id_topic nvarchar(10), > @id_stat nvarchar(10), > @abst nvarchar(20) , > @job_id nvarchar(20), > @date_abs datetime > as > insert abstract > values (cast(cast(rand()*9285 as int)as > nvarchar(10)),@abs_name,@id_reg,@id_topic,@id_stat,@abst,@job_id,@date_abs) > go > -------------------------------------- > so, when i execute it with the getdate() it raise the error specified in > this discussion > and when i neglect it so that it takes the value as the default value of > the > column , it asks for it > so, how can i solve this ? > thanx for ur help > -- > regards > Maidoo. > > > > "Jens Süßmeyer" wrote: > >> Perhaps one time there will be an issue which you can also solve in >> here... >> :-) >> >> >> "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag >> news:0BA224B0-F049-4E46-8BBD-6113DCCAF216@microsoft.com... >> > This is why I love this community; so many willing to help. Thanks. >> > All >> > of >> > you had the same solution. >> > >> > Thanks again >> > >> > "Jens Süßmeyer" wrote: >> > >> >> Declare @Today datetime >> >> Declare @Yesterday datetime >> >> >> >> SET @Today = Getdate() >> >> SET @Yesterday = DATEADD(dd,-1,Getdate()) >> >> >> >> Exec dbo.PROC_SuccessCountByDate @Yesterday,@Today >> >> >> >> HTH, Jens SUessmeyer. >> >> >> >> "savvy95" <savv***@discussions.microsoft.com> schrieb im Newsbeitrag >> >> news:058453F4-9FE1-440E-ADB0-67D284B6957C@microsoft.com... >> >> > I'm having a problem running this stored procedure that is supposed >> >> > to >> >> > count >> >> > the "Failed Domains" based on the beginning date and end date. >> >> > >> >> > I'd like to simply run this sp with getdate()-2 and getdate-1, but >> >> > when >> >> > I >> >> > run this: >> >> > >> >> > Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate() >> >> > >> >> > I get the error >> >> > Server: Msg 170, Level 15, State 1, Line 1 >> >> > Line 1: Incorrect syntax near ')'. >> >> > >> >> > >> >> > The Stored Procedure: >> >> > >> >> > CREATE PROC PROC_SuccessCountByDate >> >> > @begdate datetime, >> >> > @enddate datetime >> >> > >> >> > as >> >> > select count(domain_) as 'Failed Domain Count', T1m.domain_ >> >> > from Table1 T1 >> >> > join Table2 T2 >> >> > on T2.memberid = T1.memberid_ >> >> > where T2.completionstatusid in (301, 303) >> >> > and T2.finalattempt > @begdate >> >> > and T2.finalattempt < @enddate >> >> > group by T1.domain_ >> >> > having count(domain_)>5 >> >> > order by 'Failed Domain Count' desc >> >> > >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics