How to Check Parameter is not null in sql server?

Question

I have a stored procedure. In this stored procedure I have to check that a particular parameter is not null. How can I do this? I wrote this:

ALTER PROCEDURE [dbo].[GetReelListings]
    @locationUrlIdentifier VARCHAR(100)
AS
BEGIN   

SET NOCOUNT ON;

declare @Sql varchar(max)=''

SET @Sql = 'SELECT CategoryName, CategoryUrlIdentifier,  LocationUrlIdentifier, Directory.* FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Category.Name ORDER BY CASE WHEN '''+ @locationUrlIdentifier + ''' = Location.UrlIdentifier THEN 1 ELSE CASE WHEN ''' + @locationUrlIdentifier + ''' IS NULL AND Directory.LocationId IS NULL THEN 0 ELSE 2 END END, Directory.SortOrder ) AS ''RowNo'', Category.Name AS CategoryName, Category.UrlIdentifier AS CategoryUrlIdentifier, dbo.Location.UrlIdentifier AS LocationUrlIdentifier, Directory.DirectoryId, CASE WHEN ''' + @locationUrlIdentifier + ''' = Location.UrlIdentifier THEN 1 ELSE CASE WHEN ''' + @locationUrlIdentifier + ''' IS NULL AND Directory.LocationId IS NULL THEN 0 ELSE 2 END END AS CategoryOrder    FROM dbo.Directory  INNER JOIN dbo.Category ON Directory.CategoryId = Category.CategoryId LEFT OUTER JOIN dbo.Location ON dbo.Directory.LocationId = location.Location_ID ) AS content INNER JOIN dbo.Directory ON content.DirectoryId = Directory.DirectoryId WHERE content.RowNo =1 '

if (@locationUrlIdentifier is null)
begin
SET @Sql = @Sql + ' and 1=1'
end
else
begin
SET @Sql = @Sql + ' and CategoryOrder = 1 '
end

print @SQl
EXECUTE (@Sql)
 END

This will work in SQL but this will return a null Dataset in Codebehind.

Better if you can give full script. Then easy to find the problem — Prasanna, Sep 19 '12 at 12:43
I think you should use NULLIF Refer http://stackoverflow.com/questions/9855331/how-to-check-if-a-parameter-is-null-or-empty-sql — Prasanna, Sep 19 '12 at 12:46

Damien_The_Unbeliever · Accepted Answer · 2012-09-19T12:59:38.060

Whenever you join strings and NULLs together, the result is NULL. By the time you're asking about whether the variable is NULL, you've already done this:

' + @locationUrlIdentifier + '

Several times. If it's NULL, so will @Sql be.

You might want to consider using COALESCE to replace the NULL with a suitable replacement (e.g. an empty string):

' + COALESCE(@locationUrlIdentifier,'') + '

You also still have a logic error on your final construction. If the variable is NULL, you'll have a where clause saying:

WHERE content.RowNo =1 1=1

Which isn't valid. I don't think you should be appending anything.

I'm also not clear on why you're doing this as dynamic SQL. The below seems to be an equivalent query which can be executed directly:

SELECT
    CategoryName,
    CategoryUrlIdentifier,
    LocationUrlIdentifier,
    Directory.*
FROM
    (SELECT
        ROW_NUMBER() OVER (
            PARTITION BY Category.Name ORDER BY
                CASE
                    WHEN @locationUrlIdentifier  = Location.UrlIdentifier THEN 1
                    WHEN @locationUrlIdentifier IS NULL AND Directory.LocationId IS NULL THEN 0
                    ELSE 2
                END,
                Directory.SortOrder
        ) AS RowNo,
        Category.Name AS CategoryName,
        Category.UrlIdentifier AS CategoryUrlIdentifier,
        dbo.Location.UrlIdentifier AS LocationUrlIdentifier,
        Directory.DirectoryId,
        CASE
            WHEN @locationUrlIdentifier  = Location.UrlIdentifier THEN 1
            WHEN @locationUrlIdentifier IS NULL AND Directory.LocationId IS NULL THEN 0
            ELSE 2
        END AS CategoryOrder
    FROM
        dbo.Directory
            INNER JOIN
        dbo.Category
            ON
                Directory.CategoryId = Category.CategoryId
            LEFT OUTER JOIN
        dbo.Location
            ON
                dbo.Directory.LocationId = location.Location_ID
    ) AS content
        INNER JOIN
    dbo.Directory
        ON
            content.DirectoryId = Directory.DirectoryId
WHERE
    content.RowNo =1 and
    (@locationUrlIdentifier or CategoryOrder = 1)

@ABVyas - there's no need to add `and 1=1` to an otherwise well formed `WHERE` clause. — Damien_The_Unbeliever, Sep 19 '12 at 13:00

score 1 · Answer 2 · answered Sep 19 '12 at 12:48

1

You can do it just in ONE query:

Select Query ...where ... 
  and ((@locationUrlIdentifier is null) or (CategoryOrder = 1))

answered Sep 19 '12 at 12:48

valex

23,966
7
43
60

score 0 · Answer 3 · edited May 23 '17 at 12:31

0

You can use NULLIF instead of IS NULL

Refer : Check if a parameter is null or empty in a stored procedure

http://msdn.microsoft.com/en-us/library/ms177562.aspx

edited May 23 '17 at 12:31

Community

1
1

answered Sep 19 '12 at 12:50

Prasanna

4,583
2
22
29

score 0 · Answer 4 · answered Sep 19 '12 at 12:54

0

Alternatively you can use ISNULL() check and then change the null to empty string

IF (ISNULL(@locationUrlIdentifier,'') = '')

OR even before this check you can use ISNULL() to convert from NULL to empty string if it persists to be a problem

answered Sep 19 '12 at 12:54

Conrad Lotz

8,200
3
23
27

How to Check Parameter is not null in sql server?

4 Answers4