I need a opinion

Question

I need a opinion

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

msimone - Friday, February 2, 2018 12:52 AM
Thanks for all, yours opinions are importants for me.

Please see my and other folks comments that tell you that your version is actually faulty because it doesn't actually do the same thing as the first code. The first code is also a total misuse of dynamic SQL and demonstrates no knowledge of that NULLs do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

RandomStream - Friday, February 2, 2018 5:48 PM
msimone - Friday, February 2, 2018 12:52 AM
Thanks for all, yours opinions are importants for me.
If you want to go beyond opinions, spend some time with this: Understanding Performance Mysteries

Better than that, their team needs a good course on the basics of querying, the basics of stored procedures, the basics of how NULL operates, and the basics of testing for correct results. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

msimone SSCommitted Points: 1842 More actions · Answer 3

Hello Jeff, thank you for yours recommendations.
There is an error in the second proc, is "id>=@id", not "@id>=@id"
In the first, if you pass null on @Id, return all rows; if you pass a value, return one row if exists.
In the second,If you pass a value for id and 1 for @rows, return one row if exists; if you pass a value lesser than all rows for @id and a big number in @rows, return all rows (the developer knows then logical business)
I would say that the procs do the same, although they seem differents.
The second proc do more things, the developer decides.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

msimone - Wednesday, February 7, 2018 7:37 AM
Hello Jeff, thank you for yours recommendations.
There is an error in the second proc, is "id>=@id", not "@id>=@id"
In the first, if you pass null on @Id, return all rows; if you pass a value, return one row if exists.
In the second,If you pass a value for id and 1 for @rows, return one row if exists; if you pass a value lesser than all rows for @id and a big number in @rows, return all rows (the developer knows then logical business)
I would say that the procs do the same, although they seem differents.
The second proc do more things, the developer decides.

And using the 2nd code you have no guarantee what rows will be returned with execution of the query as there is no ORDER BY.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Lynn Pettis - Wednesday, February 7, 2018 8:26 AM
msimone - Wednesday, February 7, 2018 7:37 AM
Hello Jeff, thank you for yours recommendations.
There is an error in the second proc, is "id>=@id", not "@id>=@id"
In the first, if you pass null on @Id, return all rows; if you pass a value, return one row if exists.
In the second,If you pass a value for id and 1 for @rows, return one row if exists; if you pass a value lesser than all rows for @id and a big number in @rows, return all rows (the developer knows then logical business)
I would say that the procs do the same, although they seem differents.
The second proc do more things, the developer decides.
And using the 2nd code you have no guarantee what rows will be returned with execution of the query as there is no ORDER BY.

And will have erratic performance. I keep fixing code of similar forms in client systems.
Don't write procedures that do multiple things. They're harder to debug and perform terribly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass