Chained Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Chained Stored Procedures

Author

Topic

Marka
Starting Member

5 Posts

Posted - 2011-09-20 : 14:33:45

Hi

I need some help. I have a stored procedure (sp1) which is a basic select query using dynamic sql which uses the text output of another stored procedure (sp2) as an input.

When I execute sp1 and examine the contents of the parameter using a print statement, the result is 0, even though when I examine the output of sp2, it is a valid text string.

Do output values of a stored procedure have a scope? Is there perhaps a specific way to execute sp2 to return the text value?

I am using SQL 2008 and using sp_executesql to execute sp1 and using execute sqltext to execute sp1. Here is a snippet of the sp where sp2 is called.

EXECUTE @USERFRAGMENT = PF_FND_USERFRAGMENT @USERID, @FUNCID, @USERFRAGMENT OUTPUT

Any help would be greatly appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-20 : 14:36:49

Pass the output param from sp1 as a paramater to sp2. Output if you need to retrieve it after sp2, input if you don't.

Marka
Starting Member

5 Posts

Posted - 2011-09-21 : 02:54:35

Hi Russell

Thanks for looking at my problem.

This is the sp as I have it currently

DECLARE
@USERFRAGMENT AS NVARCHAR(MAX),
@SQLSELECT AS NVARCHAR(MAX),
@SQLGROUP AS NVARCHAR(MAX),
@SQLWHERE AS NVARCHAR(MAX),
@SQLTEXT AS NVARCHAR(MAX),
@SQLPARMS AS NVARCHAR(MAX)

--Extracts the required data details
SET @STARTDATE = CONVERT(VARCHAR(10),@STARTDATE,111)
SET @ENDDATE = CONVERT(VARCHAR(10),@ENDDATE,111)
SET @SQLPARMS = '@STARTDATE AS DATETIME, @ENDDATE AS DATETIME, @USERID AS INT, @FUNCID AS INT, @USERFRAGMENT AS NVARCHAR(MAX), @GRIDFRAGMENT AS NVARCHAR(MAX)'

SET @SQLSELECT = N'SELECT TOP (100) PERCENT dbo.TRANSPORTREQUEST.CODE AS TRNumber, dbo.HAULIER.NAME AS Haulier, dbo.TRANSPORTREQUEST.REFERENCE AS PONumber, dbo.SHIPMENTVOUCHER.INVOICENUMBER AS InvNumber, SITE_1.SITENAME AS LoadingSite, SITE_1.SUBURB AS LoadingSuburb, SITE_1.CITYTOWN AS LoadingRegion, SITE_2.SITENAME AS DeliverySite, SITE_2.SUBURB AS DeliverySuburb, SITE_2.CITYTOWN AS DeliveryRegion, MAX(dbo.SHIPMENT.LOADINGDATE) AS LoadingDate, MAX(dbo.SHIPMENT.DELIVERYDATE) AS DeliveryDate, dbo.CLIENT.NAME AS Client, dbo.PF_P2_OPS_LOADMASTER.Total_Distance AS Distance, MAX(DATEDIFF(mi, dbo.PF_P2_OPS_LOADDETAIL.LActual_Arrival, dbo.PF_P2_OPS_LOADDETAIL.DActual_Departure)) AS Time, dbo.VEHICLECLASS.CLASS AS VehicleType, dbo.VEHICLE.VEHICLE AS Vehicle, dbo.PF_P2_OPS_LOADMASTER.Total_Stops AS Drops, SUM(DISTINCT dbo.PF_P2_OPS_LOADDETAIL.Quantity) AS Quantity, SUM(DISTINCT dbo.PF_P2_OPS_LOADDETAIL.Weight) AS Weight, SUM(DISTINCT dbo.PF_P2_OPS_LOADDETAIL.Volume) AS Volume, CONVERT(money, dbo.SHIPMENT.PLANNEDTARIFFAMOUNT) AS ARAmount, CONVERT(money, dbo.TRANSPORTREQUEST.PLANNEDTARIFFAMOUNT) AS APAmount, CONVERT(money, dbo.SHIPMENT.PLANNEDTARIFFAMOUNT - dbo.TRANSPORTREQUEST.PLANNEDTARIFFAMOUNT) AS Profit, dbo.CONTRACT.NAME AS Contract, dbo.STATUSNAME.NAME AS Status, dbo.SHIPMENT.ORDERNUMBER AS OrderNumber, dbo.SHIPMENT.SRREFERENCE AS ReferenceNumber FROM dbo.VEHICLECLASS RIGHT OUTER JOIN dbo.HAULIER INNER JOIN dbo.CONTRACT INNER JOIN dbo.TARIFF ON dbo.CONTRACT.CONTRACTID = dbo.TARIFF.CONTRACTID INNER JOIN dbo.STATUSNAME INNER JOIN dbo.TRANSPORTREQUEST INNER JOIN dbo.TRANSPORTREQUESTSHIPMENTS ON dbo.TRANSPORTREQUEST.TRANSPORTREQUESTID = dbo.TRANSPORTREQUESTSHIPMENTS.TRANSPORTREQUESTID INNER JOIN dbo.SHIPMENT ON dbo.TRANSPORTREQUESTSHIPMENTS.SHIPMENTID = dbo.SHIPMENT.SHIPMENTID ON dbo.STATUSNAME.STATUSNAMEID = dbo.TRANSPORTREQUEST.PHYSICALSTATUSNAMEID INNER JOIN dbo.STATUSNAME AS STATUSNAME_1 ON dbo.TRANSPORTREQUEST.FINANCIALSTATUSNAMEID = STATUSNAME_1.STATUSNAMEID ON dbo.TARIFF.TARIFFID = dbo.SHIPMENT.TARIFFID AND dbo.CONTRACT.CLIENTID = dbo.SHIPMENT.CLIENTID INNER JOIN dbo.CLIENT ON dbo.SHIPMENT.CLIENTID = dbo.CLIENT.CLIENTID ON dbo.HAULIER.HAULIERID = dbo.TRANSPORTREQUEST.HAULIERID INNER JOIN dbo.SITE AS SITE_2 INNER JOIN dbo.SERVICEREQUEST INNER JOIN dbo.SITE AS SITE_1 ON dbo.SERVICEREQUEST.ORIGINID = SITE_1.SITEID ON SITE_2.SITEID = dbo.SERVICEREQUEST.DESTINATIONID ON dbo.SHIPMENT.SERVICEREQUESTID = dbo.SERVICEREQUEST.SERVICEREQUESTID LEFT OUTER JOIN dbo.SHIPMENTVOUCHER ON dbo.SHIPMENT.SHIPMENTID = dbo.SHIPMENTVOUCHER.SHIPMENTID ON dbo.VEHICLECLASS.VEHICLECLASSID = dbo.TRANSPORTREQUEST.VEHICLECLASSID LEFT OUTER JOIN dbo.VEHICLE ON dbo.TRANSPORTREQUEST.VEHICLEID = dbo.VEHICLE.VEHICLEID FULL OUTER JOIN dbo.PF_P2_OPS_LOADMASTER INNER JOIN dbo.PF_P2_OPS_LOADDETAIL ON dbo.PF_P2_OPS_LOADMASTER.MasterID = dbo.PF_P2_OPS_LOADDETAIL.MasterID ON dbo.TRANSPORTREQUEST.TRANSPORTREQUESTID = dbo.PF_P2_OPS_LOADMASTER.TRID'

-- SET @SQLWHERE = N' WHERE 1 = 1 ' ***TO BE USED WHEN THERE IS NO DEFAULT WHERE CLAUSE***
SET @SQLWHERE = N' WHERE (dbo.SHIPMENT.ISALPHA <> ''T'') AND (dbo.TRANSPORTREQUEST.PHYSICALSTATUSNAMEID <> 2150) AND (CONVERT(varchar(10), dbo.TRANSPORTREQUEST.STARTDATE, 111) BETWEEN @STARTDATE AND @ENDDATE)'

EXECUTE @USERFRAGMENT = PF_FND_USERFRAGMENT @USERID, @FUNCID, @USERFRAGMENT OUTPUT

SET @SQLWHERE = @SQLWHERE + ISNULL(@USERFRAGMENT, '') + ISNULL(@GRIDFRAGMENT, '')

SET @SQLGROUP = N' GROUP BY dbo.TRANSPORTREQUEST.CODE, dbo.CLIENT.NAME, dbo.HAULIER.NAME, dbo.TRANSPORTREQUEST.REFERENCE, dbo.VEHICLECLASS.CLASS, dbo.VEHICLE.VEHICLE, SITE_1.SITENAME, SITE_2.SITENAME, CONVERT(varchar(10), dbo.SHIPMENT.LOADINGDATE, 111), CONVERT(varchar(10), dbo.SHIPMENT.DELIVERYDATE, 111), dbo.STATUSNAME.NAME, dbo.CONTRACT.NAME, dbo.PF_P2_OPS_LOADMASTER.Total_Stops, SITE_1.SUBURB, SITE_1.CITYTOWN, SITE_2.SUBURB, SITE_2.CITYTOWN, dbo.PF_P2_OPS_LOADMASTER.Total_Distance, CONVERT(money, dbo.SHIPMENT.PLANNEDTARIFFAMOUNT), CONVERT(money, dbo.TRANSPORTREQUEST.PLANNEDTARIFFAMOUNT), dbo.SHIPMENTVOUCHER.INVOICENUMBER, dbo.SHIPMENT.ORDERNUMBER, dbo.SHIPMENT.SRREFERENCE, CONVERT(money, dbo.SHIPMENT.PLANNEDTARIFFAMOUNT - dbo.TRANSPORTREQUEST.PLANNEDTARIFFAMOUNT)ORDER BY TRNumber DESC'

SET @SQLTEXT = @SQLSELECT + @SQLWHERE + @SQLGROUP

BEGIN TRY

PRINT @SQLSELECT + @SQLWHERE + @SQLGROUP

EXECUTE sp_executesql @SQLTEXT, @SQLPARMS, @STARTDATE, @ENDDATE, @USERID, @FUNCID, @USERFRAGMENT, @GRIDFRAGMENT

END TRY
BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_PROCEDURE() AS ErrorSource

END CATCH

Any help would be appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources