ben.brugman (10/2/2015)
Sorry, I think I was asking for something which is not obtainable.And I wasn't clear enough in my question.
Because I want the code to cut and paste between the SSRS and SSMS, I do not want to change the code itself, only the preparation of the parameter.
And as far I understand it now that is not possible.
During the all the report server alters the code before it get's send to the server. So the actual code that is shown in the query builder of the report server does not run as it is, it needs some alteration. So took me some time to figure that out.
The actual report builder is reluctant to use a stored procedure for obtaining the information, and to figure out how to tune the query, I like to use SSMS, so I thought I just could cut and paste the code into some environments where the parameters where fixed and prepared for testing and then run the code as it came from SSRS. Not possible as I understand it know.
Took some time to figure that one out, so thanks for your time and attention,
Ben
Not unattainable at all... The only thing SSRS does to the code is replace the @Parameter with the actual parameter value.
In the case of a multi-valued parameter, the value is a comma delimited aray of values.
So... If you have SSRS code that looks like this...
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
p.BusinessEntityID IN (@CustomerID)
You just need to execute the code in SSMS like this...
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
p.BusinessEntityID IN (1,2,3,4,5,6,7,8,9)
HTH,
Jason
edit... If you want to continue to use a parameter, do a Lowell suggested and use a splitter function...
DECLARE @CustomerID VARCHAR(8000) = '1,2,3,4,5,6,7,8,9';
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
p.BusinessEntityID IN (SELECT ds.Item FROM dbo.DelimitedSplit8K(@DustomerID, ','))