variables in stored procedures 1

[stevelionbird]

I am passing variables into a stored procedure and want to use one in and ORDER BY clause like so:
[COLOR=black yellow]
order by paramContactSortBy, c.ID, eg.GroupName;
[/color black yellow]
however this does not work.

Does anyone know why?
I know the value of the variable to be valid ([blue]c.FirstName[/blue] for instance), and no errors are thrown.

does MySQL only see it as a reference to the variable and if so is there another approach I could take.

Thanks,
Steve

 

[rac2]

Not terribly familiar with MySQL, but in MS SQL Server stored procedures variables cannot be used directly as if they were column names.

One approach is to build a string using the variable, then use EXECUTE( str_sql_statement ) to interpret and execute the string. Looking at the MySQL Reference Manual, it seems that the corresponding idea is that of a prepared statement. See

http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

.

Another approach is to use a CASE expression in the ORDER BY clause. The CASE expression uses different columns depending on the value of the variable. This might work in MySQL. See

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

ORDER BY
  CASE
    WHEN paramContactSortBy = 'c.FirstName' THEN c.FirstName
    WHEN paramContactSortBy = 'c.LastName' THEN c.LastName
    ELSE CAST( c.ID AS CHAR(20) )
  END CASE,
  eg.GroupName

Note that the datatype of each column must be the same or converted to matching types.

Sorry for the weak answer, but I see it has been a few days since you posted and no one else replied.

HTH

 

[stevelionbird]

thanks for the response rac2.

For now I will probably do a case similiar to your example above. The query is a pretty complex join so I wanted to try and avoid building it as strings then executing it but it looks like if I want to make this truly dynamic it's the best way to go. (possible to pass multiple params)

set paramContactSortBy = 'c.FirstName, c.LastName';

as I was doing all validity checks in CF code prior to calling the proc.

Thanks for your help