Update SSRS report path

Here is a handy little script to help update SSRS report paths

IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'cssp_Upsert_SSRS_Path '
)
DROP PROCEDURE dbo.cssp_Upsert_SSRS_Path
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.cssp_Upsert_SSRS_Path (
@ReportReference VARCHAR(128), --the existing report reference in WSRepts table, or the new one you want to add
@ReportName VARCHAR(255), --the name if the report without the extension (as it appears in the url)
@Product INT -- 1=Job Cost, 2=Service, 3=EMS, 4=shared
)
AS
/*
KEY2ACT, Inc.
History:
20180419 djedziniak initial version
*/
BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE @DbName AS VARCHAR(128),
@ReportServerUrl AS VARCHAR(512),
@RptServerRef AS VARCHAR(255),
@lastSlashIndex AS INT,
@slashIndex AS INT,
@productfolder AS VARCHAR(255)
SELECT @DbName = DB_Name(),
@RptServerRef = coalesce((
SELECT Report_Server_URL
FROM DYNAMICS.dbo.SY40800
WHERE SITENAME = 'Company'
), '')

IF (LEN(RTRIM(@RptServerRef)) > 0)
BEGIN
/* Only when has a Report Server reference will it insert any records in regards to Reporting Services. */
SELECT @lastSlashIndex = 0,
@slashIndex = Charindex('/', @RptServerRef);

/* First, find the last forward slash */
WHILE (@slashIndex > 0)
BEGIN
IF (@slashIndex > @lastSlashIndex)
SELECT @lastSlashIndex = @slashIndex;

SELECT @slashIndex = Charindex('/', @RptServerRef, @slashIndex + 1);
END
IF (@lastSlashIndex > 0)
SELECT @ReportServerUrl = LEFT(@RptServerRef, @lastSlashIndex);
END

SELECT @productfolder = CASE @Product
WHEN 1
THEN '/Signature Job Cost/'
WHEN 2
THEN '/Signature Service/'
WHEN 3
THEN '/Signature Equipment/'
WHEN 4
THEN '/Signature Shared/'
ELSE ''
END

IF EXISTS (
SELECT NULL
FROM dbo.WSRepts
WHERE ReportReference = @ReportReference
)

UPDATE dbo.WSRepts
SET ReportLocation = coalesce(@ReportServerUrl + @DbName + @productfolder + @ReportName, '')
WHERE ReportReference = @ReportReference AND LEN(RTRIM(ReportLocation)) <= 0;
ELSE
INSERT INTO dbo.WSRepts (
ReportReference,
ReportLocation
)
SELECT @ReportReference,
coalesce(@ReportServerUrl + @DbName + @productfolder + @ReportName, '')
END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SELECT @ErrorMessage = 'ERROR IN cssp_Upsert_SSRS_Path ' + @ErrorMessage
RAISERROR (
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO

GRANT EXECUTE
ON dbo.cssp_Upsert_SSRS_Path
TO DYNGRP
GO

PRINT 'CREATED cssp_Upsert_SSRS_Path '
GO