question

juliannecoughlan avatar image
juliannecoughlan 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!
bidsreportoutputmessage
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply. That helped a great deal!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.