Is this possible with a stored procedure

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)

Is this possible with a stored procedure

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-15 : 05:21:22

Hi,

I've never written an SP before, but i've got a requirement where one might come in handy, if it can do what i'm asking.

I've got a table with a list of vehicles in, theres various columns but the ones relating to this are make,model,price

AUDI A4 145.56
AUDI A3 153.34
BMW 3 SERIES 123.45
BMW 5 SERIES 123.45

In another table i've got rules which define what I want to do with the price for each model, for example

AUDI A4 +7
AUDI A3 -4
BMW 3 SERIES +10%
BMW 5 SERIES -5%

The rules can only ever by + or -, if the percent sign is there then its percent, if not its just the figure itself that I want to add or subtract.

The end result I want a view which gives me each make and model from table 1 and the manipulated prices based on the rules in table 2. I could do it quite easy in visual basic at the application level. But could the processing be carried out directly on the server using an SP?

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-15 : 07:09:28

If you are using .NET, then you CAN do it in VB, and let the CLR worry about the SQL end of it.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 07:20:25

[code]DECLARE @price TABLE
(
make varchar(10),
model varchar(10),
price decimal(8,2)
)

INSERT INTO @price
SELECT 'AUDI', 'A4', 145.56 UNION ALL
SELECT 'AUDI', 'A3', 153.34 UNION ALL
SELECT 'BMW', '3 SERIES', 123.45 UNION ALL
SELECT 'BMW', '5 SERIES', 123.45

DECLARE @rule TABLE
(
make varchar(10),
model varchar(10),
[rule] varchar(10)
)

INSERT INTO @rule
SELECT 'AUDI', 'A4', '+7' UNION ALL
SELECT 'AUDI', 'A3', '-4' UNION ALL
SELECT 'BMW', '3 SERIES', '+10%' UNION ALL
SELECT 'BMW', '5 SERIES', '-5%'

SELECT p.make, p.model, p.price, r.[rule],
new_price = CASE WHEN type = 'P' THEN price * (100 + value) / 100.0
ELSE price + value
END
FROM @price p INNER JOIN
(
SELECT make, model, [rule],
type = CASE WHEN CHARINDEX('%', r.[rule]) <> 0 THEN 'P' ELSE 'V' END,
value = CONVERT(decimal(10,2), REPLACE(r.[rule], '%', ''))
FROM @rule r
) r
ON p.make = r.make
AND p.model = r.model

/*
make model price rule new_price
---------- ---------- ---------- ---------- ----------------------------
AUDI A4 145.56 +7 152.560000000
AUDI A3 153.34 -4 149.340000000
BMW 3 SERIES 123.45 +10% 135.795000000
BMW 5 SERIES 123.45 -5% 117.277500000

(4 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources