SELECT TOP @variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

SELECT TOP @variable

Author

Topic

agenda9533
Starting Member

16 Posts

Posted - 2005-07-06 : 16:04:21

I want to do something like this: SELECT TOP @variable .
I've read all related topics BUT 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_number
WHERE GROUP BY Ticket.problem

Or

SELECT 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
HAVING (COUNT(Solutions.solution_date) > CAST(@num AS int)) doesn't work

Please, help![I]

X002548
Not Just a Number

15586 Posts

Posted - 2005-07-06 : 16:05:40

Happy Birthday

http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

agenda9533
Starting Member

16 Posts

Posted - 2005-07-06 : 16:25:50

quote:
Originally posted by X002548

Happy Birthday

http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Thank you for response!
ONLY QUESTION: How can I run EXECUTE within "SELECT" dataset query?????

DECLARE @N int, @sql nvarchar(4000)
SET @N = 3 --The number of records to return for each grouping.

SET @sql =
N' SELECT a.SiteId, a.EmpId, a.Sales '
+ ' FROM myTable99 a '
+ ' WHERE a.Sales IN (SELECT DISTINCT TOP ' + CONVERT(varchar(15),@n) + ' b.Sales '
+ ' FROM myTable99 b '
+ ' WHERE a.EmpID = b.EmpId '
+ ' ORDER BY b.sales DESC) '
+ 'ORDER BY a.EmpID, a.Sales DESC, a.SiteID '

EXECUTE sp_executesql @sql

jhermiz

3564 Posts

Posted - 2005-07-06 : 16:28:52

You cant you run execute inside of a stored procedure, OR you simply run the sql directly in the extractor of RS.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources