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

Answers


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?


Need Your Help

How does non blocking IO work in javascript

javascript nonblocking

I read that the javascript language has characteristics that assist in the implementation of non-blocking IO which contributes to the success of projects like node.js. My question is what are these

Class bean in application context for Cacheable annotation

java spring caching ehcache

I'm using Spring 4.1.4 and Ehcache 2.9 and caching using the @Cacheable annotation.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.