Return empty or null in sql procedure

Question

Using a script that doesnt accept null but Nan i'm trying to insert null value instead of 0 in sql server table. So to do that I had an idea to create stored procedure that will cast the Nan to null float. here is the stored procedure :

create procedure [dbo].fromNanToNull(@val VARCHAR)
   AS BEGIN
   DECLARE @Work float
   if @val like 'N%'    set @Work=CAST(NULL AS float)
   else set @work=CAST(@val AS float)
    return @work
   END

Execution :

 exec  [dbo].fromNanToNull 'Nan'

but instead of getting null in return i Got this message which tell that the return is forced to 0 instead. Here is the message :

The 'fromNanToNull' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

The first immediate error I see is your statement set @Work=CAST(NULL AS float). NULL is NULL, meaning NOTHING and hence it should not be cast(ed) to any type. — FDavidov
– FDavidov, Commented Sep 19, 2018 at 10:14

Chanukya · Accepted Answer · 2018-09-19 10:41:25Z

1

As per my understanding @val is starting with n means you are converting @work value to float but even @work is float datatype only if @val is starting with n means you should display @work value else table value please explain clearly

    CREATE PROCEDURE [DBO].FROMNANTONULL(@VAL VARCHAR)
       AS BEGIN
      DECLARE @VAL VARCHAR(10)
 DECLARE @WORK FLOAT
IF (@VAL LIKE 'N%') 
  BEGIN 
SET @WORK=CAST(NULL AS FLOAT)
  END 
ELSE 
  BEGIN 
SET @WORK=CAST(@VAL AS FLOAT)
  END
       END

edited Sep 19, 2018 at 10:41

answered Sep 19, 2018 at 10:09

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Atimene Nazim Over a year ago

It was the retrun statement that throw the error. The exepression @WORK=CAST(NULL AS FLOAT) is useless. I could use @work = null instead. Thank you for your help

Ashu Over a year ago

so now you don't want any return value or what

Collectives™ on Stack Overflow

Return empty or null in sql procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related