Stored Procedure: Sort by Variable Column 2

[Mr3Putt]

Quick-n-Dirty: Here's what I WISH would work...

-------------------------------
Alter Procedure usp_getStuff
(
@sName varchar(50),
@iSortCol int = 1
)
As

SELECT sName, sStuff, iOtherStuff FROM tTable
WHERE (@sName IS NULL) OR (sName = @sName)
ORDER BY @iSortCol
-------------------------------

...but it DOESN'T work. I'm not allowed to "variably" define the ORDER BY column.

I get the following ADO error when trying to save:
[ol]
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying column positions. Variables are only allowed when ordering by an expression referencing a column name.
[/ol]
So far, I've not been able to identify a way to SORT by a Variable Column Reference...

 

[NoCoolHandle]

Yes, your problem is that you are trying to sort by a variable. The solution might be just to order by the output column...
What you probably need to do to work it out is have an if statement or select case run a specific select statement with an order by based on the case.

The other choice is to use the execute statement to build and execute dymanic sql. The problem with the execute statement is you WILL need to assign permissions on the base table.


Rob

 

[Genimuse]

I've always used select case and if statements when I've needed to do this, allowing for an input parameter that indicates the column as well as a parameter that indicates the order (ASC or DESC).

The downside is that I suspect that stored procedures with IFs and SELECT CASEs in them can't be automatically optimized very well. Maybe a stored procedure that calls other stored procedures based on the input parameters, each of which could be optimized by the database.

 

[Mr3Putt]

I attempted to use the CASE statement... with mixed results...

	ORDER BY 
	  CASE @iSortCol
	    WHEN 1 THEN sTextStuff
	    WHEN 2 THEN nDecimalStuff
	    WHEN 3 THEN iIntegerStuff
	    WHEN 4 THEN dDateStuff
	    ELSE tTable.sName
	  END

...resulted in errors "converting x to datatype datetime" or "converting x to int datatype". Very confusing. It worked with some fields, but not with others.

Confusing (to me, at least).

 

[Genimuse]

Sorry, what I meant was putting the full SELECT statement for each case with the column name hardcoded

CASE @iSortCol
  WHEN 1 THEN
    BEGIN
      SELECT sName, sStuff, iOtherStuff FROM tTable
      WHERE (@sName IS NULL) OR (sName = @sName)
      ORDER BY MyActualColumnName
    END
  WHEN 2 THEN
    BEGIN
      SELECT sName, sStuff, iOtherStuff FROM tTable
      WHERE (@sName IS NULL) OR (sName = @sName)
      ORDER BY MyOtherActualColumnName
    END
END

Etc.

 

[Mr3Putt]

oh. Dang.

 

[Mr3Putt]

Follow up -- Here's what worked:

Note that, I had to write two full iterations of the ENTIRE select statement based on if I wanted to sort ASCENDING or DESCENDING. The following is just the sample of the ORDER BY clause.

Also note that it requires a SEPARATE CASE STMT for each data-type... The FIRST column listed in the CASE STMT determines the TYPE for the ENTIRE CASE STMT. Odd, I thought.

 ORDER BY 
	    CASE @iSortCol	-- SORT NUMERIC COLUMNS
	      WHEN 1 THEN iPosID
	      WHEN 5 THEN tPosition.nCpn 
	      WHEN 7 THEN nPosTrdNet
	      WHEN 8 THEN nPosStlNet
	      WHEN 9 THEN SUM(
		CASE
		  WHEN nTransAmt > 0 THEN
			nTransAmt
		  ELSE 0
		END
		)
	      WHEN 10 THEN SUM(
		CASE
		  WHEN nTransAmt <= 0 THEN
			nTransAmt
		  ELSE 0
		END
		)
	    END ASC,

	    CASE @iSortCol 	-- SORT TEXT COLUMNS
	      WHEN 2 THEN tPosition.sTraderName 
	      WHEN 3 THEN tPosition.sSecID 
	      WHEN 4 THEN tPosition.sTicker 
	    END ASC,

	    CASE @iSortCol 	-- SORT DATE COLUMNS
	      WHEN 6 THEN dMaturity 
	      WHEN 16 THEN MAX(pend.dStlDt)
	      WHEN 17 THEN dLastTrdDt 
	    END ASC,

	2, 3
  END

... at the end, I'm sorting by columns 2 and 3, which works if the iSortCol variable is NULL. I had to call them by NUMBER, rather than NAME, because you can't sort by the same column reference twice.

So,
SELECT fld1, fld2, fld3 FROM table ORDER BY fld1, fld1
would fail...

But,
SELECT fld1, fld2, fld3 FROM table ORDER BY fld1, 1
works just fine...

 

[TheFrog]

that's simple solution

see...

CREATE PROCEDURE sorter_variable (@col varchar(255)) AS

declare @sql varchar(255)

select @sql = 'select * from cpu_insumo where projeto= 23 order by ' + @coluna

exec(@sql)
GO

ok?

 

[VitoMark]

Other solution could be, building a varchar string, containing the query to be executed.
With EXEC() you can run this query:
<i>
Declare @strSql varchar(1000)
Set @StrSql = 'SELECT sName, sStuff, iOtherStuff FROM tTable WHERE (' + @sName + 'IS NULL) OR (sName = ''' + @sName + ''') ORDER BY ' + @iSortCol
Exec(@strSql)
</i>

 

[Mr3Putt]

Frog and Mark... yeah, and that was suggested by NoCoolHandle on day one.

Not a good solution when your SQL String runs to the hundreds of lines. If I was running &quot;SELECT COALESCE(NickName,FirstName), LName FROM Associates WHERE UserID=10&quot;, I might consider building a SQL String...

My SQL Stmt is pretty complicated (for me, anyway), and I think the concatenation and embedded quotes would drive me (and any subsequent programmers) up a tree trying to build the SQL String.