check if stored procedure is valid

Question

I have a stored procedure (sproc A) which is syntactically correct. So when I hit "run" on its create or alter statement, it is saved into the database.

However, sproc A has a call to another stored procedure (sproc B). It does not provide enough parameters for sproc B, so I don't see how it's a valid stored procedure.

I want to detect any stored procedures in my database which aren't passing enough parameters to their own stored procedures.

Thankyou, Fidel

I believe "deferred name resolution" is the name for this sinful concept — Fidel, Jan 25 '12 at 09:40

score 5 · Accepted Answer · edited May 23 '17 at 12:25

5

Unfortunately, there is no mechanism in SQL Server to test dependencies, parameters etc

You have to search+check, or provide defaults for parameters. You'll only pick it up by testing otherwise.

A good auto complete tool like Red Gate SQL prompt can list parameters + types for you

Note:

It's a long standing problem and there is even a request to MS including this. SP parameter checking is one of the OPTION STRICT suggestions

edited May 23 '17 at 12:25

Community

1
1

answered Jun 15 '11 at 13:46

gbn

422,506
82
585
676

thanks for the info on OPTION STRICT, it's a shame that it was requested 4 years ago... – Fidel Jun 16 '11 at 00:21

check if stored procedure is valid

1 Answers1