asked
QUery that outputs messages only
I have a SQL query that outputs messages and I want to put the listed output in a report using BIDS but I don't know what way to do this. The qeury is a simple one to find missing file references in a table between 2 specified integers - DECLARE @TEST_ID integer, @LAST_ID integer, @ID integer SET @TEST_ID = 1 -- start compare with this ID SET @LAST_ID = 4000 -- end compare with this ID WHILE @TEST_ID <= @LAST_ID BEGIN SELECT @ID = (SELECT FileNo FROM Application WHERE FileNo = @TEST_ID and FileYear = 12) IF @ID IS NULL BEGIN PRINT 'Missing ID: ' + CAST(@TEST_ID AS VARCHAR(10)) END SET @TEST_ID = @TEST_ID + 1 END The output is - Missing ID: 742 Missing ID: 743 Missing ID: 744 Missing ID: 745 Missing ID: 746 Missing ID: 747 Missing ID: 748 Missing ID: 749 Missing ID: 750 Missing ID: 751 Missing ID: 752 Missing ID: 753 I to setup a report so that users can run this themselves and specify the start and end integers as well. I use BIDS to consruct my reports. I'm sure I'm overlooking some easy way to show this in a report!
There is no need to use loop. Instead you can use inline CTE Driven numbers table. Here is an example SET NOCOUNT ON; DECLARE @table TABLE ( FileId INT ) INSERT @table ( [FileId] ) SELECT 1 UNION ALL SELECT 2 DECLARE @TEST_ID integer, @LAST_ID integer, @ID integer SET @TEST_ID = 1 -- start compare with this ID SET @LAST_ID = 4000 -- end compare with this ID IF @LAST_ID > @TEST_ID AND @LAST_ID > 0 BEGIN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) ; WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(@LAST_ID + 1 - @TEST_ID,0)) CONVERT(INT,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E4 ) SELECT 'Missing Id: ' + CONVERT(VARCHAR, [N]) FROM [cteTally] WHERE NOT EXISTS ( SELECT FileId FROM @table WHERE [FileId] = N ) END You may need to add more validation of the input parameters. Hope it helps.
10 People are following this question.
