SQL or SP ?

[Databaseguy]

What's the better way ?

#1
Private Sub TestIt()

Dim rst as new adodb.recordset
strSQLStyleBody = "SELECT StyleID, [Body] from tblOrderDet WHERE JJNO = '" & Me.cboJob & Me.txtExt & Me.txtRev & "' GROUP BY StyleID, [Body]"
rstStyleBody.Open strSQLStyleBody, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

End Sub

#2

Private Sub TestIt()

Dim rst as new adodb.recordset
rstStyleBody.Open sp_SQLStyleBody, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

End Sub

Where the SQL in the stored procedure sp_SQLStyleBody is exactly the same as the first example with the addition of parameters.
Tyrone Lumley
augerinn@gte.net

 

[nealvenables]

The stored procedure approach. SPs...

(i) provide better performance through being pre-compiled and optimised.
(ii) make code management easier
(iii) make for more readable code at the client side.

 

[bytehd]

Ditto.
Once you get your SQL PASSTHRU optimized, its still not until it becomes compiled.

Always wondered through: what intermediate, run-time, or pseudo-compiled language T-SQL compiles down to.
Probably MS-C??