I don't get any output for this simple
DECLARE @team varchar(20) BEGIN SELECT @team = TEAM FROM DISTRIB_LINE PRINT 'value is' + @team END
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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).
Your code works on my machine and prints
SQLFiddle just doesn't print output it seems.
If you try your sample on dbfiddle you get the correct output.
The implementation details of SQLFiddle aren't clear but I suppose it just suppresses print statements.