SQL Server - How execute stored procedure from Management Studio?

Asked By Bill on 22-Mar-08 07:37 PM
I am trying to execute a stored procedure from the query window in
Management Studio Express. The proc requires a DateTime parameter and I
cannot find a way to use a literal for the date. I have tried

EXECUTE MONITORINGINVOICES @BillingDate =
CAST('2008-04-0104T00:00:00.000' AS DateTime), @BatchNumber = 'xx1',
@User_Id = 'Bill', @InvoiceCount = 0

and every other variant of CAST and CONVERT I can think of but I always
get a "Incorrect syntax near '2008-04-0104T00:00:00.000'" near the date
literal. How can I run this stored proc from Management Studio?
Thanks.

--
.Bill.




Stuart Ainsworth replied on 23-Mar-08 12:12 PM
Looks like you have a typo in your example:

2008-04-0104T00:00:00.000  is not a valid date; 2008-0401T00:00:00.000
is.

You should be able to just use @BillingDate =3D
'2008-04-01T00:00:00.000', BTW.

Stu
Tom Cooper replied on 22-Mar-08 09:52 PM
Actually, it's not just true that you CAN use @BillingDate =
'2008-04-01T00:00:00.000', you MUST either do that (which will implicitly
convert the string to a datetime - that is not a problem since you have
correctly used one of the unambigous datetime formats) or assign the result
of the Cast() function to a variable and then pass that variable.  You can
only pass values or variables as parameters, you are not allowed to pass an
expression.  So either of the following is legal and should get you what you
want:

EXECUTE MONITORINGINVOICES @BillingDate = '2008-04-01T00:00:00.000',
@BatchNumber = 'xx1',
@User_Id = 'Bill', @InvoiceCount = 0

or

Declare @TempDate datetime
Set @TempDate = CAST('2008-04-01T00:00:00.000' AS DateTime)
EXECUTE MONITORINGINVOICES @BillingDate = @TempDate, @BatchNumber = 'xx1',
@User_Id = 'Bill', @InvoiceCount = 0

Tom
Bill replied on 22-Mar-08 10:41 PM
Thanks. It is obviously time to quite for tonight.<g>

--
.Bill.
Bill replied on 22-Mar-08 10:41 PM
Thanks for the detailed explanation.

--
.Bill.