2

This is really only a short question, but I can't find the answer anywhere.

Is it possible to use an Excel parameter in a t/sql query as a SELECT TOP xxxx, where the parameter here defines how many rows are fetched, without using a stored procedure?

JMax
  • 26,109
  • 12
  • 69
  • 88
Gunnar Sjúrðarson Knudsen
  • 441
  • 4
  • 17

3 Answers3

4

From sql 2005 and up you can write your query as:

Enter the following in your msquery:

   {CALL sp_executesql (N'select top (@a) * from mytable', N'@a int', ?)}
Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
  • God. For years I've suffered from "Parameters are not allowed in queries that cannot be represented graphically." I've invented a whole bloody system to workaround this limitation. – GSerg Nov 10 '11 at 14:51
  • Could you share a link to documentation on this syntax? Excel appears to pad these string parameters with spaces, always up to 8000 characters, even if the actual parameter in the database is `nvarchar(4000)`, which causes overflows. I wonder if there's any syntax tweak to affect it. – GSerg Dec 18 '11 at 18:14
  • This `sp_executesql` trick works properly on some computers and just will not work on some other computers, where Excel would give an error on refreshing the table, "[SQL Native Client]Invalid parameter number". The error comes from Excel, when I open the query in MSQuery and refresh it there, it works fine. The computers have rather different configurations and I can't spot exactly what makes this work or fail (must be a system setting somewhere or a version of something, because the Excel book is same every time). Can you shed some light on that? – GSerg Feb 13 '13 at 09:42
  • You have more chance for success if you use the "SQL Server" driver. not the "SQL Server Native Client xx.x" driver. – Filip De Vos Feb 13 '13 at 10:36
  • Nope, does not help. Same error (on computers that give the error). Similarly, works with both drivers on computers that don't give the error. Which sort of makes sense because even on the offending computers MSQuery always executes the query properly with any driver, it's Excel being the idiot after receiving the statement definition from MSQuery. And it also doesn't seem to depend on Excel version (we have from 2003 to 2013), while it *seems* to have something to do with Windows 7 versus Windows XP. We're still investigating but no luck so far. – GSerg Feb 13 '13 at 11:13
  • I finally posted a [question](http://stackoverflow.com/q/18741965/11683) about this. – GSerg Sep 11 '13 at 13:03
0

I'm not sure about applying the Excel parameter, but to do this in T-SQL do:

select *
from (
    select row_number() over (order by [Field]) as rowNum, *
    from [myTable] ) s
where s.rowNum < @maxRows
Keith
  • 150,284
  • 78
  • 298
  • 434
0

If you are in Excel can you not use Concat to build up the sql query?

Concatenate("select top ", $D2, " from mytable where...")

and use the value of that cell as your tsql expression?

http://msdn.microsoft.com/en-us/library/aa188518(v=office.10).aspx

jenson-button-event
  • 18,101
  • 11
  • 89
  • 155