Using Stored Procedures in ColdFusion / Lucee

Question

This is how I write all my database interactions now in Lucee:

storedproc 
 procedure='OrderDetail.WhereOrderHeaderID' {
 procparam value=session.Usr.UsrID;
 procparam value=url.OrderHeaderID;
 procresult resultset=1 name='OrderDetail';
 procresult resultset=2 name='OrderHeader';
}

Assuming I've done this in SQL Server:

create schema OrderDetail authorization dbo
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'OrderHeader.[get]') AND type in (N'P', N'PC'))
DROP PROC OrderHeader.[get]
GO
CREATE PROC OrderHeader.[get]
(@UsrID Int
,@OrderHeaderID Int
) AS
SELECT OrderHeaderInfo
FROM OrderHeader
WHERE OrderHeaderID = @OrderHeaderID
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'OrderDetail.WhereOrderHeaderID') AND type in (N'P', N'PC'))
DROP PROC OrderDetail.WhereOrderHeaderID
GO
CREATE PROC OrderDetail.WhereOrderHeaderID
(@UsrID Int
,@OrderHeaderID Int
) AS
SELECT Item,Qty
FROM OrderDetail
WHERE OrderHeaderID = @OrderHeaderID
exec OrderHeader.[get] @UsrID,@OrderHeaderID

• Malachi: could you share more of what you are looking for in this review. I can't see anything more than the need for better formatting in your SQL.
• Leigh: Also, in terms of SQL, consider using SET NOCOUNT ON / OFF in your stored procedures. Enabling the setting at the beginning of a procedure, and disabling at the end, suppresses any extraneous '(x) rows affected' messages generated within the procedure. As those messages are normally discarded by CF anyway, suppressing them saves a bit of network traffic.

Answer 1

your code could use some formatting so that it can be read without much effort

create schema OrderDetail authorization dbo
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'OrderHeader.[get]') AND type in (N'P', N'PC'))
DROP PROC OrderHeader.[get]
GO
CREATE PROC OrderHeader.[get]
(@UsrID Int
,@OrderHeaderID Int
) AS
SELECT OrderHeaderInfo
FROM OrderHeader
WHERE OrderHeaderID = @OrderHeaderID
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'OrderDetail.WhereOrderHeaderID') AND type in (N'P', N'PC'))
DROP PROC OrderDetail.WhereOrderHeaderID
GO
CREATE PROC OrderDetail.WhereOrderHeaderID
(@UsrID Int
,@OrderHeaderID Int
) AS
SELECT Item,Qty
FROM OrderDetail
WHERE OrderHeaderID = @OrderHeaderID
exec OrderHeader.[get] @UsrID,@OrderHeaderID

you also should be consistent about Capitalization of Keywords in SQL, it will make it easier to see what is going on in your SQL.

CREATE SCHEMA OrderDetail authorization dbo
IF EXISTS (SELECT * 
 FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'OrderHeader.[get]') 
 AND type IN (N'P', N'PC'))
DROP PROC OrderHeader.[get]
GO
CREATE PROC OrderHeader.[get]
(
 @UsrID INT
 ,@OrderHeaderID INT
) AS
SELECT OrderHeaderInfo
 FROM OrderHeader
 WHERE OrderHeaderID = @OrderHeaderID
IF EXISTS (SELECT * 
 FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'OrderDetail.WhereOrderHeaderID') 
 AND type IN (N'P', N'PC'))
DROP PROC OrderDetail.WhereOrderHeaderID
GO
CREATE PROC OrderDetail.WhereOrderHeaderID
(
 @UsrID INT
 ,@OrderHeaderID INT
) AS
SELECT Item
 , Qty
 FROM OrderDetail
 WHERE OrderHeaderID = @OrderHeaderID
EXEC OrderHeader.[get] @UsrID,@OrderHeaderID

That is much easier to read, wouldn't you agree?

• John Farrar: You did not answer the question asked by the user. While the SQL comments are nice, they are by far secondary to the core question.
• Malachi: what question, @JohnFarrar? all I see is a request for a general review of the code given, I don't see any other question.