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' descDeclare @.Today datetime
Declare @.Yesterday datetime
SET @.Today = Getdate()
SET @.Yesterday = DATEADD(dd,-1,Getdate())
Exec dbo.PROC_SuccessCountByDate @.Yesterday,@.Today
HTH, Jens SUessmeyer.
"savvy95" <savvy95@.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
>|||Yes, you can;t pass an "Expression". like [getdate() - 1], as a parameter
to a Stored Proc.
You can only pass constant values, or variables (either the variable value
or address)
So in your case, create a variable (Declare @.MyDateTime DateTime) and set
the value of that variableto getdate() - 1, and then make your call using th
e
variable...
Exec dbo.PROC_SuccessCountByDate @.MyDateTime, getdate()
And get rid of the extra parentheses after the comma
Exec dbo.PROC_SuccessCountByDate getdate()-1, (getdate()
"savvy95" wrote:
> I'm having a problem running this stored procedure that is supposed to cou
nt
> 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 @.bd datetime
declare @.ed datetime
set @.bd = dateadd(day, -1, getdate())
set @.ed = getdate()
Exec dbo.PROC_SuccessCountByDate @.bd, @.ed
...
AMB
"savvy95" wrote:
> I'm having a problem running this stored procedure that is supposed to cou
nt
> 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
"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" <savvy95@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:058453F4-9FE1-440E-ADB0-67D284B6957C@.microsoft.com...
>
>|||Perhaps one time there will be an issue which you can also solve in here...
:-)
"savvy95" <savvy95@.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 Smeyer" wrote:
>|||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_topi
c,@.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" <savvy95@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:0BA224B0-F049-4E46-8BBD-6113DCCAF216@.microsoft.com...
>
>|||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
>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 ?
Hi maidoo,
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_topi
c,@.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_topi
c,@.id_stat,@.abst,@.job_id,@.date_abs)
go
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maidoo" <maidooalex@.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_topi
c,@.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 Smeyer" wrote:
>
8) [10:44:52:780]: Note: 1: 1708