Pass built-in function to stored procedure parameter

Question

I have a stored procedure (SP1), I want it to call another stored procedure(SP2).

Code SP2

ALTER PROCEDURE dbo.addOrderStatus
    (
    @orderID INT,
    @statusID INT,
    @startTime DATETIME,
    @endTime DATETIME,
    @isActive BIT
    )
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Order_Status
    (orderID, statusID, startTime, endTime, isActive) VALUES
    (@orderID, @statusID, @startTime, @endTime, @isActive)
END

Code SP1

ALTER PROCEDURE [dbo].addNewOrder
(
    @customerID int,
    @restaurantID int,
    @cost float,
    @addressID int,
    @ID INT OUTPUT
)
AS
    SET NOCOUNT OFF;

INSERT INTO [Order] 
([customerID], [restaurantID], [cost], [addressID]) 
VALUES (@customerID, @restaurantID, @cost, @addressID);

set @ID = @@IDENTITY

EXEC addOrderStatus @ID, @statusID = 1, @startTime =  SYSDATETIME , @endTime = NULL, @isActive = TRUE

My problem

I got this exception

Error converting data type nvarchar to datetime.

How could I solve it ?

Thanks advance

after your answers and comments

the code is

DECLARE @startTime DATETIME
SET @startTime = SYSDATETIME
EXEC addOrderStatus @ID, @statusID = 1, @startTime , @endTime = NULL, @isActive = TRUE

now I got that I have to use this form name , value

You can't pass a *function* to a parameter. Either declare a variable up front and assign SYSDATETIME to that, passing the variable into the parameter, or give the parameter a default value and leave it out (but if you do this you'll need to properly identify your parameters instead of being lazy and relying on ordinal position). — Aaron Bertrand, May 12 '13 at 13:15
Also, `@isActive` is a `BIT`, not a `BOOLEAN`. I suggest you use `1` instead of `TRUE` (especially `TRUE` without quotes). — Aaron Bertrand, May 12 '13 at 13:22
@AaronBertrand I did as you said, please check my edit qeustion — Marco Dinatsoli, May 12 '13 at 13:23
Ok, now name all of your parameters. `@customerID = @ID, @statusID = 1, @startTime = @startTime, @endTime = NULL, @isActive = 1;` — Aaron Bertrand, May 12 '13 at 13:24
I will be back after 1 hour, sorry but I have to go, please don't go — Marco Dinatsoli, May 12 '13 at 13:28
Use `SCOPE_IDENTITY` rather than `@@IDENTITY`. This is more robust in case an insert trigger is added to `Order` at some point. — Martin Smith, May 12 '13 at 13:29
The only system functions that can be used inline in a stored procedure parameter list are those prefixed with `@@` such as `@@spid`. Possible duplicate of [Using the result of an expression in a stored procedure parameter list?](http://stackoverflow.com/q/4936180/73226) — Martin Smith, May 12 '13 at 14:14

YvesR · Answer 1 · 2013-05-12T13:26:59.833

0

Edit - wrong part removed

Declare your variables first, set content to it and then pass them over.

DECLARE @starttime datetime;
DECLARE @statusID int;
DECLARE @endTime datetime;
DECLARE @isActive int;

SET @startTime =  GETDATE();
SET @statusID = 1;
SET @endTime = null;
SET @isActive = 1;

EXEC addOrderStatus @ID, @statusID, @startTime , @endTime, @isActive

edited May 12 '13 at 13:26

answered May 12 '13 at 13:18

YvesR

5,922
6
43
70

score 0 · Answer 2 · answered May 12 '13 at 13:31

Instead of:

EXEC addOrderStatus @ID, @statusID = 1, @startTime =  SYSDATETIME , @endTime = NULL, @isActive = TRUE

Try this:

declare @sDate datetime;
set @sDate=getdate();
EXEC addOrderStatus @ID, @statusID = 1, @startTime =  @sDate , @endTime = NULL, @isActive = TRUE

Pass built-in function to stored procedure parameter

Code SP2

Code SP1

My problem

after your answers and comments

2 Answers2