Using CAST/CONVERT in SP call.

Question

Post reply

Using CAST/CONVERT in SP call.

Trystan Hughes

SSCrazy

Points: 2109
More actions
February 27, 2006 at 9:21 am

#189365

Hi all,
is it not possible to use a SQL function call such as CAST/CONVERT in a call to a Stored Procedure such as the following?
EXEC u_GetWeekNo CONVERT(DATETIME, SUBSTRING(CAST(GETDATE() AS CHAR(20)), 1, 12), 103), @week OUTPUT
Thanks
Tryst

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

You can circumvent it with

DECLARE @var1 datetime

SET @var1 =CONVERT(DATETIME, SUBSTRING(CAST(GETDATE() AS CHAR(20)), 1, 12), 103)

EXECUTE u_GetWeekNo @myvar=@var1,@week OUTPUT

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 2

Yeah, I had that solution.

Out of curosity, why have you use 'myvar=@var1' instead of just passing in @var1?

Thanks

Tryst

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

Because if for an odd reason the order of the parameters change (similiar with the order of columns in a table), the call will still work.

procedure myproc @startdate,@numberofdays

-> procedure myproc @numberofdays,@startdate

EXECUTE myproc '2006/02/01',5 would cause an error (converting '2006/02/01' to an int?)

EXECUTE myproc @startdate='2006/02/01',@numberofdays=5 works in both cases.

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 4

Ahh, so naming the passed in variables the same as the names in which the parameters are declared in the SP?

Thanks

Tryst

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Yes Trystan, that's it. @myvar is how the parameter is called in procedure. You just explicitly name it when assigning values during SP call, to make sure there is no confusion about what is what.