Using Stored Procedures in ColdFusion / Lucee 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
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?