Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

NULL in T-SQL

I need to check if a value (PXProjectID) is NULL for filtering in the where clause of the select statement of the cursor. Unfortunately I can't fetch a NULL value in the variable @PXProjectID what is a int value. I also tried to declare the varialble as sql_variant. How can I filter the cursor without need to set a variable for the PXProjectID Field?

Declare CurProj Cursor
            Global
            Scroll
            Dynamic
            Scroll_Locks
            For
            Select ProjectID, CustomerID, MiteProjectID, Name, Budget, Archived, CreatedOn, PXProjectID
            From dbo.AX_Project
            Where Archived = 0 and isNULL(PXProjectID,'null') = 'null' --Only Projects where are not archieved and where are no PXProjectID
            
            Open CurProj
            Fetch next from CurProj into @ProjectID, @CustomerID, @MiteProjectID,
            @Name, @Budget, @Archived, @CreatedOn, @PXProjectID

            While @@FETCH_STATUS = 0
                  Begin
0
Opusretis
Asked:
Opusretis
1 Solution
 
jatinkhatriCommented:
good
0
 
igni7eCommented:
I'm not sure what you are trying to do.
Can you explain
1. What data it's currently returning
2. What data you want it to return

Will this work???
"where Archived = 0 and PXProjectID is null"
0
 
tbsgadiCommented:
Hi Opusretis,
try something like isNULL(PXProjectID,-999) =-999



Gary
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
leakim971PluritechnicianCommented:
Hello Opusretis,

... AND PXProjectID IS NULL ...

Regards.
0
 
tbsgadiCommented:
When you use the Isnull function the default must be of the same type ,ie an int in your case


Gary
0
 
tigin44Commented:
can the PXProjectID field contain '' instead of NULL if so you should use a syntax like

AND NULLIF(PXProjectID, '') IS NULL
0
 
OpusretisAuthor Commented:
I try to explain it better:

The Error occurs in the fetch line because the variable (@PXProjectID) can't be settet with NULL.
I tried to fetch it without @PXProjectID but then I get the error that the fetch need similar values as the cursor.
0
 
igni7eCommented:
change "From dbo.AX_Project"
to "FROM (SELECT col1, col2, ISNULL(PXProjectID,0) as PXProjectID, col4, ... FROM  AX_PROJECT) AX_PROJECT"

If you want to exclude the nulls just do:
"FROM (SELECT * FROM  AX_PROJECT where PXProjectID is not null ) AX_PROJECT"
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now