SQL Server debugging-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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