SQL Server Stored Proc - What is returned to a variable if no data?
If, using the following code in a MS SQL Server 2008 stored procedure:
DECLARE @PROD_ID VARCHAR(20) SELECT @PROD_ID = MYTABLE.PROD FROM MYTABLE WHERE MYTABLE.DEVID = @DEVCODE
DEVCODE does not exist, what will PROD_ID contain? I've tried printing it, but it prints what seems to be a space. However, testing it for space fails. Also, testing for NULL fails. OR, should I be testing for empty in a different manner?
Thanks for reading BBz
If the @DEVCODE id doesn't exist then @PROD_ID will remain null.
You can't use COALESCE or ISNULL inside the SELECT @PROD_ID = ... statement because it won't return any records at all.
However, you can do:
DECLARE @PROD_ID VARCHAR(20) SELECT @PROD_ID = MYTABLE.PROD FROM MYTABLE WHERE MYTABLE.DEVID = @DEVCODE IF (@PROD_ID is null) BEGIN -- do something END
@PROD_ID should remain NULL if @DEVCODE does not exist.
Are you testing appropriately using
...@PROD_ID IS NULL...
and not trying to test
...@PROD_ID = NULL...
which would be incorrect?