Skip to content

Instantly share code, notes, and snippets.

@erikdarlingdata
Last active August 10, 2021 12:44
Show Gist options
  • Save erikdarlingdata/f5dd35313ec6c3e966f472002eb9f1e4 to your computer and use it in GitHub Desktop.
Save erikdarlingdata/f5dd35313ec6c3e966f472002eb9f1e4 to your computer and use it in GitHub Desktop.
CREATE OR ALTER PROCEDURE
dbo.Longingly
(
@loops int = 1,
@debug bit = 0
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
DECLARE
@psql nvarchar(MAX) = N'DECLARE @p',
@ssql nvarchar(MAX) = N'SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u WHERE u.DisplayName IN (@p',
@asql nvarchar(MAX) = N'',
@i int = 1;
WHILE @i <= @loops
BEGIN
SELECT
@psql += RTRIM(@i)
+ N' NVARCHAR(40) = N'
+ QUOTENAME
(
CONVERT
(
nvarchar(36),
NEWID()
),
''''
)
+ N';'
+ NCHAR(10)
+ N'DECLARE @p',
@ssql += RTRIM(@i)
+ N', @p';
SELECT
@i += 1;
END;
SELECT
@psql =
SUBSTRING
(
@psql,
1,
LEN(@psql) - 10
),
@ssql =
SUBSTRING
(
@ssql,
1,
LEN(@ssql) - 4
) + N');';
SELECT
@asql = @psql + NCHAR(10) + @ssql;
IF @debug = 1
BEGIN
PRINT N'----';
PRINT N'@psql';
PRINT @psql;
PRINT N'----';
PRINT N'@ssql';
PRINT @ssql;
PRINT N'----';
PRINT N'@asql';
PRINT @asql;
PRINT N'----';
END
SET STATISTICS XML ON;
EXEC sys.sp_executesql
@asql;
SET STATISTICS XML OFF;
END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment