How to compare SqlDbtype.Money = 0 with money field in Sql server

Question

I have a SQL server 2008. The table(Items) structure with data i have is as follows:

ID(int)   Name(varchar(50))         Price(money)
 1        ArchBook                 1200.89
 2        Freebie                  0.00
 3        Board                    800.54

The datatypes of these column are in the (). I have a select stored proc that takes price as a parameter

 create procedure dbo.GetItemByPrice
 @price money 
 as
 begin
 if @price = '' OR @price is NULL
 set @price = ''
 select * from dbo.Items 
 where @price = '' OR price = @price
 end

When i send 0 for price from C# code the result is incorrrect. I used cast in stored proc to convert it to money again after if clause.(How do I convert from a money datatype in SQL server?) But it doesnt seem to work. Any suggestions of how i can take care of compare with 0? If price is empty is return all records. C# Code

            cnn.Open();               
            string amount = "0";
            var command = new SqlCommand("GetItemsByPrice", cnn);
            command.CommandType = CommandType.StoredProcedure;
            var param = new SqlParameter("@price", SqlDbType.Money);
            param.Value = Convert.ToDecimal(amount);
            command.Parameters.Add(param);
            var dataReader = command.ExecuteReader();
            while (dataReader.Read())
            {
               //printing columns

            }
            dataReader.Close();
            command.Dispose();

Check my answer and tell me if helped you. – mybirthname Dec 26 '14 at 09:23 — mybirthname, Dec 26 '14 at 09:23

score 0 · Answer 1 · answered Dec 26 '14 at 09:14

0

Try this

CREATE PROCEDURE dbo.GetItemByPrice
@price money 
as
BEGIN
if @price = '' OR @price is NULL
SET @price = ''
select * from dbo.Items 
where @price = '' OR price = @price
END

answered Dec 26 '14 at 09:14

su8898

1,703
19
23

I have added set. I made a mistake in typing. This doesn't help – Eve Dec 26 '14 at 09:42
Did you try executing this SP from SSMS/SqlCmd? See if it's returning the correct results and then check from your C# code. If it works from SSMS and not from C# code, then please post the part of your code that's executing the SP. – su8898 Dec 26 '14 at 09:46
From ssms. The problem is with the SP. I need to handle a case where price can be empty in which case i need to return all fields from table. And also price can be zero;In this case i need to return all records with price 0 – Eve Dec 26 '14 at 09:55
The problem can't be with the SP. It works on my SQL Server 2008 R2 instance. I tested it. It could be with your C# code. – su8898 Dec 26 '14 at 09:59

mybirthname · Accepted Answer · 2014-12-26T09:56:05.603

0

In my opinion your logic is broken.

I want to GetItemByPrize for price 800.54 and I will receive Items with price "". Just make Price field with default value 0 and not allow nulls.

Change the procedure:

CREATE PROCEDURE dbo.GetItemByPrice
@price money 
as
BEGIN
select * from dbo.Items 
where price = @price
END

EDIT:

If you take price value from textbox for an example: Here is code example

decimal price = 0;

decimal.TryPrase(priceTxtBox.Text, out price);

cmd.Parameters.AddWithValue(@"price", price)

This will guarantee that the price param will be 0 when the textbox value is "" or invalid text.

edited Dec 26 '14 at 09:56

answered Dec 26 '14 at 09:22

mybirthname

17,949
3
31
55

How do i handle case where i need to return all values in table if price sent was empty? – Eve Dec 26 '14 at 09:50
On ui price is a filter criteria. it can be empty. So i cannot make it zero explicity. I want to exclude it from SP in case if empty. – Eve Dec 26 '14 at 09:58
@Eve show your code ... if you want to not execute when is empty just add one if to not execute the procedure .. Add your code in the question. – mybirthname Dec 26 '14 at 10:02
My bad. Seems like i made a mistake. I cannot send empty at all after all. Is there any way you could suggest how i can take care of such a scenario? Empty as well as zero?. If filtering wasn't applied on Price column then return all column and if it were return items of that price – Eve Dec 26 '14 at 10:30
@Eve I will not use procedure. I will create sql select * from dbo.Items and if Price != null && Price!=0 add where clause with the condition and add the parameter in this case. In other cases I will just execute Select * from dbo.Items. Make the answer as correct if this helped you. – mybirthname Dec 26 '14 at 10:34
I got it. Finally. I figured out a way like you mentioned. Thanks so much – Eve Dec 26 '14 at 10:58

How to compare SqlDbtype.Money = 0 with money field in Sql server

2 Answers2