CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user4992798
user4992798

Reputation:

Are stored procedures declarative or imperative?

Please read the following example first.

I have a database table named Product and this table has a column named Id whose data type is INT.

I'm writing a stored procedure that adds a new Product. One of the rules of the database is when adding new Product, the id assigned for it must be the smallest integer started from 1, and of course the ids are unique.

For example, if the existent ids are 1, 2, 3, 4, 5, 6 then the new Product will have an id of 7. But if the existent ids are 1, 2, 3, 5, 6, 8 the new id will be 4.

This is my try:

DECLARE @newId INT
SET @newId = 1
WHILE EXISTS (SELECT * FROM Product WHERE Product.id = @newId)
    SET @newId = @newId + 1
/*Then use this @newId to insert new item into table Product*/

But my friend told me this code was not efficient because the query inside the while loop's condition would be evaluated for each iteration.

And this is a part of his code:

DECLARE @currentId INT, @lastId INT, @newId INT
SET @lastId = 0

DECLARE idCursor CURSOR FOR SELECT Product.id FROM Product ORDER BY Product.id
OPEN idCursor
FETCH NEXT FROM idCursor INTO @currentId

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @currentId <> @lastId + 1
            BREAK

        SET @lastId = @currentId
        FETCH NEXT FROM idCursor INTO @currentId
    END

SET @newId = @lastId + 1
CLOSE idCursor
DEALLOCATE idCursor
/*Then use this @newId to insert new item into table Product*/

In my opinion, SQL is a declarative language, no matter what we write our codes the DBMS will rearrange them to achieve a good execution plan, so I give the optimization part for the DBMS and try to keep things simple and more readable. Besides, finding the new id is only a small part of the stored procedure.

But in my friend's opinion, when writing stored procedure it becomes an imperative language and the code author takes the responsibility for the code efficiency. And he said what I did was to make things simple for me, not for the system.

So my questions are:

  1. Is SQL still or no more declarative when we write stored procedures? Or it's somewhere in the middle?

  2. What is considered to be a better practice when writing store procedures: keeping them simple or taking all care of their efficiency?

Upvotes: 1

Views: 388

Answers (1)

George Mastros
George Mastros

Reputation: 24498

I would argue that every language is a mixture of what you call declarative and imperative.

A person can write bad code (or great code) in any language. In my opinion, if you want to write great code, you must know the strengths and weaknesses of the language you are writing the code in.

Both code block you show will perform poorly because of the loops involved. A set based approach to this problem would look something like this:

Declare @NewId Int

Select  Top 1 @NewId = RowId
From    (
        Select  Id, Row_Number() Over (Order By Id) As RowId
        From    Product
        ) As A
Where   Id <> RowId
Order By RowId

Select @NewId

TSQL has a weakness where loops are involved. In 99.9% of situations, you would be better off avoiding loops.

Upvotes: 3

Related Questions