[lehel]

I have multiple SQL commands in a LINQPad file for checking errors in the database.
I would like to show some comments between the Result Sets returned by those commands.
I figured out that I could use the PRINT command to print some text to the output, but even though my PRINT commands are between the select statements, all of them are printed out on the top and all SELECT results are coming after.
Is there any other command which I could use to print some comments between the query results? Or give some titles to the Result Sets. Otherwise I have to count all result sets and count my code section to see which one referred to which result.
(Usually my result sets return 0 rows if there is no error, and return the result if there are errors, so I have to check which was the code referring to it, and I have my comments there in my code section, but it would be much easier to see the comment printed beside the result)

Thank you,
Steven

[sgmoore]

There are a number of ways.

You can add a 'go' before your print. eg

Print 'Stage 1'
Select ....
Go
Print 'Stage 2'
select  ...
Go
Print 'Stage 3'
select  ...

You can also use

 Select 'Status Message' . 

However, this will take up three lines - as in

   Result Set (1 item)  
   Column1 
   Status Message

If you are doing a select statement and are only interested in the non-empty results, you can add an extra static column , for example

select 'Check Customer Balances'  as Routine, Code, Name 
from customer where Balance > 100000000

if there are no customers with balances over 100million, you just get a line saying

 Result Set (0 items)

otherwise you get something like

Result Set (1 item)  
Routine                     Code    Name 
Check Customer Balances     BLOGGS1 Joe Bloggs

so the first column will show you which routine has failed.

[lehel]

Thank you very much.
Using the GO statement was enough.
I found this interesting but couldn't understand exactly:
> select 'Check Customer Balances' as Routine, Code, Name from customer where Balance > 100000000

What is Code in this case?
Or in your example, what is Code = BLOGGS1 ? Is it a stored procedure?
I don't really want to create stored procedures which stay in the database, only want to run some scripts for analyzing the DB, or maybe some temprorary stored procedures which are valid until the script runs.

Thanks,
Steven

[sgmoore]

lehel, on 15 September 2011 - 06:54 AM, said:

I found this interesting but couldn't understand exactly:
> select 'Check Customer Balances' as Routine, Code, Name from customer where Balance > 100000000

What is Code in this case?
Or in your example, what is Code = BLOGGS1 ? Is it a stored procedure?

Code is a column in the customer table, Bloggs is just an example customer code.

The sql statement is returning two columns (code and name) from the customer table in order to identify customers with balances outside normal parameters. (It also returns a column of static text to identity the routine or test that is running)

In this example, the query returns the details of just one customer whose name is 'Joe Bloggs' and his Code (or Account Number/ID) happens to be BLOGGS1.

[lehel]

Using the GO statement looks good, but I tried to use it in a script where I have local variables and those variables are not valid anymore after the GO statement.

For example:
SET @ID = '4638813'

Print 'Profile information:'
SELECT ID, Full_Name, Full_Address FROM User_Profile
WHERE ID = @ID
GO

After this I cannot use more select statements with the same @ID variable.

Is there a way that I could use some kind of variables and print a text + show the select results?

Your 2nd solution (Select 'Status Message') looks very busy, and hard to see the real information.

The 3rd solution is fine, but in case is 0 results, it doesn't show the text either.

I would prefer the 1st solution (print, go) but it would be nice to be able to use some kind of variables too.

Thank you,
Steven

[JoeAlbahari]

I've figured out to fix this in LINQPad - the next beta due out in a week or so will have this mod.

Cheers
Joe

[lehel]

Hi Joe,

Have you figured out some solution for this? Are you using some kind of LINQPad system variables?
Does the newest version of LINQPad has the solution?

Thanks,
Steven

[JoeAlbahari]

This is now in the latest beta:
http://www.linqpad.net/beta.aspx

Joe

[lehel]

Hi Joe,

I see that the beta has lots of new features, and you mentioned that you found a fix for this problem in that.
What is the solution for this problem in the newest beta?
The variables with @ you still cannot use after you used a GO statement.

Thanks,
Steven

[JoeAlbahari]

You don't need a GO statement - just do this:

print 'Stage 1'
select ....
print 'Stage 2'
select ...
print 'Stage 3'
select ...

and LINQPad will display everything in the correct order.