Is this a good advantage of using inline SQL over stored procedures?

Question

Here is an argument for SP that I have not heard. Flamers, be careful with a tick down,

Since there is an overhead for every trip to the database server, I would suggest that the POSSIBLE reason for placing your SQL in SP in the embedded code is because you are more isolated for change without sacrificing performance.

For instance. Let's say you need to execute Query A, which returns an integer.

Then, later, the requirements change, and you decide that the results of the scalar will be> x, and then, and only then you need to execute another query. If you made the first query in SP, you can easily check the result of the first query and conditionally execute the second SQL in the same SP.

How would you do this efficiently in embedded SQL without having to run a separate query or an unnecessary query?

Here is an example:

--This SP may return 1 or two queries. 

SELECT @CustCount = COUNT(*) FROM CUSTOMER 

IF @CustCount > 10 
   SELECT * FROM PRODUCT 

Could this / what is the best way to do this in embedded SQL?

Answer 1

Very convincing article

SQL and stored procedures will be present throughout your data.

Client languages ​​come and go, and each time you have to re-implement embedded SQL.

Answer 2

In the example you specify, the save time sends one scalar value and one additional request on the wire. This is negligible in any reasonable scenario. This does not mean that there cannot be other valid reasons for using SP; it’s just not such a reason.

Answer 3

, - SP, , . , SP, , , db.

, , , SP, , - .

Answer 4

SQL ?

. SQL Server CASE.

Answer 5

, WHERE sproc:

WHERE (all your regular conditions)
AND   myScalar > myThreshold

Answer 6

SP:

• ( )
• ( )
• , SQL, .

:

• ( App + )

2 ...

:

select * from products where (select count(*) from customers>10)

Answer 7

SP ( , uber , proc ... CLR ). LINQ to SQL, LINQ. , , , , , ( ). SQL , , . , , . . , , . , . , , , .