6 Replies - 484 Views - Last Post: 10 August 2016 - 11:22 AM

#1 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 148
  • View blog
  • Posts: 1,043
  • Joined: 17-November 10

conditions in scalar function

Posted 10 August 2016 - 10:45 AM

Greetings;

I am having an odd issue regarding scalar functions.

It takes the first condition upon execution but it does not touch the second condition at all.

It is doing what it suppose to, well, partly but there are values that are under 800.00 that suppose to be an empty fields.

I would really appreciate your help. Thanks.

CREATE FUNCTION fn_MASTER_ORDERS_STEMP(@CAP_AMOUNT DECIMAL)
RETURNS VARCHAR(255)
AS
BEGIN
	DECLARE @STAMPED VARCHAR(255);
	--SET @CAP_AMOUNT = (SELECT TOP 1 TOTAL_PRICE FROM dbo.MASTER_ORDERS);
	SET @CAP_AMOUNT = 800.00;
	IF(@CAP_AMOUNT >=800.00)
	BEGIN
		SET @STAMPED = N'STAMPED!';
	END
	ELSE IF(@CAP_AMOUNT < 800.00)
	BEGIN
		SET @STAMPED = N'';
	END
		--SET @STAMPED = (SELECT TOP 1 [email protected] FROM MASTER_ORDERS);
		RETURN @STAMPED;
END;
GO




USE TSQL2012
GO

IF OBJECT_ID(N'dbo.MASTER_ORDERS', N'U') IS NOT NULL
DROP TABLE MASTER_ORDERS
GO
SELECT	OD.ORDERID AS OD_ORDER_ID, 
		O.ORDERID AS O_ORDER_ID,
		(OD.UNITPRICE * OD.QTY) AS TOTAL_PRICE,
		O.SHIPCOUNTRY AS SHIP_COUNTRY		
		
		INTO MASTER_ORDERS FROM SALES.ORDERDETAILS AS OD, SALES.ORDERS AS O
		WHERE OD.ORDERID = O.ORDERID
		AND O.SHIPCOUNTRY != N'Brazil'
		AND O.SHIPCOUNTRY != N'UK'

ALTER TABLE dbo.MASTER_ORDERS
ADD STAMPED VARCHAR(255) NULL

UPDATE MASTER_ORDERS SET STAMPED = dbo.fn_MASTER_ORDERS_STEMP(TOTAL_PRICE)
FROM MASTER_ORDERS WHERE TOTAL_PRICE >= 800.00

SELECT * FROM MASTER_ORDERS






Is This A Good Question/Topic? 0
  • +

Replies To: conditions in scalar function

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: conditions in scalar function

Posted 10 August 2016 - 10:53 AM

Why are you over writing the input parameter?
07	    SET @CAP_AMOUNT = 800.00;

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,569
  • Joined: 12-December 12

Re: conditions in scalar function

Posted 10 August 2016 - 10:56 AM

I am confused. If you are referrring to this:
	SET @CAP_AMOUNT = 800.00;
	IF(@CAP_AMOUNT >=800.00)
	BEGIN
		SET @STAMPED = N'STAMPED!';
	END
	ELSE IF(@CAP_AMOUNT < 800.00)
	BEGIN
		SET @STAMPED = N'';
	END

then you are setting the value to 800 (ignoring the passed value), so only the first condition will ever be met.

I am also curious that you would use SELECT TOP 1 without an ORDER BY clause.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: conditions in scalar function

Posted 10 August 2016 - 11:01 AM

.. or why the whole table is dropped and not truncated.
Was This Post Helpful? 0
  • +
  • -

#5 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 148
  • View blog
  • Posts: 1,043
  • Joined: 17-November 10

Re: conditions in scalar function

Posted 10 August 2016 - 11:11 AM

Ahh thanks and sorry for posting it, felt like a noob while I was posting it!

I didn't realize that setting the @CAP_AMOUNT will create issues while comparing conditions with TOTAL_PRICE.

As for TOP 1 with no ORDER BY. It worked without ORDER BY as it is running through each row (I think,) but than I decided not to use it.

Anyways thanks. It runs amazingly now.

CREATE FUNCTION fn_MASTER_ORDERS_STEMP(@CAP_AMOUNT DECIMAL)
RETURNS VARCHAR(255)
AS
BEGIN
	DECLARE @STAMPED VARCHAR(255);

	IF(@CAP_AMOUNT >=800.00)
	BEGIN
		SET @STAMPED = N'STAMPED!';
	END
	ELSE IF(@CAP_AMOUNT < 800.00)
	BEGIN
		SET @STAMPED = N'';
	END
		RETURN @STAMPED;
END;
GO



ALTER TABLE dbo.MASTER_ORDERS
ADD STAMPED VARCHAR(255) NULL

UPDATE MASTER_ORDERS SET STAMPED = dbo.fn_MASTER_ORDERS_STEMP(TOTAL_PRICE)
FROM MASTER_ORDERS -- WHERE TOTAL_PRICE >= 800.00



EDIT: You are right, I should've used TRUNCATED instead of drop.

This post has been edited by Nitewalkr: 10 August 2016 - 11:13 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: conditions in scalar function

Posted 10 August 2016 - 11:19 AM

Quote

I didn't realize that setting the @CAP_AMOUNT will create issues while comparing conditions with TOTAL_PRICE.

Why would you think it wouldn't? Much like imperative programming languages lines evaluate from top to bottom.
Was This Post Helpful? 1
  • +
  • -

#7 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,569
  • Joined: 12-December 12

Re: conditions in scalar function

Posted 10 August 2016 - 11:22 AM

A small note that you don't need ELSE IF, if it isn't greater than or equal to 800 it must be less. Just ELSE.

Nor do you need BEGIN..END for a single statement, although it is useful to include if you might expand to more statements later. In that sense, it is a useful habit to acquire, but I thought I'd mention it anyway ;)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1