I am attempting to import data from an SQL Server instance hosted on Azure into Excel (Excel 365 desktop app)
I can get the basic query to work by going to Data>Get Data>From Database>From SQL Server Database
Lets suppose the initial query (entered into the SQL statement box under the advanced options in the dialog box produced by the command above) is EXECUTE dbo.getClient 2. This works and returns data correctly
What I want to do is parameterize this query to EXECUTE dbo.getClient [Sheet1$b1] in a similar way to that suggested here Excel: Use a cell value as a parameter for a SQL query However attempting to do this gives the error "Microsoft SQL: Error converting data type nvarchar to int."
I then attempted to cast thus EXECUTE dbo.getClient SELECT CAST([Sheet1$b1:b1] AS int) but this gives the error "Microsoft SQL: Invalid column name 'Sheet1$b1:b1'."
So how do I parameterize Excel queries to an SQL server database? Answers which avoid macro scripts would be preferred if possible. I would like to be able to update the parameter cells in the workbook and then just go to Data>Refresh All.
