Calling SP with optional parameters - assignment not happening correctly...

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hi All...

    I have a question regarding calling SP with optional parameters...

    I have created a SP with definition like this

    CREATE PROCEDURE [dbo].[GetPerson] @RecordNumber VARCHAR(16) = NULL ,

    @PerNumber VARCHAR(9) = NULL ,

    @PerType VARCHAR(2) = NULL ,

    @ActiveOnly BIT =null ,

    @ActiveDate datetime =null

    I need to be able to call this SP in two ways

    either by passing RecordNumber i.e. first parameter

    or by passing PerNumber/PerType combination.. i.e. 2nd parameter and 3rd paramater

    The last 2 paramaters are applicable in both calls

    But when I call this SP like this

    exec [dbo].[GetPerson] '123',1,'10/10/2013'

    it does assignment like this

    RecordNumber = '123'

    PerNumber = 1

    PerType = 10

    instead it should be assigning like this:

    RecordNumber = '123'

    Active= 1

    ActiveDate= 10/10/2013

    I don't get any results....

    Can anyone please suggest something on how can I call or take care of optional parameters so that i calls properly based on parameters passed....

    CREATE TABLE [Person](

    [RecNo] [char](16) NOT NULL,

    [PerNo] [char](9) NOT NULL,

    [Pertype] [char](2) NOT NULL,

    [Active] [int] NULL,

    [ActiveDate] [datetime] NULL


    INSERT INTO [Person]

    SELECT '123','11','01',1,'2013-10-10'


    SELECT '345','11','02',1,'2013-11-10'


    SELECT '456','12','01',1,'2013-9-10'


    SELECT '789','12','02',1,'2013-8-10'


    SELECT '234','13','01',1,'2013-9-9'


    SELECT '678','13','02',1,'2013-8-8'


    SELECT '1234','15','01',0,'2013-9-9'


    SELECT '1678','15','02',0,'2013-8-8'

    select * from Person

    here is SP create statement:

    CREATE PROCEDURE [GetPerson] @RecordNumber VARCHAR(16) = NULL ,

    @PersonNo VARCHAR(9) = NULL ,

    @PersonType VARCHAR(2) = NULL ,

    @ActiveOnly BIT = NULL ,

    @ActiveDate datetime = NULL



    print 'hello'

    SET @ActiveDate = ISNULL(@ActiveDate, convert(varchar(10), GETDATE(),101) )

    SET @ActiveOnly = ISNULL(@ActiveOnly, 0)

    IF (@RecordNumber IS NULL AND @PersonNo IS NULL AND @PersonType IS NULL)

    OR (@PersonNo IS NULL AND @PersonType IS NOT NULL)

    OR (@PersonNo IS NOT NULL AND @PersonType IS NULL)


    print 'go ifff line'

    --Get info




    print 'go else line'

    print '@RecordNumber = ' + @RecordNumber

    print '@PersonNo = ' +@PersonNo

    print '@PersonType = ' +@PersonType

    print '@ActiveOnly = ' + cast(@ActiveOnly as varchar)

    print '@ActiveDate = ' + cast(@ActiveDate as varchar)

    -- this is how i am sing the 3 optinal paramaters to get data from person table


    FROM Person

    WHERE (@RecordNumber IS NULL OR RecNo = @RecordNumber)

    AND (@PersonNo IS NULL OR PerNo = @PersonNo)

    AND (@PersonType IS NULL OR Pertype = @PersonType)

    AND Active = @ActiveOnly

    AND ActiveDate=@ActiveDate



    print 'go return'




    ANY help on this is Appreciated.....


    [font="Comic Sans MS"]

    Thanks [/font]

  • sestell1


    Points: 10230

    You can call the procedure like this:

    exec [dbo].[GetPerson] @RecordNumber = '123', @ActiveOnly = 1, @ActiveDate = '10/10/2013'

  • sql_learner29

    SSCrazy Eights

    Points: 9107


    No , I can't do this, as its been called from a DOT NET program and they are just passing parameters and calling it...

    Any other way to handle the optional parameters inside...


    [font="Comic Sans MS"]

    Thanks [/font]

  • sestell1


    Points: 10230

    You can include Nulls for the two parameters not being used when you call it.

    If that's not possible either, then I think you'll need to write another stored procedure as a wrapper with just the parameters you want, and have that one call the original procedure adding in the null parameters or naming the parameters as above.

  • sestell1


    Points: 10230

    I suppose you could change your procedure to have local variables, and then try to add logic that would guess which parameters get mapped to which local variables based on the number of non-null parameters provided... but that's just yucky, unreliable, and a maintenance nightmare.

  • Ed Wagner

    SSC Guru

    Points: 286982

    I have .NET pages that call stored procedures where I specify the parameter values by name and they work with no problems. Since some parameters are optional and have default values, you should specify the ones you want to pass by name and not rely on ordinal.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Looks like you need to test for the number of parameters that are NOT NULL and then redistribute the values based on that number (either 3 or 4 of them will be NOT NULL)

    Note also that you need to "genericize" your parameters to be able to contain all data that might be in them. From the way I see it you need these parameters:

    @Param1 VARCHAR(16) = NULL ,

    @Param2 VARCHAR(9) = NULL ,

    @Param3 VARCHAR(30) = NULL ,

    @Param4 VARCHAR(30) = NULL

    Note I have changed the size of Param3 and 4 because they MIGHT contain a long string that would be a DATETIME.

    Inside your sproc you will need to declare and assign the 'actual' variables to be used by your logic:

    @RecordNumber VARCHAR(16),

    @PerNumber VARCHAR(9),

    @PerType VARCHAR(2),

    @ActiveOnly BIT ,

    @ActiveDate datetime

    If you have 3 NON-NULL input parameters then your assignments will go like this:

    SELECT @RecordNumber = @Param1, @ActiveOnly = CAST(@Param2 as bit), @ActiveDate = CAST(@Param3 as datetime)

    Otherwise this:

    SELECT @PerNumber = @Param1, @PerType = @Param2, @ActiveOnly = CAST(@Param3 as bit), @ActiveDate = CAST(@Param4 as datetime)

    Hopefully that makes sense! 😎

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Mark Eckeard

    Hall of Fame

    Points: 3453

    What Ed said above.

    Check your values in your .net app. If present, add their corresponding params to the sproc call and that'll work.


Viewing 8 posts - 1 through 8 (of 8 total)

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