SQL Server debugging

Question

I want to ask if there is a possibility to use some debugging option in the program?

Before when I worked with Visual Studio I can use the F5 key to start debugging. Can I do it also with SQL?

When I'm trying to solve some query or see the steps of the program behind I want to see steps through the running process

In SSDT yes, in SSMS no (the debugger was removed in SSMS 18). — Thom A
– Thom A ♦, Commented Dec 7, 2022 at 13:44
Even when it was included, setting it up and actually getting it to work typically required a good bit of hair pulling, even for a local server, let alone a remote one. It's less frustrating and more reliable to break your queries up into steps yourself and write them so that they're easily inspected in parts through the use of CTEs. The only thing you're missing then is parameter values when a stored procedure is executed, which you can get through either client-side logging or a profiler/extended events trace. — Jeroen Mostert
– Jeroen Mostert, Commented Dec 7, 2022 at 15:08

Philip Kelley · Accepted Answer · 2022-12-07 15:24:25Z

1

By and large, the style of debugging where you walk through code, manually advancing execution line by line, with breakpoints and windows showing current variable contents, is a poor fit for database work. Done properly it means creating and holding locks as you go through your code, and depending on environment (none of us would ever do this on Production, of course) that could cause serious blocking problems. Also, viewing the contents of temp tables can be highly problematic.

As others have and probably will say, you are better off manually setting input values and running selected chunks of code at a time, with PRINT and SELECT statements showing what's going on at any given point. This can be slow, a bit awkward, and very effective.

answered Dec 7, 2022 at 15:24

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Meyssam Toluie · Accepted Answer · 2022-12-07 15:11:06Z

I used to print message all over the procedures with a format in this procedure. Also to prevent sending message to application. It checks whether it is production server or not. (using a setting table).

CREATE PROCEDURE dba.Usp_Util_TraceLog
    @ProcId INT, @Message NVARCHAR(4000) = '', @Param0 NVARCHAR(255) = NULL, @Param1 NVARCHAR(255) = NULL, @Param2 NVARCHAR(255) = NULL, @Param3 NVARCHAR(255) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF (mon.fn_Setting_Get('is_production_envirement') = 1)
        RETURN;

    SET @Param0 = ISNULL(@Param0, '');
    SET @Param1 = ISNULL(@Param1, '');
    SET @Param2 = ISNULL(@Param2, '');
    SET @Param3 = ISNULL(@Param3, '');

    SET @Message = REPLACE(@Message, '{0}', @Param0);
    SET @Message = REPLACE(@Message, '{1}', @Param1);
    SET @Message = REPLACE(@Message, '{2}', @Param2);
    SET @Message = REPLACE(@Message, '{3}', @Param3);

    DECLARE @proc_name sysname;
    DECLARE @schema_name sysname;
    SELECT @proc_name = o.name, @schema_name = s.name
    FROM sys.objects AS o
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    WHERE o.object_id = @ProcId;
    
    DECLARE @msg NVARCHAR(4000)= CONCAT('[', @schema_name, '].[', @proc_name, ']: ', @Message)

    PRINT @msg;
END;
GO

Collectives™ on Stack Overflow

SQL Server debugging

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related