2

The following SQL compiles in Sybase ASE and MS SQL Server without problems:

go
create proc foo
  @foo1 int
as
  select @foo1
go
create proc bar
  @bar1 int
as
  exec foo @foo1=@bar1, @foo2=@bar1
go

It runs without issue in Sybase ASE, but in MS SQL Server complains:

Msg 8144, Level 16, State 2, Procedure foo, Line 11
Procedure or function foo has too many arguments specified.

Is it possible to tell MS SQL Server to ignore this warning? And if not, is it possible to tell MS SQL Server to break when compiling stored procedures that calls other procedures with too many arguments? It is potentially dangerous when we will only learn of these issues when the procedures are actually called.

This code is supposed to work both in Sybase ASE and MS SQL Server, and we have so far been using the luxury of Sybase's relaxed rules, as we are now adding support for MS SQL Server.

As I mentioned below, the problem is not optional parameters. It is prediction about stored procedures. I don't mind solving the issue by only calling stored procedures with the correct number of parameters or adding dummy parameters to stored procedures, I just want MS SQL Server to tell me at compile time, not at runtime. Since MS SQL Server actually verifies whether or not a procedure exists (it gives a warning if it does not, although I'd prefer if it failed like Sybase ASE), why can't it check its parameters at the same time?

Svip
  • 2,958
  • 3
  • 22
  • 33
  • You would have to specify only e.g. two parameters making one of them optional. http://stackoverflow.com/questions/1810638/optional-parameters-in-sql-server-stored-proc – Vojtěch Dohnal Dec 12 '14 at 14:04
  • "why can't it check..." It can, but it doesn't. Unfortunately [deferred name resolution](http://technet.microsoft.com/library/ms190686) is considered a feature rather than a bug. Only on execution does SQL Server try to produce a query plan, and only then does it see the call is invalid. That you get a warning at all is a courtesy. – Jeroen Mostert Dec 12 '14 at 14:58
  • Well, this conversion is going to be a lot of fun then. – Svip Dec 18 '14 at 09:53

2 Answers2

2

No, it is not possible.

I suggest you start using SSDT - SQL Server Data Tools - to run SQL compilation checks on your code. It is part of Visual Studio.

TomTom
  • 61,059
  • 10
  • 88
  • 148
0

No, you can't do that. If it can have one or two parameters then use an optional parameters with default values:

create proc foobar
  @foo1 int = null,
  @bar1 int = null

You would have to handle code execution paths based on the params provided. Then you can do this:

exec foobar -- with no params, both default to null

Or:

exec foobar 1 -- just a foo param, bar defaults to null

Or:

exec foobar 1,2 -- with both params

Or:

exec foobar @bar1 = 2 -- with a specified param, other param defaults to null
Tanner
  • 22,205
  • 9
  • 65
  • 83
  • Let me give you a counter example: We have a number of report stored procedures. Our report server calls the SQL database with a set of parameters, including a common parameter, that *most* of the report stored procedures, but not all, since it is not necessary for all. So either I have to create dummy parameters for a lot of stored procedures or have my report server check whether the stored procedures actually take these parameters. But that's just a minor set of our stored procedures, we have over 2000 stored procedures. – Svip Dec 12 '14 at 14:18
  • @Svip: that's not so much a counterexample as it is a reason why you have a lot of work ahead of you. SQL Server simply doesn't allow you to call procedures with parameters they don't accept. Even worse, there is no simple way of determining what parameters a stored procedure *will* accept -- you can get the definition from `sys.sql_modules` and search on that, though. Another option is to unify the set of all parameters, then create all stored procedures with the exact same parameter list -- this ought to be automatable. – Jeroen Mostert Dec 12 '14 at 15:05