1

Without going too much into the situation that has caused the need for this to happen... See towards end of question.

I am calling a stored procedure sp_that_I_dont_controll which may or may not (depending upon the day of the week) accept @p_some_parameter. Is there a way to tell the SQL Parameter object that a given parameter is optional and if the stored procedure doesn't accept a parameter with the given name, just ignore it?

(I'm trying to have @p_some_parameter added as a permenant optional parameter to the query, but I'm looking for a stop gap til then.)

Seems like I will have to go into the background as to why this is needed.

There are three software products that use this stored procedure.

A, B, and C.

A uses it and only works when it has the following parameters.

PROCEDURE [dbo].[sp_example]
  @p_a varchar(8), 
  @p_b varchar(8)

OR

PROCEDURE [dbo].[sp_example]
  @p_a varchar(8), 
  @p_b varchar(8) = NULL

B uses it and only works when it has the following parameter.

PROCEDURE [dbo].[sp_example]
  @p_a varchar(8)

B does not work when the following is used:

PROCEDURE [dbo].[sp_example]
  @p_a varchar(8), 
  @p_b varchar(8) = NULL

This is a problem is product B, but I don't have control over it.

On days when A is being used, the DBA changes it so the stored procedure accepts @p_a and @p_b. On days when B is being used, the DBA changes it so the stored procedure only accepts @p_a.

I want to make C work regardless of which stored procedure (it doesn't need to pass b in to work).

Possible solution:

New stored procedure: rejected, DBA doesn't want me adding more.

Try catch that calls with @p_a and @p_b and if it fails calls only with @p_a: works, but it is an ugly solution I'm trying to avoid.

Check what paramters the current stored procedure accepts and use that to control if I add @p_b or not: works, looks better, but means I'm not using the 'official' data access libraries (honestly, I would love to drop the entire data access library we are using, as it offers 30+ ways to call a stored procedure, the majority of which break when optional sql parameters are added).

Edit 1: Added description of the problem resulting in this need. Edit 2: Added a suggested solution from the comments.

Lawtonfogle
  • 974
  • 4
  • 17
  • 32
  • 1
    sure would help if you would show the existing structure of the current `SP` that you are using.. – MethodMan Dec 01 '14 at 20:53
  • I'm not talking about the stored procedure itself. I'm talking about the SqlParameter object in C#. I can't change the stored procedure. – Lawtonfogle Dec 01 '14 at 20:54
  • 1
    What kind of stored procedure changes it's parameters over time? Or are you saying the parameter is only _required_ under certain conditions? – D Stanley Dec 01 '14 at 20:54
  • like I said it would help to see the see the existing `SP` to help eliminate all kinds of non answers also why not show us what you have tried so that we can gain a better understanding.. what you are trying to do is seriously not that trivial so more effort / help on your part would be truly appreciated in regards to seeing what you currently have – MethodMan Dec 01 '14 at 20:57
  • The DBAs change it depending upon what software that uses it is currently being used. Three products, A, B, and C, call the stored procedure. I can control C, but not A or B. B breaks even if the parameter is added as optional. A only works if it has the parameter (optional or not). The DBA changes it depending upon if A or B is being used. I want C to work regardless of if A or B is in use. – Lawtonfogle Dec 01 '14 at 20:58
  • Sounds like you need to have a parameter(s) setup with Default values set to `Null` then you can pass / provide parameters as well as have your `DBA` as some `ISNUll(` Checks you are being very hesitant for some reason.. showing the `SP` Definition and Structure is not a big secret here.. – MethodMan Dec 01 '14 at 21:01
  • 5
    It might be time to get some new DBAs – LittleBobbyTables - Au Revoir Dec 01 '14 at 21:02
  • Product B breaks when the number of parameters accepted is not what it wants (even if they are optional ones). The developers for it purposefully developed it so that optional parameters aren't allowed. I don't know why. – Lawtonfogle Dec 01 '14 at 21:07
  • Since the problem lies in ProgramC which you can control maybe you can look at sys.parameters. If your "extra" parameter is there, you need to include it in the call, otherwise don't include it in the call. – Sean Lange Dec 01 '14 at 21:12
  • Why don't A and B use different procedures? That would seem to solve many problems. – Mark Peters Dec 01 '14 at 21:22
  • Probably for the same arcane/incomprehensible reasons the developers made it so that B breaks if it detects optional parameters. – Lawtonfogle Dec 01 '14 at 21:25
  • Is there a way to remove the duplicate answer bit at the top? – Lawtonfogle Dec 01 '14 at 21:31
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Dec 01 '14 at 21:50
  • I got here looking for answers around optional parameters in C#. I feel like the question is erroneously marked "C#"? – Mark Feb 14 '18 at 23:21

2 Answers2

1

Sorry, but the fix to this is a human one, not a code one. Your DBA is doing something incredibly bizarre and daft:

On days when A is being used, the DBA changes it so the stored procedure accepts @p_a and @p_b. On days when B is being used, the DBA changes it so the stored procedure only accepts @p_a.

The problem is your DBA. If they want to change it to accept @p_a and optionally @p_b (i.e. it has a default value, most likely null) but simply ignore @p_b, that would be OK - just about. Changing the number of parameters is - and I don't use this lightly - dumb. Tell them to stop doing that.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I agree the root problem is a human one, but I wouldn't blame the DBA for creating this. The developers who wrote A and B so that they have to work this way hold the real blame. The DBA is trying their best to work with horrible software that purposefully breaks when it detects optional parameters. – Lawtonfogle Dec 01 '14 at 21:21
  • 1
    Net necessarily a DBA problem (though I tend to agree). It seems like product B wouldn't handle the optional parameter (maybe it's querying for the parameters). They should really be using two different procedures, IMO – Mark Peters Dec 01 '14 at 21:25
  • 1
    @Lawronfogle I would blame the DBA, because they have not challenged a bad decision, or had the code fixed. A sproc is meant to be an api contract. If you cant preserve the contract, it should not pretend to be one sproc. If the sproc name is hard coded, my first thought is: default schema - i.e. have Foo.Sproc and Bar.Sproc, and run them under different accounts – Marc Gravell Dec 01 '14 at 21:27
  • @MarcGravell Good suggestion using different schemas – Mark Peters Dec 01 '14 at 21:32
  • @MarkPeters That is exactly what it is doing. Querying the parameters and throwing an error if anything unexpected shows up. – Lawtonfogle Dec 01 '14 at 21:34
1

You could query the stored procedure to see which one is installed.

See this answer for a sample of querying parameters.

That should tell you which one is installed and you can build your parameters based on that result.

Community
  • 1
  • 1
Mark Peters
  • 17,205
  • 2
  • 21
  • 17
  • While it would definitely work, that is anathema to regular intended usage / best practice. – Marc Gravell Dec 01 '14 at 21:15
  • 1
    @MarcGravell The whole situation is quite a distance from best practice... I agree that it's really a DBA problem – Mark Peters Dec 01 '14 at 21:20
  • I think this counts as the best technical answer if I can get them to allow me to read this data in code. I agree it is the 'least bad' solution, not a good one. Getting the developers to fix B to allow for stored procedures to have optional parameters is the long term goal. – Lawtonfogle Dec 01 '14 at 21:29