5

I don't get any output for this simple PRINT statement. What am I missing?

Data supplied in sqlfiddle.

DECLARE @team varchar(20)

BEGIN
   SELECT @team = TEAM
   FROM DISTRIB_LINE 
   PRINT 'value is' + @team
END
  • Using MSSQL 2016, I do (value is 800) - what version are you using? – Ian Kemp Mar 13 '18 at 10:40
8

The PRINT statement will print nothing if the expression evaluates to a null. Apparently @team ends up being null as the result of the preceding statement, causing the PRINT to output nothing. If you want to print something even when the variable is null, use ISNULL or COALESCE:

PRINT 'value is ' + ISNULL(@team, '(null)');

You could also add a filter to the SELECT to ensure that NULLs are skipped (if you want them skipped, that is):

SELECT @team = TEAM
FROM DISTRIB_LINE
WHERE TEAM IS NOT NULL;

However, if the table has no rows or if all TEAM values are null, this statement will not do any assignment. In this case, if PRINT still outputs nothing, it means @team was null even before the SELECT. Therefore, you may still need to apply the previous method.

Besides, it is not a good idea to assign a variable this way when a table has many rows, because you have no control of which value will ultimately be assigned. Therefore. Alexei has a point there as well with regard to using TOP, as well as combining it with ORDER BY and, possibly, additional filters.

  • To clarify: concatenating null with any string in MSSQL results in a null string by default - see the setting CONCAT_NULL_YIELDS_NULL. – Ian Kemp Mar 13 '18 at 10:44
  • 2
    @IanKemp: I wish there had never been a notion that concatenating a null can yield anything else than a null. In relation to that (albeit unrelated to this question), I wish Oracle had never conflated the concept of an empty (zero-length) string with that of a null (unknown) string in their database product. – Andriy M Mar 13 '18 at 14:21
6

PRINT outputs a message, not a resultset. Try something like the following:

DECLARE @team varchar(20)
SELECT TOP 1 @team = TEAM
FROM DISTRIB_LINE 

SELECT @team AS Team

My example contains TOP because I have ensured that @team receives a single value. However, its value will not be clear (non-deterministic). The most certain is using a ORDER BY clause and a WHERE that enforces a single record).

3

Your code works on my machine and prints

value is800

SQLFiddle just doesn't print output it seems.

If you try your sample on dbfiddle you get the correct output.

If you just run PRINT 'A'; on SQLFiddle it doesn't print anything either (see here) while it does on DBFiddle.

The implementation details of SQLFiddle aren't clear but I suppose it just suppresses print statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.