MSSQL - Function to generate empty rows

There the text of inline table-value function to generate a specified count of empty rows:

IF OBJECT_ID(N'dbo.GenerateRows') IS NOT NULL
  DROP FUNCTION dbo.GenerateRows;
GO
CREATE FUNCTION dbo.GenerateRows (@Count INT)
/*
https://yabele.blogspot.com/2017/07/mssql-function-to-generate-empty-rows.html
*/
RETURNS TABLE
AS
RETURN
WITH TenRows AS (
  SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
)
SELECT TOP(@Count)
  I = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM
  TenRows T1 CROSS JOIN TenRows T2 CROSS JOIN TenRows T3 CROSS JOIN TenRows T4 CROSS JOIN TenRows T5
/*
SELECT * FROM dbo.GenerateRows(100);
*/
;

MSSQL - How to generate range of Dates

There the text of VIEW to generate range of dates between 1.January 2000 and 31.December next year:

IF OBJECT_ID(N'dbo.calendar_dates') IS NOT NULL
  DROP VIEW  dbo.calendar_dates;
GO
/*
http://yabele.blogspot.de/2016/08/how-to-generate-range-of-dates.html
*/
CREATE VIEW dbo.calendar_dates
AS
WITH
  R10 AS (
    SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
  )
SELECT TOP(DATEDIFF(DAY, '2000-01-01', CAST(YEAR(GETDATE()) + 1 AS CHAR(4)) + '-12-31'))
  date_value = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT 1)), '1999-12-31')
FROM
  R10 T1 CROSS JOIN R10 T2 CROSS JOIN R10 T3 CROSS JOIN R10 T4 CROSS JOIN R10 T5 CROSS JOIN R10 T6
/*
SELECT *
FROM dbo.calendar_dates
ORDER BY date_value;
*/
;

MSSQL - How to replicate to readonly database?

First, I have to apologize, title of article lies - it is unfortunately impossible to set up replication into the READ-ONLY database.
One of the reasons why we want to have the target database read-only, is the need to avoid direct changes of data in it.
For this purpose, we can use another method - Triggers, which prohibit any changes except changes from the replication.

There such trigger:

USE MySourceDB
GO
ALTER TRIGGER dbo.trig_mytable_CheckContext4Changes ON dbo.MyTable
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
AS
IF NOT (@@SERVERNAME = 'MySourceServer' AND DB_NAME = 'MySourceDB')
    ROLLBACK;

Very important is this expression: NOT FOR REPLICATION.
On the target database at the same time we have to
- prohibit all direct changes (INSERT/UPDATE/DELETE)
- but allow data changes which are comming from replication
Expression NOT FOR REPLICATION deactivates trigger for changes from replication.

MSSQL - Immediately PRINT big messages - more then 8000 bytes (4000 unicode characters)

Transact-SQL function PRINT has two disadvatages:
- is limited for 8000 bytes - 8000 VARCHAR characters or 4000 NVARCHAR (unicode) characters
- does not send messages immediately to the client, usually user have to wait until the procedure is complete before seeing messages

We can use alternate method - the RAISERROR function, this function can send messages immediately, but is limited for 2047 characters.

There the stored procedure which send any messages immediately to client, also with length more then 2047 (or 8000) characters:

CREATE PROCEDURE [dbo].[sp_print_big_message]
    @message NVARCHAR(MAX)
AS BEGIN

    -- SET NOCOUNT ON to prevent extra log messages
    SET NOCOUNT ON;

    DECLARE
        @CRLF            CHAR(2) = CHAR(13)+CHAR(10),
        @message_len    INT = LEN(@message),
        @i                INT,
        @part            NVARCHAR(2000),
        @part_len        INT;

    IF @message_len <= 2000 BEGIN
        -- Message ist enough short
        RAISERROR (@message, 0,1) WITH NOWAIT;
    END ELSE BEGIN
        -- Message is too long
        SET @i = 1;
        WHILE @i < LEN(@message) BEGIN
            -- Split to parts end send them to client immediately
            SET @part = SUBSTRING(@message, @i, 2000);
            SET @part_len = 2000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(@part)) - 1;
            SET @part = CASE @i
                            WHEN 1
                            THEN ''
                            ELSE '/* CRLF ' + CAST(@i AS VARCHAR(20)) + ':'
                                 + CAST(@part_len AS VARCHAR(20)) + ' */' + @CRLF
                        END
                        + REPLACE(SUBSTRING(@message, @i, @part_len), '%', '%%');
            RAISERROR (@part, 0,1) WITH NOWAIT;
            SET @i = @i + @part_len + 2;
        END;
    END;

END;

Usage:

-- Declare long message
DECLARE @LongMessage NVARCHAR(MAX) = '';
-- Fill message with test data
DECLARE @i INT = 1;
WHILE @i < 200 BEGIN
    SET @LongMessage = @LongMessage
                       + CASE @i WHEN 1 THEN '' ELSE CHAR(13) + CHAR(10) END
                       + CAST(@i AS VARCHAR(10))
                       + '. Lorem ipsum dolor sit amet, consectetur adipiscing elit.';
    SET @i = @i + 1;
END;

-- Display length of generated message
DECLARE @len INT = LEN(@LongMessage);
RAISERROR('Message length: %i', 0, 1, @len);

-- Use SP to print long message
EXEC sp_print_big_message @LongMessage;

MSSQL - Convert speсial types to strings or HTML:MONEY to string, DATETIME to string, GUID or UNIQUEIDENTIFIER to string, VARBINARY or BINARY to string, XML to string, TIMESTAMP to string

Most of datatypes is relative simple convert to a string.
For this we can use the CONVERT() or even the CAST() function:

SELECT int_as_string = CONVERT(VARCHAR(20), int_field) FROM MyTable;

SELECT int_as_string = CAST(int_field AS VARCHAR(20))  FROM MyTable;

But for some other datatypes convert is not so simple.
For example for MONEY, DATETIME, UNIQUEIDENTIFIER, BINARY, XML and TIMESTAMP datatypes.

There the script which shows how to convert such datatypes to strings:

USE tempdb;
GO

-- Drop test table if exists
IF OBJECT_ID('test_converts') IS NOT NULL
    DROP TABLE test_converts;
GO

-- Create test table
-- The technique to generate random money value I have used from there:
--     http://yabele.blogspot.de/2013/08/mssql-random-number-generator-with.html
CREATE TABLE test_converts
(
    mon MONEY             NOT NULL DEFAULT RAND(CHECKSUM(NEWID())) * 1000,
    dt DATETIME           NOT NULL DEFAULT GETDATE(),
    guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    bin VARBINARY(MAX)    NOT NULL DEFAULT NEWID(),
    x XML                 NOT NULL,
    ts TIMESTAMP
);
GO

-- Fill test table with 3 records
-- For all columns except "x" (xml) the default values will be used
INSERT INTO test_converts (x)
VALUES
    ('<a b="c"><d/></a>'),
    ('<e f="g"><h/></e>'),
    ('<i j="k"><l/></i>');

-- Show original values
SELECT * FROM test_converts;

-- Convert values to string
SELECT
                          -- Third parameter (0) is default and generates result text
                          -- in "0.00" format
    money_as_string     = CONVERT(VARCHAR(20), mon, 0),
                          -- Third parameter (121) generates result text
                          -- in ODBC-canonical format: "yyyy-MM-dd HH:mm:ss.sss"
    datetime_as_string  = CONVERT(VARCHAR(30), dt, 121),
    guid_as_string      = CONVERT(VARCHAR(36), guid),
                          -- Third parameter (1) generates result text
                          -- in uppercase form with "0x" prefix
    binary_as_string    = CONVERT(VARCHAR(MAX), bin, 1),
    xml_as_string       = CONVERT(VARCHAR(MAX), x),
    timestamp_as_string = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ts), 1)
FROM
    test_converts;

After execution in SSMS (SQL Server Management Studio) we will have these two resultsets:



These techniques could be used for example to convert table-content to HTML.
In case you need convert values to HTML-string some converted string values will be necessary to HTML-encode:

-- Convert values to HTML-string
SELECT
                        -- Third parameter (0) is default and generates result text
                        -- in "0.00" format
    money_as_HTML     = CONVERT(VARCHAR(20), mon, 0),
                        -- Third parameter (121) generates result text
                        -- in ODBC-canonical format: "yyyy-MM-dd HH:mm:ss.sss"
    datetime_as_HTML  = CONVERT(VARCHAR(30), dt, 121),
    guid_as_HTML      = CONVERT(VARCHAR(36), guid),
                        -- Third parameter (1) generates result text
                        -- in uppercase form with "0x" prefix
    binary_as_HTML    = CONVERT(VARCHAR(MAX), bin, 1),
                        -- Convert XML-string to HTML-encoded string
    xml_as_HTML       = REPLACE(REPLACE(REPLACE(
                            CONVERT(VARCHAR(MAX), x)
                        , '&','&amp;'), '<', '&lt;'), '>', '&gt;'),
    timestamp_as_HTML = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ts), 1)
FROM
    test_converts;

Correspondent resultset in SSMS:

---

See also:
MSSQL - The random number generator with a random initialization (random seed)

MSSQL - Kill all my processes (except current)

Template to kill all my processes (connections) except current

USE master
GO

DECLARE @sql VARCHAR(MAX) = '-- Kill all my processes (except current: '
                          + CAST(@@SPID AS VARCHAR(20)) + ')';
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE hostname = HOST_NAME() AND spid != @@SPID;

PRINT @sql;
EXEC (@sql);

After execution we have something like this:

-- Kill all my processes (except current: 80)
kill 52;
kill 53;
kill 73;

MSSQL - Stored procedure to replace placeholders in texts (dynamic SQL)

It is often necessary to make the query text from constant pieces and variables.

For example we have a many tables with such structure:

(
    ID         INT IDENTITY(1,1) PRIMARY KEY,
    Number     INT,
    ChangeDate DATETIME
)

and we need dynamically, depending on variables with table name, [Number] min value and [ChangeDate] min value send queries to one or other table.
We can do it using Dynamic SQL method, for example so:

CREATE PROCEDURE MySP
    @ObjectName SYSNAME,
    @MinNumber  INT,
    @ValidFrom  DATETIME
AS BEGIN
    DECLARE @SQL NVarChar(MAX);

    SET @SQL = '
        SELECT
            *
        FROM
            ' + @ObjectName + '
        WHERE
            Number >= ' + CAST(@MinNumber AS VARCHAR(20)) + '
            AND
            ChangeDate >= CONVERT(DATETIME, '''
            + CONVERT(VARCHAR(30), @ValidFrom, 121) + ''', 121);';

    PRINT @SQL;

    EXEC sp_executesql @SQL;
END;
GO

EXEC MySP 'MyTableNr1', 123, '2015-01-01 12:00';

It works, but SQL-Text format is not readable.
Much more readable looks the text converted to a template with placeholders:

'
SELECT
    *
FROM
    {ObjectName}
WHERE
    Number >= {MinNumber}
    AND
    ChangeDate >= CONVERT(DATETIME, ''{ValidFrom}'';
'

In this case our example stored procedure will look like this:

CREATE PROCEDURE MySP
    @ObjectName SYSNAME,
    @MinNumber  INT,
    @ValidFrom  DATETIME
AS BEGIN
    DECLARE @SQL NVarChar(MAX);

    SET @SQL = '
        SELECT
            *
        FROM
            {ObjectName}
        WHERE
            Number >= {MinNumber}
            AND
            ChangeDate >= CONVERT(DATETIME, ''{ValidFrom}'';'

    SET @SQL = REPLACE(@SQL, '{ObjectName}', @ObjectName);
    SET @SQL = REPLACE(@SQL, '{MinNumber}',  CAST(@MinNumber AS VARCHAR(20)));
    SET @SQL = REPLACE(@SQL, '{ValidFrom}',  CONVERT(VARCHAR(30), @ValidFrom, 121));

    PRINT @SQL;

    EXEC sp_executesql @SQL;
END;

It is good for so simple example, but if we will have more dynamical parameters and inside one stored procedure (or plain query) more then one template, then this query will have too many redundant call like this:

SET @SQL = REPLACE(@SQL, ..., ...);

The solution is to create some help procedure which will take text template and dictionary with placeholder name:value pairs as parameters.
Stored procedure can not take table-value parameters directly.
Table-valued parameters have to be declared by using user-defined table types.
In our case:

CREATE TYPE PlaceholderMappingType AS TABLE (
    Placeholder NVARCHAR(100),
    Value NVARCHAR(MAX)
);

The complete script with usage example:

USE tools;
GO
IF OBJECT_ID('tools.dbo.sp_ReplacePlaceholders') IS NOT NULL BEGIN
    PRINT '-- DROP PROCEDURE dbo.sp_ReplacePlaceholders';
    DROP PROCEDURE dbo.sp_ReplacePlaceholders;
END;
GO
IF EXISTS (
    SELECT *
    FROM tools.INFORMATION_SCHEMA.DOMAINS
    WHERE DOMAIN_NAME = 'PlaceholderMappingType'
) BEGIN
    PRINT '-- DROP TYPE dbo.PlaceholderMappingType';
    DROP TYPE dbo.PlaceholderMappingType;
END;
GO
PRINT '-- CREATE TYPE dbo.PlaceholderMappingType';
-- TABLE-Domain (User Defined Type) for placeholders dictionary
CREATE TYPE PlaceholderMappingType AS TABLE (
    Placeholder NVARCHAR(100),
    Value NVARCHAR(MAX)
);
GO
CREATE PROCEDURE dbo.sp_ReplacePlaceholders (
    @Text                NVARCHAR(MAX) OUTPUT,
    -- TABLE-Domain (User Defined Type) for placeholders dictionary
    @PlaceholderMappings PlaceholderMappingType READONLY,
    -- Flag to replace in placeholder values each single apostrof with double apostrof
    @EscapeApostrofs     BIT = 1,
    -- Text to TrimLeft in each text line
    @IndentString        VARCHAR(MAX) = '',
    -- Flag to PRINT additional diagnostic information
    @Debug               BIT = 0
)
AS BEGIN
    SET NOCOUNT ON;

    IF (@Debug = 1)
        PRINT '-- EXEC dbo.sp_ReplacePlaceholders'

    DECLARE
        @CRLF             CHAR(2) = CHAR(13)+CHAR(10),
        @Indent_CharIndex INT,
        @I                INT = 0,
        @Char             CHAR(1),
        @Indent           VARCHAR(MAX) = '';

    -- Trim rows left
    IF (CHARINDEX(@IndentString, @Text, 0) = 1)
        SET @Text = STUFF(@Text, 1, LEN(@IndentString), '');
    SET @Text = REPLACE(@Text, @CRLF + @IndentString, @CRLF);

    -- Declare FOREACH(placeholderRow IN @PlaceholderMappings) cursor
    DECLARE
        @Placeholder NVARCHAR(102),
        @Value       NVARCHAR(MAX);
    DECLARE Placeholders_Cursor CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT
        Placeholder = '{' + REPLACE(REPLACE(Placeholder, '{', ''), '}', '') + '}',
        Value       = CASE @EscapeApostrofs
                          WHEN 1
                          THEN REPLACE(Value, '''', '''''')
                          ELSE Value
                      END
    FROM @PlaceholderMappings;

    OPEN Placeholders_Cursor;

    FETCH NEXT FROM Placeholders_Cursor INTO @Placeholder, @Value;
    WHILE @@FETCH_STATUS = 0 BEGIN
        
        IF (@Debug = 1) AND (CHARINDEX(@Placeholder, @Text) = 0)
            PRINT '--        ==> Could not find Placeholder ' + @Placeholder;
        SET @Text = REPLACE(@Text, @Placeholder, @Value);

        FETCH NEXT FROM Placeholders_Cursor INTO @Placeholder, @Value;
    END;

    CLOSE Placeholders_Cursor
    DEALLOCATE Placeholders_Cursor

    RETURN;
END;
GO
DECLARE
    @SQL NVARCHAR(MAX),
    @PlaceholderMappings PlaceholderMappingType;

SET @SQL = '
        SELECT
            F1=''{aaa}'',
            F2=''{bbb}'',
            F3=''{ddd}'';';
INSERT INTO @PlaceholderMappings
VALUES
    ('aaa',   'A''A''A'),
    ('{bbb}', 'BBB'),
    ('ccc',   'CCC');

DECLARE @IndentString VARCHAR(MAX) = SPACE(8);
EXEC dbo.sp_ReplacePlaceholders
    @SQL OUTPUT,
    @PlaceholderMappings,
    @IndentString=@IndentString,
    @Debug=1;
PRINT '>>>' + @SQL + '<<<';