July 15, 2019 at 8:47 pm
I'm checking to see if anyone has already written a stored procedure that can be run to identify any and all stored procedure calls from within a trigger or other stored procedure that are missing some of their input parameters.
For example, if I have a procedure named SP_MyProc and three input parameters whenever I call this procedure from a trigger or another stored procedure, I must do so with three parameters specified. Example - execute sp_myproc 'a', 'b', 'c' is correct and would be ignored, but execute sp_myproc 'a', 'b' is incorrect since there's only two items specified instead of 3 and would display in the results of the procedure.
July 15, 2019 at 8:51 pm
Doing that in a stored procedure? Ouch! Lot's of string parsing to do. Not fun in T-SQL.
Have a look at SSDT (SQL Server Data Tools). It will find such errors.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 15, 2019 at 10:12 pm
Thanks. I'm not familiar with SQL Server Data Tools. Is this a part of SQL Management Studio or something optional?
July 16, 2019 at 1:42 am
It's a free added development tool that you can install - it replaced BIDS/Business Intelligence Studio Designer. You can download it from this link:
Download and install SQL Server Data Tools (SSDT) for Visual Studio
Sue
July 16, 2019 at 7:24 am
SSDT is a free add-in to the Visual Studio shell. It is somewhat confusing. Sue says that it replace BIDS, and that is true, but SSDT has two faces. Originally, it was just a way to build database applications in Visual Studio, but they put the BIDS stuff under the same umbrella.
Anyway, once you have it installed, open the SQL Server Explorer (I think that is what is called), find your database, right-click and create a project from it. One you have the project, you can "build" it, and there should be error or warnings for things like missing arguments in SP calls. By default, only the 200 errors are shown. This can be changed, but I don't recall where.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 16, 2019 at 10:04 am
also, it's possible to trace Exceptions and User Errors returned to a client using Profiler
see below :
https://www.mytecbits.com/microsoft/sql-server/trace-errors-and-exceptions-using-profiler
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply