How to test if record is found or not and do different things accordingly in a query

I want to do something like

if "Product_code" is Null

insert dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)
select Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1
FROM dbo.t_Shopping_cart
WHERE Product_code='AHTU140213'

else

update dbo.t_Shopping_cart
set Quantity=Quantity+1
where Product_code='AHTU140213'

In short: I cant test existence of a record.

Regards

Leif

IF @.Product_codeISNULLBEGININSERT dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)SELECT Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1FROM dbo.t_Shopping_cartWHERE Product_code= @.Product_CodeENDELSEBEGINUPDATE dbo.t_Shopping_cartSET Quantity=Quantity+1WHERE Product_code=@.Product_CodeEND

|||

IFNOT EXISTS(SELECT *FROM dbo.t_Shopping_cartWHERE Product_code=@.Product_code)BEGININSERT dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)SELECT Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1FROM dbo.t_Shopping_cartWHERE Product_code= @.Product_CodeENDELSEBEGINUPDATE dbo.t_Shopping_cartSET Quantity=Quantity+1WHERE Product_code=@.Product_CodeEND

|||

This looks interesting. I will certainly try it later tonight.

A couple of short questions.

By the way, is it @.Product_code and not Product_code in "IF @.Product_codeISNULL"line.

If it is possible to write Yhteensa=1, which means total=1 by the way, why isn't this possible "Yhteensa=Price*Quantity". I tried it in Management Studio.

Many thanks

Leif

how to suppress zeroes after decimal point at the end in a value

hi,
i have a sql table field price and datatype is
decimal 13(20,6).
when i insert values to this field, values are being
inserted correctly. i.e. 13.45 inserted as 13.45 and
145.653 inserted as 145.653 only.
But while fetching only the values are coming as 13.450000,
145.653000, because the datatype is decimal 13(20,6) with
6 decimals. but i want 13.45, 145.653 as in the table.
How suppress the unwanted zeroes at the end of those
numbers.
any help.
thanks,
hari.
see following example:
drop table test
create table test(c1 decimal (15,5))
insert into test values (3.567000)
insert into test values (232233.567000)
insert into test values (3.567)
query:
select c1,reverse(substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) - 1)
)) 'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||hi thanks,
but it is not working for whole number like ex:11120
it is giving it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||try:
select c1, reverse(
case when substring(substring(reverse(cast(c1 as varchar(25)))
,
patindex('%[^0]%', reverse(cast(c1 as varchar(25))))
,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1)) ,1,1) = '.'
then
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) + 1 ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 2))
else
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1))
end)
'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"hari" <anonymous@.discussions.microsoft.com> wrote in message
news:207601c4a145$b5262d90$a601280a@.phx.gbl...[vbcol=seagreen]
> hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
> thanks,
> hari.
> (25))) ,
> reverse(cast(c1 as
|||See if this works:
select
replace(rtrim(replace(
replace(rtrim(replace(
c1,'0',' ')),' ','0')
,'.',' ')),' ','.')
from test
Steve Kass
Drew University
hari wrote:
[vbcol=seagreen]
>hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
>thanks,
>hari.
>
>(25))) ,
>
>reverse(cast(c1 as
>
>