Code Sample: SCRS_GetCompany_CUSTOM SCRM Stored Procedure
This sample demonstrates the changes made to the SCRM 2006 stored procedure SCRS_GetCompany in the task How to Switch the Filter Order of an SCRM Report. SCRS_GetCompany is in the SCRM 2006 SystemCenterPresentation database.
Requirements
SCRM 2006
Example
SCRS_GetCompany is changed so that it no longer returns the full list of company names. Instead, it requires a program name used to provide a list of companies associated with a specific program.
USE [SystemCenterPresentation]
GO
/****** Object: StoredProcedure [dbo].[SCRS_GetCompany_CUSTOM] Script Date: 04/06/2006 12:06:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SCRS_GetCompany_CUSTOM] ( @LocaleID int , @ProductName NVARCHAR(255) = '<ALL>')
AS
-- Prevent number of rows affected being returned as part of results.
SET NOCOUNT ON
/*Select Company Name */
IF @ProductName = '<ALL>'
BEGIN
SELECT 1 AS Sequence,'<ALL>' AS ProductCompanyName, dbo.fn_getLocalizedString('<ALL>','Misc',@LocaleID) AS ProductCompanyNameLabel
UNION
SELECT DISTINCT 2 AS Sequence,ARPUserPublisher AS ProductCompanyName , ARPUserPublisher AS ProductCompanyNameLabel
FROM [dbo].[SCRS_AddRemoveProgram_DIMENSION_View]
WHERE ARPUserPublisher IS NOT NULL
ORDER BY Sequence, ProductCompanyNameLabel
END
ELSE
BEGIN
SELECT 1 AS Sequence,'<ALL>' AS ProductCompanyName, dbo.fn_getLocalizedString('<ALL>','Misc',@LocaleID) AS ProductCompanyNameLabel
UNION
SELECT DISTINCT 2 AS Sequence,ARPUserPublisher AS ProductCompanyName , ARPUserPublisher AS ProductCompanyNameLabel
FROM [dbo].[SCRS_AddRemoveProgram_DIMENSION_View]
WHERE ARPUserPublisher IS NOT NULL
AND ARPUserDisplayName = @ProductName
ORDER BY Sequence, ProductCompanyNameLabel
END
--To Restore Database Settings
SET NOCOUNT OFF
See Also
Tasks
How to Create an SCRM Stored Procedure
How to Switch the Filter Order of an SCRM Report
Reference
SCRM 2006 SCRS_AddRemoveProgram_DIMENSION_View
SCRS_GetCompany Stored Procedure