Power Query data type conversion issue running SQL Server stored procedure

0
votes

Latest version of Excel M365 and SQL Server 2017.

I have a stored procedure which takes a date as its input in format YYYY-MM-DD - currently the variable type is set to nvarchar(20), but I've also tried to resolve this issue by making it type Date.

I have a stored procedure that when run in T-SQL, looks like this:

EXECUTE [Database1].[dbo].[SP1]
@Date = '2020-10-01'

I'm using Power Query to pull the results of this into Excel. Here's what the generated M query looks like in Power Query:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date = '2020-10-01'"])

This runs and imports the data as expected.

However now I'm trying to derive the @Date value from a cell in Excel. I have followed a good tutorial for making the date from the Excel cell available to Power Query and I believe I have that setup correctly. I have named the parameter SDate, and set it as type text since this is what the stored procedure is expecting.

So now the M query looks like this:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date = SDate"])

When I run this, I get this error in Power Query:

Microsoft SQL: Conversion failed when converting date and/or time from character string.

Two other things I tried:

  • create a literal string for SDate that's the date surrounded by single quotes i.e. '2020-10-01' - this gave the same error.

  • changed the stored procedure so the @Date variable is of type Date. Tested that by running the exact same T-SQL statement as above in SSMS, and the results return just the same as when it was nvarchar. So then in Power Query I ensured that the SDate parameter is of type date. Then I run the M query and this time I get the error "error converting type nvarchar to date."

One thing I found interesting is that after creating the aforementioned M query, if I go into settings of Source for the step that runs the query, the SQL Statement box shows this:

EXECUTE [Database1].[dbo].[SP1]
@Date = SDate

To me this looks like it's passing a literal SDate as the date to SQL Server, instead of the value of that parameter. I could be wrong there.

Any thoughts on getting this to run correctly?

1
sql-serverpowerquerym

1 Answers

1
votes

The literal string works if you drop it in right. You need to exit the literal text inside the quotes and append or insert the string you want.

Instead of this:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = SDate"]

Try this:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = " & Sdate]

Where Sdate has been defined as the literal string '2020-10-01'.

If Sdate is a date, then use this:

[Query="EXECUTE [Database1].[dbo].[SP1]
@Date = '" & Date.ToText(Sdate, "yyyy-MM-dd") & "'"]