Default nvarchar parameter not respected

Question

I have a stored procedure in SQL Server 2008 R2 with the following parameter values declared:

@UN nvarchar(30),
@SN nvarchar(8),
@GG uniqueidentifier,
@Ss irnapp.SymbolTableType READONLY,
@SD date,
@ED date,
@IR nvarchar(1),
@ID nvarchar(1),
@NR int = NULL,
@GP nvarchar(1) = N'N'

It was my intention that if the @GP value is not supplied, then it should be given a value of N'N'. However, the procedure only returns the expected results when I explicitly pass in N'N' for @GP.

I've attempted searching for examples of SQL stored procedures with default parameter values, but the only examples I've found for nvarchar are defaults of NULL, which is not feasible for my application.

Would anyone know if the above is a legal default parameter declaration?

UPDATE:

Thanks Aaron for the quick response. I was hoping this would be a simple catch, as the code is quite lengthy. That said, here goes:

  BEGIN TRY
    DECLARE @GI int;
    EXEC irn.GetGroupID @UN, @SN, @GG, @GI OUT;

    DECLARE @CUID int;
    IF @GP = N'Y'       
    BEGIN
        SELECT @CUID = UserID
            FROM Users
            WHERE Uname = @UN
                AND SNum = @SN;
    END;    

    DECLARE @NoteIDs irn.NoteIDTableType;

    INSERT INTO @NIDs (NO, NID)
        SELECT *
            FROM GetNIDs(@GI, @Ss, @SD, @ED, @IR, @ID, @NR, @GP, @CUID);

    EXEC GetNsByNIDs @NIDs, N'N';
END TRY
BEGIN CATCH
    EXEC irn.CreateProcedureErrorLog 
    EXEC irn.RaiseProcedureError 
END CATCH;




ALTER FUNCTION [i].[GetNIDs] (
    @GID int,
    @Ss SymbolTableType READONLY,
    @SD date,
    @ED date,
    @IR nvarchar(1),
    @ID nvarchar(1),
    @NR int,
    @GP nvarchar(1) = N'N',
    @CUID int = NULL)
RETURNS @TopOrderedMatchingNote TABLE (
    NO int NOT NULL PRIMARY KEY,
    NID    int NOT NULL UNIQUE)
AS
BEGIN


    INSERT INTO @MN (NID)
        SELECT NID
            FROM N
            WHERE GID = @GID
                AND ND >= @FDate
                AND ND <= @TDate
                AND IP = @GP
                AND ((IP = N'Y' AND CUID = @CUID)                           OR (IP = N'N'))
                AND IsDeleted = CASE @IncludeDeleted
                                    WHEN N'N' THEN N'N'
                                    ELSE IsDeleted
                                END;
END;

...snip...

Hope this is helpful and thanks again

You need to show more code than just the parameter definition (and the rest of the parameters seem irrelevant to the problem). Can you post a procedure that reproduces the problem without any reliance on the other parameters or your underlying table structure? You haven't provided enough information for someone else to troubleshoot for you. — Aaron Bertrand, Jun 26 '12 at 01:57
Thanks for the feedback, Aaron. I've posted more code above. — user1481448, Jun 26 '12 at 02:37
Still don't see a stored procedure or some kind of explanation about what goes wrong... — Aaron Bertrand, Jun 26 '12 at 02:49
As a side-note: `nvarchar(1)` really makes no sense at all. If your string is only ever max. of 1 character long - use a `NCHAR(1)` instead. There's no need to incur the at least 2 bytes overhead for a variable length string. `NCHAR(1)` will always occupy 2 bytes of space - `NVARCHAR(1)` is 2-4 bytes, depending on whether something is stored in the string or not. Strings of length 5-10 or less should **not** use the `(n)varchar` datatype - there's really no benefit in doing so. — marc_s, Jun 26 '12 at 05:34

score 1 · Answer 1 · answered Jun 26 '12 at 02:00

Yes, your default parameter declaration example is valid and legal. Here is a quick repro:

USE tempdb;
GO
CREATE PROCEDURE dbo.splunge
   @GP nvarchar(1) = N'N'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT COALESCE(@GP, N'Y');
END
GO

EXEC dbo.splunge;
EXEC dbo.splunge @GP = N'Y';

Results:

----
N

----
Y

If you're having problems getting this to work, you'll need to post more of your code to demonstrate what that means.

Default nvarchar parameter not respected

1 Answers1