If it's a multi-select then SSRS passes the SSRS parameter values as a comma delimited string.

If user selects CA then 'CA' is passed to the stored proc. If the user selects CA, CT, TX and VA then the value 'CA,CT,TX,VA' is passed.

Using the splitter that FridayNightGiant referenced in his original response you can write your stored proc logic similar to my example below (I wrote my sample sql in a way that you can just copy/paste and run it locally provided that you have set up delimitedsplit8k).

-- sample of your database table

DECLARE @sampleTable TABLE(id int identity, SomeValue int, [state] varchar(100))

INSERT @sampleTable([state],SomeValue) VALUES ('CA',3),('CT',5),('TX',6),('IL',9);

-- What a multi-select SSRS parameter looks like

DECLARE @states varchar(1000) = 'CA,CT,TX,VA';

-- How you would use a splitter to pass the SSRS multi-select values to your stored proc:

-- Option #1

SELECT st.SomeValue, st.[state]

FROM @sampleTable st

WHERE [state] IN (SELECT item FROM dbo.DelimitedSplit8K(@states,','));

-- Option #2

SELECT st.SomeValue, st.[state]

FROM @sampleTable st

WHERE EXISTS (SELECT item FROM dbo.DelimitedSplit8K(@states,',') WHERE Item = st.[state]);

-- Option #3

SELECT st.SomeValue, st.[state]

FROM @sampleTable st

JOIN dbo.DelimitedSplit8K(@states,',') ds ON ds.item = st.[state];