SQL变量在Excel查询中不起作用 [英] SQL Variables not working in Excel Query

问题描述

我有一个查询无法在Excel外部SQL Server查询中以图形方式表示，因为查询不简单，所以我无法设置Excel变量.相反，我尝试使用声明/设置在SQL中设置变量，但是这样做时，在尝试导入数据时会失败.

Hi I have a query that can not be represented graphically in Excel External SQL Server Query, because the query is not simple I can not set Excel Variables. Instead I try to set variables in the SQL using declare/set however when doing this it fails when trying to import the data.

有没有办法解决这个问题?我需要一定程度的灵活性来运行查询并将数据传递到Excel.

Is there a way to get around this? I need a level of flexibility for running the query and piping the data into Excel..

好的，这是所要求的示例.

Okay so here is an example as requested.

declare @enddate as datetime
set @enddate = (select max(rpt_week) from [results].dbo.t_Data)
Select * from results.dbo.t_Data
where rpt_week = @enddate

这将无法在excel中运行，显然我的查询更为复杂，但是变量的使用至关重要，我正在寻找一种方法来进行这项工作.

This will not run in excel, obviously my query is more complex but the use of variables is essential and I am looking for a way to have this work.

谢谢

推荐答案

在Excel中使用声明值时，需要设置NOCOUNT ON

When using declare values in Excel, you need to SET NOCOUNT ON

您的示例如下:

SET NOCOUNT ON;
declare @enddate as datetime
set @enddate = (select max(rpt_week) from [results].dbo.t_Data)
Select * from results.dbo.t_Data
where rpt_week = @enddate

这篇关于SQL变量在Excel查询中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！