change to null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

change to null

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-10-07 : 06:30:36

Hi

I have a stored procedure that I call using LINQ, my problem is that I cannot pass null values to it and therefore I get wrong results, so I need to check if a value is '', and if so change it o NULL.

I have these parameters..


@Model nVarChar(50) = NULL, -- Optional
@S1 nVarChar(50) = NULL,
@S2 nVarChar(50) = NULL,
@CountryID Int = NULL

If @Model, @S1 or @S2 values is equal to '' then I need to change them to NULL and use that null value in my question instead, and if the @CountryID is equal to -1 I want to change that to NULL.

But if the values are NULL no change should be made.

How would I do that?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-07 : 06:34:55

In the WHERE clause or where you are using the parameters you can do
NULLIF(@Model,'')
or
NULLIF(@CountryID,-1)

No, you're never too old to Yak'n'Roll if you're too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-10-07 : 06:41:03

Hi

My Where clause look like this..

WHERE (dbo.Qip.MODN LIKE @Model + '%' OR @Model IS NULL)
AND (dbo.Qip.MODE = @S1 OR @S1 IS NULL)
AND (dbo.Qip.NR = @S2 OR @S2 IS NULL)
AND (dbo.tbl_Field.CountryID = @CountryID OR @CountryID IS NULL)

So I guess It have to be changed before my where clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 06:53:02

nope. just change as webfred suggested inside sp and it will work fine


....
@Model nVarChar(50) = NULL, -- Optional
@S1 nVarChar(50) = NULL,
@S2 nVarChar(50) = NULL,
@CountryID Int = NULL
...
AS

SELECT @Model=NULLIF(@Model,''),@S1=NULLIF(@S1,''),@S2=NULLIF(@S2,''),..

....


WHERE ...
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-10-07 : 08:09:24

Ahh, yes. It worked perfect. Thanks !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 12:22:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources