none
How to call a stored proc with optional parameters using sp_executesql

    Question

  • I have a stored proc declared as follows:

    create procedure usp_test
        @p int = -1
    as
    select @p as result
    go

    I can call it like this, which returns the expected result of -1:

    exec usp_test default

    I want to be able to do the same (i.e. call the proc with the paramater's default value) using sp_executesql, so i do this:

    exec sp_executesql N'usp_test @p', N'@p int', @p=default

    and what I get is this:

    Msg 8178, Level 16, State 1, Line 0
    The parameterized query '(@p int)usp_test @p' expects the parameter '@p', which was not supplied.

    Am I missing something, or this just cannot be done using sp_executesql?

    Background: This question originated from me looking into the possibility of calling a stored proc with optional parameters from Entity Framework using the Database.ExecuteSqlCommand method. If you use SqlCommand object and set the parameter value to null in C#, the actual sql executed on the database will be exec usp_test default. But EF's ExecuteSqlCommand method uses sp_executesql behind the scenes and the actual sql is exec sp_executesql N'usp_test @p', N'@p int', @p=default which causes an error.

    Thursday, September 26, 2013 1:20 AM

Answers

  • If I do NOT use sp_executesql, I have two options to use the param's default value:

    exec usp_test

    or

    exec usp_test default

    Do I have the second option with sp_executesql?

    As far as I know, you can't do what you want.  That is, there is nothing you can use for <value> in the following that will use @p's default value in the call to the stored procedure.

    exec sp_executesql N'usp_test @p', N'@p int', N'@p = <value>'

    Perhaps you could try asking this question in an Entity Framework forum.  Maybe you can find someone who has had this problem and has a workaround that works in EF.

    Tom

    • Marked as answer by Andrew-72 Thursday, September 26, 2013 1:53 PM
    Thursday, September 26, 2013 1:49 PM

All replies

  • Hi,

    I think you can directly call the SP instead of calling it dynamically.


    Many Thanks & Best Regards, Hua Min

    Thursday, September 26, 2013 1:31 AM
  • Thanks Hua, I know I can call an SP directly. The question is specifically about the ability to DYNAMICALLY call an SP with optional parameters and make the SP use the paramaters' default values.
    Thursday, September 26, 2013 1:55 AM
  • Try

    use test
    go
    create procedure usp_test
        @p int = -1
    as
    select @p as result
    go
    use test
    go
    declare @p int
    exec sp_executesql N'usp_test @p', N'@p int', @p=@p



    Many Thanks & Best Regards, Hua Min


    Thursday, September 26, 2013 2:12 AM
  • Thanks, but your code returns NULL, because the value of the variable @p is NULL. The result we want to get is the default value of the @p input parameter, which is -1.
    Thursday, September 26, 2013 2:23 AM
  • Try

    use test
    go
    DROP procedure usp_test
    go
    create procedure usp_test
        (@p int output)
    as
    BEGIN
    SET @P=-1
    select @p as result
    END
    go
    use test
    go
    declare @p int
    exec sp_executesql N'usp_test @p', N'@p int output', @p=@p output



    Many Thanks & Best Regards, Hua Min



    Thursday, September 26, 2013 2:43 AM
  • Well, there are a few problems here:

    1. The stored proc's @p parameter is not optional anymore, which defeats the purpose.
    2. The stored proc always returns -1, while it's suposed to return what i passed in, and -1 if did not pass in anything.
    3. The call to sp_executesql will throw an error. Please test before posting.
    Thursday, September 26, 2013 3:04 AM
  • No, the point is: if you want to assign anything to the parameter within the SP, you have to declare it as Output parameter.

    Many Thanks & Best Regards, Hua Min

    Thursday, September 26, 2013 3:08 AM
  • >  if you want to assign anything to the parameter within the SP, you have to declare it as Output parameter

    I do not want to assign anything to the parameter inside the stored proc, I want the paramater to have a default value (which will make it optional), and I want to know whether there's a way to call such a stored proc using sp_executesql.

    Thursday, September 26, 2013 3:35 AM
  • >  if you want to assign anything to the parameter within the SP, you have to declare it as Output parameter

    I do not want to assign anything to the parameter inside the stored proc, I want the paramater to have a default value (which will make it optional), and I want to know whether there's a way to call such a stored proc using sp_executesql.


    You can have such logic when you're calling the SP dynamically, like:
    if the Output parameter of the SP is returning null, then you put the default value for it.

    Many Thanks & Best Regards, Hua Min

    Thursday, September 26, 2013 4:45 AM
  • Can you please provide the sql code for this - a stored proc with an optional parameter, and a call to it using sp_executesql.
    Thursday, September 26, 2013 4:54 AM
  • Try

    use test
    go
    
    DROP procedure usp_test
    go
    
    create procedure usp_test
        (@p int output)
    as
    BEGIN
    select @p as result
    END
    go
    
    use test
    go
    
    declare @p int
    exec sp_executesql N'usp_test @p', N'@p int output', @p=@p output
    
    if (@p is null)
    	SET @P=-1
    select @p
    


    Many Thanks & Best Regards, Hua Min

    Thursday, September 26, 2013 4:58 AM
  • You may try as below:

    --With default value
    declare @p int
    exec sp_executesql N'usp_test ', N'@p int =DEFAULT' 
    
    
    --Without default value
    --declare @p int
    exec sp_executesql N'usp_test @p', N'@p int =DEFAULT',@p = 2


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 5:08 AM
  • If you want the parameter to take the default value and you are using sp_executesql, don't pass the parameter.  For example,

    create procedure usp_test
        @p int = -1
    as
    select @p as result
    go
    
    exec sp_executesql N'usp_test'
    go
    drop proc usp_test

    If you have multiple parameters, and you want some of them to use the default value and others you want to pass a real value, then use the same sort of idea.  Don't pass the ones you want to default, and for the ones you want to give a value to, pass those parameters by name, for example

    create procedure usp_test
        @i int,
        @p int = -1,
    	@j int = -75
    as
    select  @i, @j, @p as result
    go
    
    exec sp_executesql N'usp_test @i=@i, @j=@j', N'@i int, @j int', @i=7, @j=3
    
    go
    drop proc usp_test

    Tom

    Thursday, September 26, 2013 5:27 AM
  • Thanks Latheesh and Tom, you pretty much gave the same answer - if you want a parameter to use its default value, do not pass it at all. I guess I'll have to modify my question then. When you call sp_executesql:

    • The parameterised sql string must include all paramaters. In my example it is N'usp_test @p'.
    • The parameter declaration string must include all parameters. In my example it is N'@p int'.
    • Setting parameter values must include all paramaters. In my example it is @p=<value>. You can only change the value.

    So for my original example, the sql string will be as follows:

    exec sp_executesql N'usp_test @p', N'@p int', @p=<value>

    and the question is - what should I put into the <value> to make the stored proc use the default.

    If I do NOT use sp_executesql, I have two options to use the param's default value:

    exec usp_test

    or

    exec usp_test default

    Do I have the second option with sp_executesql?

    Thursday, September 26, 2013 5:50 AM
  • Using sp_executeSQL, its not so straight forward. You may try the below variation as per your req:

    --With default value
    declare @p int
    exec sp_executesql N'usp_test @p =DEFAULT', N'@p int =DEFAULT' 
    
    
    --Without default value
    --declare @p int
    exec sp_executesql N'usp_test @p', N'@p int =2'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 6:21 AM
  • Hi Latheesh,

    In your "With default value" example, the N'@p int =DEFAULT' part is redundant, it does not do anything. I can execute

    execsp_executesql N'usp_test @p =DEFAULT',N'@p int =55'

    and will still get -1, not 55.

    The requirements come from my original post - Entity Framework builds the sql string which I cannot control. I only set the parameter values. If I set a param value to null in c#, it becomes default in SQL. The word default works in this:

    exec usp_test default

    but not int this:

    exec sp_executesql N'usp_test @p', N'@p int', @p=default

    Thursday, September 26, 2013 7:00 AM
  • If I do NOT use sp_executesql, I have two options to use the param's default value:

    exec usp_test

    or

    exec usp_test default

    Do I have the second option with sp_executesql?

    As far as I know, you can't do what you want.  That is, there is nothing you can use for <value> in the following that will use @p's default value in the call to the stored procedure.

    exec sp_executesql N'usp_test @p', N'@p int', N'@p = <value>'

    Perhaps you could try asking this question in an Entity Framework forum.  Maybe you can find someone who has had this problem and has a workaround that works in EF.

    Tom

    • Marked as answer by Andrew-72 Thursday, September 26, 2013 1:53 PM
    Thursday, September 26, 2013 1:49 PM
  • Thanks Tom, I was pretty sure this would be the answer.

    Thursday, September 26, 2013 2:00 PM
  • First,

    It's a little easier to understand what's going on if you don't reuse @p for both the procedure's parameter and the batch's parameter.  Eg this:

    create procedure usp_test
        @p int = -1
    as
    select @p as result
    go
    exec sp_executesql N'usp_test @p=@q', N'@q int = 23', @q=default

    outputs 23, since that's the default for the parameter @q.  That is then passed to the parameter @p in the stored procedure.

    Second, I'm confused on what the EF issue is.  On EF 5 and 6 I don't see the behavior you describe.  The calls are direct RPC calls, not sp_executesql, and passing a null correctly passes null to the procedure; it is not replaced by 'default'.

    EF doesn't codegen overload functions in your DbContext for optional parameter sets, but you can.  

            public virtual ObjectResult<Nullable<int>> usp_test()
            {
    
                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<int>>("usp_test");
            }
    
            public virtual ObjectResult<Nullable<int>> usp_test(Nullable<int> p)
            {
                var pParameter = p.HasValue ?
                    new ObjectParameter("p", p) :
                    new ObjectParameter("p", typeof(int));
        
                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<int>>("usp_test", pParameter);
            }

    And if you're calling dynamically instead of through codegen, you can just omit the parameter to get the default:

            public static void Main()
            {
                using (var con = new AdventureWorks2012Entities1())
                {
                    var i = con.usp_test(null).First();
                    var j = con.usp_test().First();
    
                    var k = con.Database.SqlQuery<int?>("usp_test").First();
                    Console.Write(i);
                }
            }

    David


    David http://blogs.msdn.com/b/dbrowne/




    Thursday, September 26, 2013 2:36 PM
  • Hi David,

    Yes stored proc wrappers autogenerated by EF will not support optional parameters, and also the sql they will produce will not contain sp_ececutesql. I'm not using them though. What I'm using is DbContext.Database.ExecuteSqlCommand. This produces sql like exec sp_executesql N'usp_test @p', N'@p int', @p=default when I set the value of @p to null in C#, which makes SQL server throw an error that parameter @p was not passed.

    Thursday, September 26, 2013 2:55 PM
  • Ok.  Database.ExecuteSqlCommand shouldn't really be doing that.  What appears to be happening is that if you pass DbNull.Value, it will pass a null to the stored procedure.  And if you pass null, then it will try to pass the default.  But since it uses sp_executesql, instead of a direct RPC it fails to pass the default to the stored procedure.

    Still, you can simply omit the parameter if you want the default.  eg

    con.Database.ExecuteSqlCommand("usp_test");

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 26, 2013 3:07 PM
  • Yes, C#'s DbNull.Value becomes null, and C#'s null becomes "default". Not sure what you mean by "But since it uses sp_executesql, instead of a direct RPC it fails to pass the default to the stored procedure". It does pass what it's supposed to pass - in the resulting SQL the value of the parameter is "default", exactly what I expect it to be if I set it to null in C#. The problem is, sp_executesql does not seem to understand the word "default".

    E.g. if a call this sored proc using SqlCommand.ExecuteReader(), i'll get exec usp_test @p=default, which will execute as expected without errors.

    Thursday, September 26, 2013 3:16 PM
  • sp_executesql understands defaults, but it's a extra layer between your calling code and the stored procedure.  The parameter definition in sp_executesql can contain defaults, but those are different than the stored procedure defaults.

    eg

    create procedure usp_test
       
    @p int = -1
    as
    select @p as result
    go
    exec sp_executesql N'usp_test @p=@q', N'@q int = 23', @q=default

    But the larger point is that it's easy to call stored procedures with optional parameters by simply not passing those parameters.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 26, 2013 3:48 PM
  • Thanks Hua, I know I can call an SP directly. The question is specifically about the ability to DYNAMICALLY call an SP with optional parameters and make the SP use the paramaters' default values.
    Have you ever had a course in basic software engineering? Look up coupling and cohesion. 

    Cohesion:
    This is how well a module does one and only one thing; that it is logically coherent. The modules should have strong cohesion. You ought to name the module in the format "<verb><object>", where the "<object>" is a specific logical unit in the data model. There are several types of cohesion. We rank them going from the worst form of cohesion to the best

    1) Coincidental 
    2) Logical
    3) Temporal
    4) Procedural
    5) Communicational
    6) Informational 
    7) Functional 

    Coupling:
    If modules have to be used in a certain order, then they are strongly coupled. If they can be executed independently of each other and put together like Lego blocks, then they are loosely or weakly coupled. There are several kinds of coupling, which are ranked from worse to best: 

    1) Content 
    2) Common 
    3) Control 
    4) Stamp 
    5) Data 
     
    This is covered briefly in a chapter on writing stored procedures in my book on SQL PROGRAMMING STYLE. In the meantime, you can read DeMarco, Yourdon, Constantine, Myers or several other of the pioneers. 

    This is FAR more basic than SQL programming. This is what you are supposed to know before you write any code in any language. Your approach to programming is wrong. Your code changes in a local context at run-time! This is called "Automoiblea, Sqiuds and Laddy GaGa" coding.
     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 26, 2013 3:55 PM
  • > it's easy to call stored procedures with optional parameters by simply not passing those parameters

    Of course, but I was hoping to have a C# function that accepts all possible parameters, and setting those that I do not want to pass to null.

    > exec sp_executesql N'usp_test @p=@q', N'@q int = 23',@q=default

    this will make the stored proc execute with @p = 23, not -1. You can change the last assignment to @q = <anything>, or even romove it, the proc will still use 23.

    Thursday, September 26, 2013 4:01 PM
  • CELKO - this would be the most polite way I could possibly reply to your post: go write comments on some forum for people who write books about programming, this is a forum for people who actually do programming.
    Thursday, September 26, 2013 4:08 PM
  • >Of course, but I was hoping to have a C# function that accepts all possible parameters, and setting those that I do not want to pass to null.

    Ok.  Then write one.

        public partial class AdventureWorks2012Entities1 : DbContext
        {
            public void usp_test(int? p, int? q)
            {
                var parameters = new List<SqlParameter>();
                if (p.HasValue)
                {
                    var param = new SqlParameter("@p", SqlDbType.Int);
                    param.Value = p.Value;
                }
                if (q.HasValue)
                {
                    var param = new SqlParameter("@q", SqlDbType.Int);
                    param.Value = q.Value;
                }
                this.Database.ExecuteSqlCommand("usp_test", parameters.ToArray());
    
            }
        }

    >You can change the last assignment to @q = <anything>, or even romove it, the proc will still use 23.

    No.

    exec sp_executesql N'usp_test @p=@q', N'@q int = 23', @q=12

    outputs:

    result
    -----------
    12

    The point is that the parameter list of sp_executesql defines the default, and that's different that the parameter list of the stored procedure.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, September 26, 2013 4:17 PM
  • David,

    Regarding execsp_executesql N'usp_test @p=@q',N'@q int = 23', @q=12, yes you're right it'll return 12 not 23, my mistake.

    Regarding the C# function - close, but usp_test will never use the parameter values you'll be trying to pass to it. Instead of this:

    this.Database.ExecuteSqlCommand("usp_test", parameters.ToArray());

    you need to have this if you're passing both parameters:

    this.Database.ExecuteSqlCommand("usp_test @p, @q", parameters.ToArray());

    or this if you're passing e.g. @q only:

    this.Database.ExecuteSqlCommand("usp_test @q", parameters.ToArray());

    So yes it can be done, but along with dynamically building the list of parameters, you'll also have to dynamically build the command string. Becomes messy. I eventually just used the SqlCommand object and the connection which you can get from DbContext.

    Thursday, September 26, 2013 4:33 PM
  • >I eventually just used the SqlCommand object and the connection which you can get from DbContext.

    That's probably better.  You can also then use CommandType.StoredProcedure and it will create parameter markers only for the parameters you configure.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 26, 2013 5:54 PM
  • I think I can safely say that I have been a paid programmer longer than you have :) If you are under 40, I have  been a paid programmer longer than you have been alive. In those decades, I have had to correct bad code, train programmers with your attitude (or fire them) and supervise teams. How many million lines of code do you have in your portfolio? 

    Bluntly, you do not know how to program. You kludge, not code. You are missing fundamentals. Instead of explaining what you want, you decide how to do it (“I do not want to assign anything to the parameter inside the stored proc, I want the parameter to have a default value”). 

    You can get the education from some world class experts for free in forums. With your attitude, you will not actually learn anything and stay the IT version of “Larry the Cable Guy” when you could become a professional. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 26, 2013 8:20 PM