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);

You are getting an error becuase 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 values to a single variable in one go. — Deepshikha, Jun 03 '14 at 09:30

Deepshikha · Accepted Answer · 2014-06-03T10:12:21.347

0

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..

edited Jun 03 '14 at 10:12

answered Jun 03 '14 at 09:43

Deepshikha

9,896
2
21
21

ok tried that, getting error Procedure or function dbo.csvToTable has too many arguments specified. Ive just added the last bit of sql into the report in visual studio for now, is that correct or should I add everything in here? Eventually this would be a stored procedure but as im just testing things out for now though id just use it as an actual query if that makes sense... – user3691566 Jun 03 '14 at 09:57
have you tried as SELECT * FROM dbo.CSVToTable(1,2,3,4,5,6,7,8,9); if yes then it's wrong ..please confirm the data type of @CompanyNameParam if that's string it should be SELECT * FROM dbo.CSVToTable('1,2,3,4,5,6,7,8,9'); I have added demo link for reference – Deepshikha Jun 03 '14 at 10:11
Had a look at your demo and its where you have declared the 1,2,3,4,5,6,7,8 etc that I want the sql to get these values from the tickboxes on my report which I have created in CompanyNameParam, the dropdown is showing the company names, but should be pulling the companyID which is an INT. – user3691566 Jun 03 '14 at 10:41
This example did work but I also worked out how to do what I wanted using the filters in visual studio which is the part I was getting stuck on I think, thanks for the help – user3691566 Jun 03 '14 at 15:03

Pass String into @Parameter from a VS report

1 Answers1