Sql-server – Pass a parameter from SSRS to MySQL

MySQLsql serverssrsssrs-2012

Here is the query for the dataset:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = @FieldManager

FieldManager is the parameter name. It's not taking the value of the parameter.

Please help me with the syntax to pass a parameter to a MySQL query.

Best Answer

If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead.

So for your query it should look like:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = ?

That way you should get a parameter named Parameter1. If you have more than one question mark your parameters will be named Parameter1, Parameter2 etc in the same order as they appear in the query.

This isn't specific to MySQL but to any ODBC connection. See Binding Parameters ODBC for reference.

Related Solutions

SQL Server – How to Pass Multi-Valued Characters in SSRS Report

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...

Sql-server – How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package

It's not possible to bind column names to variables, but you can achieve similar results by using dynamic SQL.

DECLARE @datetxt varchar(10) = '2015-01-01'
DECLARE @sqltxt nvarchar(1000); 

SET @sqltxt = N'SELECT dateAdd(second, 1, ' + @datetxt + ') AS StartTime FROM Termination_Call_Detail'
Exec (@sqltxt)

Best Answer

Related Solutions

SQL Server – How to Pass Multi-Valued Characters in SSRS Report

Sql-server – How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package

Related Question