Fetch the Option-set label and value

In a select query, if the field type is an option-set it only displays the Value and not the Label.
But sometimes you need the label instead of the Value in the result set.

To get the list of Values and Labels for an option-set just pass the entity name & the option-set field name:

SELECT AttributeValue,Value 
From StringMap s inner Join EntityLogicalView e
ON s.ObjectTypeCode = e.ObjectTypeCode
where e.Name = '<entityname>' and s.AttributeName = '<option-set field name>'


You can use this as a sub-query in you main SELECT query like this:

SELECT Value from StringMap s inner join EntityLogicalView e on s.ObjectTypeCode = e.ObjectTypeCode where e.Name = '<entityname>' and s.AttributeName = '<fieldname>'
AND AttributeValue = <recordvalue>

Using Multiple Integer Valued Parameters in SSRS

This approach is to resolved the issues while using multi-valued integer values as parameters.


Step 1: 

Create a Table-Valued function that would accept the output from the multivalued integer parameter and generate a table format list.

CREATE FUNCTION FnSplit
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS 
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

Step 2:

Now in your Stored Procedure use the function by passing you paramter like this:

pgcrm_ApplicationService IN (SELECT Value FROM FnSplit(@ApplicationService,','))