Select Top @variable
I want to do something like this: SELECT TOP @variable. I cannot use stored procedure for this report, 'cause I do not have access to prod db, I'm just sending rdl for upload. How to make it work on dataset level or through the report layout?I know SQLServer doesn't recognize "Select Top @variable" statement. SELECT top &variable, Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]FROM Ticket INNER JOIN Solutions ON Ticket.ticket_number = Solutions.ticket_numberWHERE GROUP BY Ticket.problemOrSELECT Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]FROM Ticket INNER JOIN Solutions ON Ticket.ticket_number = Solutions.ticket_numberWHERE GROUP BY Ticket.problemHAVING (COUNT(Solutions.solution_date) > CAST(@num AS int)) doesn't work Please, help!
July 6th, 2005 11:00pm
You can use an expression based query, i.e.="SELECT TOP (" & Parameters!Variable.Value & ") Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]FROM Ticket INNER JOIN Solutions ON Ticket.ticket_number = Solutions.ticket_number WHERE GROUP BY Ticket.problem"
July 10th, 2005 12:01pm
you can use also "set rowcount " command (sql)ex : --setup the max number of rowsSet rowcount @topSelect ..... from XXX-- reinitializeset rowcount 0
December 14th, 2005 1:04pm
Is there a way to do this from within Visual Studio 2005? For example, if I open the Data tab for a report that currently returns 7000+ rows, I can enter "TOP 100" directly into the SELECT statement and all works as expected. But when I try either of the methods mentioned above, I get syntax errors.
January 3rd, 2006 8:21pm
Hiyou can do without any Dynamic Query---------declare @NoOfRecordsSet @NoOfRecords = 10set Rowcount @NoOfRecordsSELECT * FROM TABLENAMESET Rowcount 0
August 30th, 2006 11:02am
If you are using SQL Server 2005 the following should do the needfuldeclare @limit intSet @limit=5select top(@limit) * from TableName
May 29th, 2007 11:39am
Unfortunately I'm stuck with SQL 2K.
September 22nd, 2008 4:44pm
Rather than using ROWCOUNT you can use the following statement:CREATE PROCEDURE TEST@TestInt INTASBEGIN DECLARE @SqlQuery NVARCHAR(4000); SET @SqlQuery = 'SELECT TOP '+CAST(@TestInt as CHAR)+'* FROM TABLE_TEST'; EXEC(@SqlQuery)END
December 30th, 2008 9:28am
I was trying something like this, but I was just forgetting the parenthesis XD Thank you!
December 18th, 2009 8:06pm
it was helpful
October 12th, 2010 8:51pm