从SQL中将参数传递到MS SQL中的临时变量 [英] Pass parameters to temp variables in MS Query on SQL Server from Excel

问题描述

我已经使用Microsoft查询创建了一个参数查询，如此处。但是当我想将参数传递给临时变量并创建表变量并编辑它们以获得所需的结果，而不是进行10到15个连接，并提到where子句中的参数，我得到错误

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驱动程序]无效参数号

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

和

and

[Microsoft] [ODBC SQL Server驱动程序]无效的描述符索引

[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 

如何在Excel 2007中为SQL Server 2005中的数据库传递参数到temp变量？我没有权限在数据库中创建存储过程，并将它们作为参数传递给它。

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.

更新

我已经通过VBA href =https://stackoverflow.com/questions/15848015/pass-parameters-to-temp-variables-in-ms-query-on-sql-server-from-excel#comment22552445_15848015> David Vandenbos 。如果没有VBA的帮助，我仍然很想知道这是否可以完成。

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.

推荐答案

您可以在声明前添加SET NOCOUNT变量。我在Excel 2010中与Microsoft Query和SQL Server 2005完美一致。

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

< a href =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 = exceldevrel =nofollow> https://social.msdn.microsoft.com/Forums/office/en-US/d8003854-d11a-44f7-960c-a042347736d7/microsoft- query-can not-run-sql-code-with-a-tsql-variables-in-it？forum = exceldev

这篇关于从SQL中将参数传递到MS SQL中的临时变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！