Here is an example:
exec [dbo].[utl_OrganizationByAttribute_s] --returns all clients
exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = NULL --returns all Clients
exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = '1' --A type Clients
exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = '1, 2' --A type and B type clients
CREATE PROCEDURE [dbo].[utl_OrganizationByAttribute_s]
@OrganizationAttributeTypeID varchar(500) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@ErrorMessage AS nvarchar(250)
,@ErrorSeverity AS int
,@SprocName varchar(100) = DB_NAME(DB_ID()) + '.' + SCHEMA_NAME(SCHEMA_ID()) + '.' + OBJECT_NAME(@@PROCID,DB_ID())
BEGIN TRY
;WITH CTE_SelectOrgList
AS
(
SELECT
oa.OrganizationID
FROM
dbo.Utl_Split(',', @OrganizationAttributeTypeID) AS oatSplit
JOIN dbo.OrganizationAttrib AS oa ON oa.OrganizationAttribTypeID = CONVERT(smallint, oatSplit.SeparatedValue)
)
SELECT DISTINCT
o.OrganizationID
,o.OrgName
,o.LabAccountNumber
FROM
dbo.Organization AS o
LEFT JOIN CTE_SelectOrgList AS sol ON o.OrganizationID = sol.OrganizationID
WHERE
o.OrganizationID = CASE
WHEN @OrganizationAttributeTypeID IS NULL THEN o.OrganizationID
ELSE sol.OrganizationID
END
ORDER BY
o.OrgName
END TRY
BEGIN CATCH
SET @ErrorMessage = @SprocName + ' Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + ' Line: '
+ CONVERT(nvarchar(5), ERROR_LINE()) + ' - ' + ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH
SET NOCOUNT OFF;
END
ALTER FUNCTION [dbo].[utl_Split]
(
@Seperator char(1)
,@StringToSplit varchar(MAX)
)
RETURNS @OutputTable TABLE
(
ID int
,SeparatedValue varchar(MAX)
)
AS
BEGIN
DECLARE @String varchar(MAX),@StringInput varchar(MAX),@ID int
SET @StringInput=@StringToSplit
SET @ID=0
WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Seperator, @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Seperator, @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))
SET @ID=@ID+1
INSERT INTO @OutputTable
(
ID
,SeparatedValue
)
VALUES
(
@ID
,@String
)
END
RETURN
END