CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkstored-procedures
Jacek
Jacek

Reputation: 12053

Passing nullable guid to stored procedure

When I pass nullable guid parameter to stored procedure in my c# code below. I am getting exception:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@customerGuid'.

var userNameSqlParam = new SqlParameter
{
    ParameterName = "userName",
    Value = userName,
    DbType = DbType.String
};

var customerGuidSqlParam = new SqlParameter
{
    ParameterName = "customerGuid",
    Value = customerGuid,
    DbType = DbType.Guid
};

var rows = _dbContext.Database
    .SqlQuery<CurrentUserContextWithoutCustomer>
        ("EXEC [dbo].[GetCurrentUserContext] @userName @customerGuid",
        userNameSqlParam, customerGuidSqlParam)
    .ToList();

So when I look by SQL Profiler what was called I get

exec sp_executesql 
N'EXEC [dbo].[GetCurrentUserContext] @userName @customerGuid',
N'@userName nvarchar(4),@customerGuid uniqueidentifier',
@userName=N'gir1',@customerGuid=default

I see default on the end of query. It generates problem. There is header of stored procedure

ALTER PROCEDURE [dbo].[GetCurrentUserContext]
    @userName nvarchar(100),
    @customerGuid uniqueidentifier
AS

How can I pass nullable guid to sql as stored procedure parameter.

Upvotes: 2

Views: 7943

Answers (3)

Hamid Pourjam
Hamid Pourjam

Reputation: 20754

First of all you should send a DBNull.Value instead of null

var customerGuidSqlParam = new SqlParameter
{
    ParameterName = "customerGuid",
    Value = (object)customerGuid ?? (object)DBNull.Value,
    DbType = DbType.Guid
};

and you have missed a comma in your query.

var rows = _dbContext.Database
    .SqlQuery<CurrentUserContextWithoutCustomer>
        ("EXEC [dbo].[GetCurrentUserContext] @userName, @customerGuid",
        userNameSqlParam, customerGuidSqlParam)
    .ToList();

Upvotes: 6

Md. Tazbir Ur Rahman Bhuiyan
Md. Tazbir Ur Rahman Bhuiyan

Reputation: 2032

you can put this in your C# end

Value = customerGuid == null ? (object)DBNull.Value : customerGuid.Value

and at stored procedure end as suggested from the earlier answer:

@customerGuid uniqueidentifier = NULL

When there is null, it will pass null. if there is value you will get it in @customerGuid param.

Upvotes: 2

Anonymous Duck
Anonymous Duck

Reputation: 2978

In your stored procedure set the default value to null

ALTER PROCEDURE [dbo].[GetCurrentUserContext]
    @userName nvarchar(100),
    @customerGuid uniqueidentifier = NULL
AS

Upvotes: 2

Related Questions