Stored Procedure Parameters Default Values ???

[MdotButler]

I am trying to set default values on the parameters defined in a procedure. Following is the parameters portion of the 'create procedure' I am trying to set up.

CREATE PROCEDURE xxx_Insert
    (
    @U_UID char = NEWID,
    @U_LoginName varchar = '',
    @U_PassWord varchar = '',
    @U_Enabled bit = 1,
    @U_LoginDate datetime = NULL,
    @U_LoginCount int = 0,
    @U_LoginFailedDate datetime = NULL,
    @U_LoginFailedCount int = 0,
    @U_UserName varchar = '',
    @U_Affiliation varchar = '',
    @U_Phone varchar = '',
    @U_UserType varchar = '',
    @U_PasswordChanged datetime = NULL,
    @U_Comments varchar = '',
    @U_GUID char = '',
    @U_AddDate smalldatetime = GETDATE,
    @U_AddUID char = NULL,
    @U_ChgDate smalldatetime = GETDATE,
    @U_ChgUID char = NULL
    )

When I run the create procedure script I get a number of errors which I can't seem to get past. Mainly it is the setting of the character type parameters to '' which is the major culprit. Following are the errors.

Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_UID' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_LoginName' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_PassWord' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_UserName' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_Affiliation' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_Phone' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_UserType' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_Comments' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_GUID' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_AddUID' has an invalid data type.
Server: Msg 2724, Level 16, State 1, Procedure xxx_Insert, Line 0
Parameter '@U_ChgUID' has an invalid data type.
Parameter '@U_UID' has an invalid data type.
Parameter '@U_LoginName' has an invalid data type.
Parameter '@U_PassWord' has an invalid data type.
Parameter '@U_UserName' has an invalid data type.
Parameter '@U_Affiliation' has an invalid data type.
Parameter '@U_Phone' has an invalid data type.
Parameter '@U_UserType' has an invalid data type.
Parameter '@U_Comments' has an invalid data type.
Parameter '@U_GUID' has an invalid data type.
Parameter '@U_AddUID' has an invalid data type.
Parameter '@U_ChgUID' has an invalid data type.

It has to be something very easy. What am I missing?

TIA
Mark

 

[AlexCuse]

You need to specify length.

Like char(6) or varchar(900)

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

One thing I see right off is that you haven;t set the length of the varchar and char parameters. This may not be your problem, but I assure you it will be a problem when you try to send data in.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

For your char and varchar parameters, you must specify the size. Also, you need to put parenthesis after NewId() and GetDate()

Change:
@U_UID char = NEWID

To:
@U_UID char(36) = NEWID()



-George

"the screen with the little boxes in the window." - Moron

 

[MdotButler]

The problem was that the length of the parameters was missing. George when I use NEWID() I get:

Line 3: Incorrect syntax near '('.

Thanx for the help. I knew better, but couldn't see what was right in front of me.

 

[gmmastros]

OK. OK. Sure. I see that now.

Just for fun, try this...

Create Procedure Test_NewId
  @Param Char(36) = NewId
As
Select @Param

Now, execute the stored procedure without the parameter.

[tt][blue]Exec Test_NewId[/blue][/tt]

You'll actually get the value 'NEWID' selected as data, not a guid, like you expect. You will have the same problem with the GETDATE() parameter.

According to BOL....

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The [!]default must be a constant or it can be NULL.[/!] It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

NewId() and GetDate() are not exactly constants, are they. Instead, set those parameters to default = NULL. Then, inside the proc, put...

Set @U_UID = Coalesce(@U_UID, NEWID())

If the parameter is left blank, it will be NULL. The Coalesce function will set it's value to NEWID() if it starts off with null.

Make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[MdotButler]

Thanx George for saving me that discovery. I would have been scratching my head on that one next. It is interesting that NEWID is treated as a string and that the single quote is only needed if there is an embedded blank in the string.

It is also interesting that in the case of the date fields it did not bark about a date being set to a string or at least not a valid date string...

Thanx again...

 

[SQLDenis]

when calling the proc with positional parameters instead of named parameters then the optional parameters have to be on the end you can not ommit one in the middle because the rest will shit

lets say your proc has 3 parameters and you want to ommit the second one
you do this with name parameters

exec proc @param1,@param2

with positional parameters you have to do this
exec proc 1,null,1

however if you want to ommit param3 then you can do this
exec proc 1,1

obviously these parameters have to be setup with a null default value


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions