• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1234
  • Last Modified:

MS SQL Stored Procedure order by inner select results... yay

My issue is with a Stored Procedure within MS SQL

I am using a case statement to allow me to use a variable for the 'order by' clause, which are mostly referencing result variables from inner queries, which is returning errors stating that those values are 'invalid column names'

Help?

CREATE PROCEDURE dbo.endeavor_sp_memberRecentActivity
(
 @workingWithID int,
 @maxDate      datetime,
 @returnTop int,
 @sortString nvarchar(50)
)
             
AS  
BEGIN
      SET NOCOUNT ON

       IF @returnTop < 1
              SET ROWCOUNT 0
       ELSE
              SET ROWCOUNT @returnTop

       SELECT id, username,firstName,lastName,type, contactDate,
      (select count(id) FROM hurrikane.endeavor_search WHERE hurrikane.endeavor_search.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,searchDate)>=0) as searchcount,
      (select count(id) FROM hurrikane.endeavor_mlsSaved WHERE hurrikane.endeavor_mlsSaved.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as savedcount,
      (select count(id) FROM hurrikane.endeavor_mlsPrint WHERE hurrikane.endeavor_mlsPrint.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as printcount,
      (select count(id) FROM hurrikane.endeavor_mlsView WHERE hurrikane.endeavor_mlsView.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as viewcount
       FROM hurrikane.endeavor_memberInfo WHERE workingWith = @workingWithID
       ORDER BY
        CASE @sortString
      WHEN 'All' THEN (searchcount+savedcount+printcount+viewcount)
      WHEN 'Name' THEN lastName
      WHEN 'Saved' THEN savedcount
      WHEN 'Printed' THEN printcount
      WHEN 'Viewed' THEN viewcount
      WHEN 'SavedSearch' THEN searchcount
      WHEN 'LastContact' THEN contactDate
        END

END
GO
0
trickyidiot
Asked:
trickyidiot
  • 2
  • 2
1 Solution
 
derekkrommCommented:
Try this:

CREATE PROCEDURE dbo.endeavor_sp_memberRecentActivity
(
 @workingWithID int,
 @maxDate      datetime,
 @returnTop int,
 @sortString nvarchar(50)
)
             
AS  
BEGIN
      SET NOCOUNT ON

       IF @returnTop < 1
              SET ROWCOUNT 0
       ELSE
              SET ROWCOUNT @returnTop

       SELECT id, username,firstName,lastName,type, contactDate,
      (select count(id) FROM hurrikane.endeavor_search WHERE hurrikane.endeavor_search.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,searchDate)>=0) as searchcount,
      (select count(id) FROM hurrikane.endeavor_mlsSaved WHERE hurrikane.endeavor_mlsSaved.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as savedcount,
      (select count(id) FROM hurrikane.endeavor_mlsPrint WHERE hurrikane.endeavor_mlsPrint.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as printcount,
      (select count(id) FROM hurrikane.endeavor_mlsView WHERE hurrikane.endeavor_mlsView.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as viewcount
into #tmpTable
       FROM hurrikane.endeavor_memberInfo WHERE workingWith = @workingWithID

select * from #tmptable
       ORDER BY
        CASE @sortString
      WHEN 'All' THEN (searchcount+savedcount+printcount+viewcount)
      WHEN 'Name' THEN lastName
      WHEN 'Saved' THEN savedcount
      WHEN 'Printed' THEN printcount
      WHEN 'Viewed' THEN viewcount
      WHEN 'SavedSearch' THEN searchcount
      WHEN 'LastContact' THEN contactDate
        END

END
GO
0
 
adatheladCommented:
I'd recommend avoiding temp tables unless absolutely necessary. Try:

SELECT * FROM
(
SELECT id, username,firstName,lastName,type, contactDate,
      (select count(id) FROM hurrikane.endeavor_search WHERE hurrikane.endeavor_search.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,searchDate)>=0) as searchcount,
      (select count(id) FROM hurrikane.endeavor_mlsSaved WHERE hurrikane.endeavor_mlsSaved.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as savedcount,
      (select count(id) FROM hurrikane.endeavor_mlsPrint WHERE hurrikane.endeavor_mlsPrint.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as printcount,
      (select count(id) FROM hurrikane.endeavor_mlsView WHERE hurrikane.endeavor_mlsView.username=hurrikane.endeavor_memberInfo.username AND datediff(d,@maxDate,mlsDate)>=0) as viewcount
       FROM hurrikane.endeavor_memberInfo WHERE workingWith = @workingWithID
) t
ORDER BY
   CASE @sortString
      WHEN 'All' THEN (searchcount+savedcount+printcount+viewcount)
      WHEN 'Name' THEN lastName
      WHEN 'Saved' THEN savedcount
      WHEN 'Printed' THEN printcount
      WHEN 'Viewed' THEN viewcount
      WHEN 'SavedSearch' THEN searchcount
      WHEN 'LastContact' THEN contactDate
        END
0
 
derekkrommCommented:
tricky, any update?
0
 
adatheladCommented:
I'd still recommend you don't use temp tables unless you have to, which I don't think you do. You'll incur an unnecessary performance/resource hit.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now