Set based or Procedural based - 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 2008 Forums

Transact-SQL (2008)

Set based or Procedural based

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 06:05:53

Hi Friends,
I am working as SQL Server Programmer ( Intermediate Level ) and writes stored procedures in my most of work.
I want to know Is it necessary to write the SQL Server Code in Set-based instead of procedural-based?

I know It needs practice but how can one adhere to Set based Coding methodolgy?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 06:26:40

One of the best things to do when coding a stored proc or similar is to ask:

How would I do this thing if I was given a List of values rather than one value?

And think in terms of columns rather than rows.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 07:27:46

Hi Charles
Your comments are interesting.
Any example would have been good :)
i'll try.
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 07:58:32

An example: Assume you have a table that lists all the products of your company. For example, this would create a sample table with 3 products.

CREATE TABLE #Products
(ProductId INT, UnitPrice DECIMAL(18,4));

INSERT INTO #Products VALUES
(1,201.75),
(2,33.4),
(3,98.77);

Now you are asked to raise the price of all products by 5%. How would you do it in T-SQL?

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 08:25:38

Hi Sunita,
In the first sight of looking at the problem it seems it isn't possible to solve it in single update statement and like most of programmers ( am not saying Developers

) I would go for Cursor.

Plz give ur suggestion on it

Thanks,
vishal_sql

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 08:35:58

I beleive we need to raise price of few Productid in table
and for that needs to use cursor (take the required productid and raise the price of those products in cursor)
Coming back to root of question How can we do it using SET based ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 09:07:58

the code to do it would be simply this:


UPDATE p SET
    [UnitPrice] = [UnitPrice] * 1.05
FROM
    products AS p

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 09:10:31

In set-based programming, instead of using a cursor or while loop, you would simply run a single update statement which would update all the rows.

UPDATE #Products SET UnitPrice = UnitPrice*1.05;

If you wanted to update only a subset then you could still use the set-based update but this time specifying a WHERE clause to do the updates only on the subset. If you were asked to update the prices only for products whose product id is less than or equal to two:

UPDATE #Products SET UnitPrice = UnitPrice*1.05
WHERE ProductId <= 2;

You can find lot of resources on set-based programming on the web. Also, many basic T-SQL books should cover the elements of it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 09:13:59

and if you wanted to do this for only some group of productId's then you could do something like this:


DECLARE @products TABLE (
	[ProductID] INT
	, [UnitCost] MONEY
	)

INSERT @products ([ProductID], [UnitCost]) VALUES
(1,201.75),
(2,33.4),
(3,98.77);


-- Display Products
SELECT * FROM @products

-- Update all prices by 5%
UPDATE p SET [UnitCost] = [UnitCost] * 1.05 FROM @products AS p

SELECT 'After all upped by 5%', * FROM @products

-- Update some list of prices by 5% (Products 1 and 3)
DECLARE @updates TABLE ( [ProductID] INT )
INSERT @updates([ProductID]) VALUES (1), (3)

UPDATE p SET [UnitCost] = [UnitCost] * 1.05
FROM
	@products AS p
	JOIN @updates AS u ON u.[ProductID] = p.[ProductID]
	
SELECT 'After 1 and 3 upped by 5%', * FROM @products

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 09:56:07

It means for solving any problem you have to think that I will solve the problem without using Cursor or while loop and try to do it in single query.( except some cases where should use cursor's eg:- SSRS queries)
Great comments Sunita and Charles.Thanks.
It would definately help me.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-09 : 10:00:52

if you just remember that a single value is just a special case of multiple values then you'll do fine.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 10:05:19

quote:
Originally posted by Vishal_sql

It means for solving any problem you have to think that I will solve the problem without using Cursor or while loop and try to do it in single query.( except some cases where should use cursor's eg:- SSRS queries)
Great comments Sunita and Charles.Thanks.
It would definately help me.

Not exactly. Why should you use cursors for SSRS queries? it can also be using set based techniques and really depends on scenario. You cant predetermine that it requires cursor.

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

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 10:19:42

Hi Visakh ,
I Agreed on your comments.One will use the Cursor depending on the scenarios.
And m happy u jumped in between.
We were discussing When can we use Cusror and when can we use Set based approach.
I heard that reporting queries requires cursor as most of calculations cant be done in single queries.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 11:00:30

quote:
Originally posted by Vishal_sql

Hi Visakh ,
I Agreed on your comments.One will use the Cursor depending on the scenarios.
And m happy u jumped in between.
We were discussing When can we use Cusror and when can we use Set based approach.
I heard that reporting queries requires cursor as most of calculations cant be done in single queries.

thats not mostly true. I've been developing SSRS reports for over 6 years and during this time i never had to use cursors for my queries. Most cases you can come up with set based alternative for your cursor related queries.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources