3

I have created a parameter query using Microsoft query as mentioned here. But when I want to pass parameters to temporary variables and create table variables and edit them to get the desired result instead of doing 10 to 15 Joins and mentioning the parameters in the where clause I get errors

[Microsoft] [ODBC SQL Server Driver] Invalid Parameter number

and

[Microsoft] [ODBC SQL Server Driver] Invalid Descriptor Index

My code looks something like this it is way complex with many temp tables and temp variables

BEGIN
    SET NOCOUNT ON

    DECLARE @sDate DATETIME, @eDate DATETIME; --used in many places to manipulate temp table

    SET @sdate = ?
    SET @edate = ?

    DECLARE @Temptable TABLE (Variable1 INT ,...... VariableN DECIMAL(18,4));

    Manipulate @temptable

    Select * from @Temptable 
END

How is it possible to pass parameters to temp variables in Excel 2007 for a database in SQL Server 2005? I have no permission to create stored procedures in the database and pass them as parameters to it.

UPDATE

I have figured a way through VBA as suggested by David Vandenbos. I am still curious to know if this can be done without the help of VBA.

Community
  • 1
  • 1
Ram
  • 3,092
  • 10
  • 40
  • 56
  • 2
    +1, Great question. Unfortunately, I don't think this can be done in Excel through MS Query. Query is limited to passing parameters in queries that can be "displayed graphically", although you can pass everything else. Do you have permission to create views on the database? You may be able to create a view(s) and then return them to Excel with parameters. Otherwise, I think you'll probably need VBA. – dav Apr 05 '13 at 12:43
  • @DavidVandenbos I found a way through VBA. Thanks – Ram Apr 09 '13 at 21:11

1 Answers1

0

You can add SET NOCOUNT ON before declaring the variables. I works perfectly in Excel 2010 with Microsoft Query and SQL Server 2005.

SET NOCOUNT ON
DECLARE @VAR1 VARCHAR(4)
SET @VAR1 = '1234'
SELECT @VAR1

https://social.msdn.microsoft.com/Forums/office/en-US/d8003854-d11a-44f7-960c-a042347736d7/microsoft-query-cannot-run-sql-code-with-a-tsql-variables-in-it?forum=exceldev

kinch
  • 1
  • 1
  • I did add `SET NOCOUNT ON` as you can see in the code in the question above. It doesn't work in Excel 2007. – Ram Apr 15 '15 at 18:34