Pass String into @Parameter from a VS report

Question

I did ask a question about this but still cant work out how to get this working, ive looked at some examples where people have used functions, table variables, create types and cant really get any working so wondering if someone could help and maybe explain a little bit of the code they write if its complicated.

Im using Visual studio 2012 to create reports and have on the report a drop down list populated with company names that a user can tick to view info about each of the companies.

What I want to do is get all the company IDs from this drop down and pass it into an In clause in my sql where statement. This is a simplified version of what I have got, im new to this so just trying to see what works for when I have to look at more complex stuff in the future.

Ideally the line ive comented out will be the line that should be used and the values passed into this, but ive added the equivalent line below which I have been using for testing, I get an error on the comma between the 1,2 and

error message syntax error near ','.

If someone can help with how to get this to work using the @companynameParam I have comented out that would be great. CompanyNameParam is the name of the drop down on the VS report.

Declare @ContactID int

--set @ContactID = @CompanyNameParam
set @ContactID = 1,2,3,4,5,6,7,8,9


select CompanyName as 'reportcompanyname'
from company co inner join contacts c on c.CompanyID = co.CompanyID
where ContactID in (@ContactID);

Answer 1

You are getting an error because SET command is used to assign single / scalar value to a single variable at a time and you are trying to assign multiple comma separated string values to a single integer variable @ContactID in one go.

Correct approach is to first declare a local variable of type varchar and not Int as @CompanyNameParam must be a string.

Declare @ContactID varchar(500);

Then convert comma separated string into a table. [There can be many ways to do this] I'm just giving an example. Create a user defined function as:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
        SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
    RETURN
END
GO

Once done you can re-write the sproc as:

select CompanyName as 'reportcompanyname'
from company co inner join contacts c on c.CompanyID = co.CompanyID
where ContactID in (SELECT * FROM dbo.CSVToTable(@CompanyNameParam));

Check DEMO here..