Thread: Trying to Track Down Where a Stored Procedure Is Called

Hi,

I'm assisting in a project where the previous developer went AWOL. We need to change the way the system is handling entries in the VendorFullSKU field of the Inventory table. The previous developer wrote a stored procedure as follows:

Code:

UPDATE Inventory SET
		VendorFullSKU = dbo.GetVendorFullSKU(InventoryID)
	WHERE InventoryID IN 
	(SELECT InventoryID FROM Inventory WHERE LEN(ISNULL(VendorFullSKU, '')) <= 0 AND VariantID > 0)

The GetVendorFullSKU is a SQL Function written as:

Code:

DECLARE @VendorFullSKU	nvarchar(100) = ''
	
	SELECT @VendorFullSKU = ISNULL(dbo.Inventory.VendorFullSKU, '')
	FROM dbo.ProductVariant INNER JOIN
         dbo.Inventory ON dbo.ProductVariant.VariantID = dbo.Inventory.VariantID
    WHERE dbo.Inventory.InventoryID=@InventoryID

	SELECT @VendorFullSKU = LTRIM(RTRIM(@VendorFullSKU))
    IF @VendorFullSKU <> ''
		RETURN @VendorFullSKU

	SELECT   @VendorFullSKU =
		ISNULL((SELECT TOP (1) DTBA FROM dbo.zProductSku WHERE (ProductID = pv.ProductID)), 'U') 
		+ '-' + CAST(pv.ProductID AS nvarchar(50)) + '-' + CAST(pv.VariantID AS nvarchar(50)) 
        + '-' + REPLACE(dbo.GetSizeSkuModifier(i.InventoryID), '-', '') 
        + '-' + SUBSTRING(REPLACE(REPLACE(dbo.GetColorSkuModifier(i.InventoryID), '-', ''), ' ', ''), 1, 4)
        + '-' + CAST(i.InventoryID AS nvarchar(20))
	FROM    dbo.Inventory AS i INNER JOIN
            dbo.ProductVariant AS pv ON i.VariantID = pv.VariantID
    WHERE i.InventoryID=@InventoryID	
	RETURN @VendorFullSKU

We want to prevent the above stored procedure from being run. Unfortunately, I'm not able to track down within the codebase where it's being called. I've found references in the WSI.cs class relative to VendorFullSKU, but cannot find any scheduled jobs or bits of code that specifically call this function or stored procedure.

Can anyoone suggest where I should look either in our codebase or server logs for references to the above stored procedure / function? The goal is to disable it completely.

Thanks,
Sid

You can just modify the stored procedure itself by inserting at the top a RETURN function.

Code:

ALTER PROCEDURE dbo.MyProcedure
    @someVar INT
AS
BEGIN

    RETURN

    [... rest of procedure here...]

END

This will "run" the procedure but return nothing. You could also comment out everything below the RETURN if you care to with some comments to explain why it's been disabled for future developers' sake.

If the app is expecting "something" you can add:

Code:

SELECT
   NULL AS Col1
  ,NULL AS Col2
  --etc

RETURN

Just put in whatever default values the app needs and you should be good to go!