Making a SPROC query work like a normal one ?? 1

[link9]

Hello all --

I have the following query in the query analyzer that works perfectly:

select top 1 r.callCenter, count(*) as countTop
from cgResponse2001_3 c, regionCallCenter r
where r.callCenterCode=c.callCenter and q5 between 4 and 5
group by r.callCenter
order by c.countTop DESC

I am trying to make it work in a SPROC by dynamically creating and executing a sql statement, but I'm having real problems getting the ORDER BY clause to function. Here's what I have so far:

SET @sql1='(select top 1 @best = regionCallCenter.callCenter, @thisCount = count(*) as countTop from ' + @responseTable +' , regionCallCenter where (regionCallCenter.callCenterCode='+@responseTable +'.callCenter and '+@responseTable+'.' + @variable + ' BETWEEN ' + @topLBound + ' AND ' + @topUBound + ')' + @regionWhere + @callCenterWhere + @customerExperienceWhere + @usageLevelWhere + @customerSegmentWhere + 'group by regionCallCenter.callCenter order by ' + @responseTable + '.countTop DESC )'

Please pay attention to the bolded portion, as this is the culprit. I just can't seem to get the thing to work.

I know that AS is usually not supported inside a SPROC, but if I don't have it, then what do I order by?

Error msg:
Incorrect syntax near the keyword 'as'.

If anyone could shed some light on how to get this to function, I really would appreciate it.

Thanks!
Paul Prewett

 

[fluteplr]

You can't have an 'as' when you are assigning a value to a variable, since no field is created what would the 'as' do? Pick one or the other.

 

[tlbroadbent]

You can use AS in an SPROC. That is not the issue. The problem you have is that you've created a query that assigns values to variables rather than returning a result set. A column name is invalid in this context.

I suggest creating a query that returns the result set you want. Then use that query as a sub query in a query that assigns the values to the variables. NOTE: You can also use the ordinal position of the column in the Order By clause instead of the column name.

Suggested revision: Note that I used table aliases also.

SET @sql1=
'Select @best=CallCenter, @ThisCount=CountTop From
(Select Top 1 c.callCenter, count(*) As countTop
From ' + @responseTable +' as r, regionCallCenter As c
Where (c.callCenterCode=r.callCenter
And r.' + @variable + ' BETWEEN ' + @topLBound + '
And ' + @topUBound + ')' +
@regionWhere + @callCenterWhere +
@customerExperienceWhere + @usageLevelWhere +
@customerSegmentWhere +
'Group By c.callCenter Order By 2 DESC ) As qTop'
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[link9]

Excellent. I see your point. One of these days, I'm going to get this whole SQL Server thing down. ;-) SPROCS always seem to give me a real headache. The logic sometimes just doesn't make sense to me.

Thanks again. Your help and advice is always very much appreciated.

paul