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 开发者_JS百科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?