"ORDER by ?" in parameter query with dynamic SQL

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

Other Development Tools

"ORDER by ?" in parameter query with dynamic SQL

Author

Topic

goldnhue
Starting Member

4 Posts

Posted - 2012-04-16 : 01:39:42

I am building Parameterized dynamic SQL queries (not stored procedures) in a classic ASP application (using SQL Server 2005).
When I try to pass a parameter to the " ORDER BY " clause, I get error "Application uses a value of the wrong type for the current operation". Below is example. What am I doing wrong? Thank you in advance.

' define variable value
fld1="ObjectNum"

' Define query
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess WHERE ObjectNum >= 0 ORDER BY ?;"

' -- Setup Parameter Query and execute
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = application ("DBConStr")
objCmd.CommandType = adCmdText
objCmd.CommandText=strSQL
objCmd.Parameters(0) = CheckStringParam(fld1)
objCmd.execute(strSQL)
Set objCmd=Nothing

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-16 : 07:13:49

Don't you need to create the parameter object before you can assign a value to it? The examples on this page seem to suggest so: http://support.microsoft.com/kb/164485

goldnhue
Starting Member

4 Posts

Posted - 2012-04-16 : 10:15:53

While not first defining the parameter is less desireable technique, it does work when the query assigns the parameter to a specific column like this:
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess WHERE ObjName = ?;"

But it fails when the query does not have a specific column to assign the parameter to, like this.
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess WHERE ObjectNum >= 0 ORDER BY ?;"

So, if I predefine it, what would be the type and size for this parameter?
objCmd.Parameters.Append = objCmd.CreateParameter(fld1, type??, adParamInput, size?? , fld1)

I tried using the data type of the column being passed as below, but it still failed:

fld1="ObjectNum" ' this column is an integer type
strSQL="SELECT ObjectNum, ObjName FROM qryVisMultiProcess ORDER BY ?;"

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = application ("DBConStr")
objCmd.CommandType = adCmdText
objCmd.CommandText=strSQL
objCmd.Parameters.Append = objCmd.CreateParameter("ObjectNum", adInteger, adParamInput, , fld1)
objCmd.execute(strSQL)

Would the data type correspond to the what SQL uses in the schema for storing all column names, not the definition of the specific column per-se? If yes, how would I get that info?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 14:46:21

why do you need the data to be sorted from db itself? You're retrieving the entire dataset then why not do the sorting at your presentation layer? which is front end you're using?

alternatively you can make above query into procedure and add a parameter to that for sorting. then use parameter and add a case..when to vary sorting based on that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goldnhue
Starting Member

4 Posts

Posted - 2012-04-17 : 14:44:49

This application is in Classic ASP. I'm only familiar with bubble sort or quicksort scripts for Classic, which I would not think would be efficient way to go if I can order in the query.

As you suggest, it is probably best to simply create a stored procedures instead.

Thanks much.

(But I sure would love to know how to pass that "Order By" parameter in original scenario, if anyone knows!)

goldnhue
Starting Member

4 Posts

Posted - 2012-04-17 : 22:21:10

I found the solution, just needed to construct like normal stored procedure using "CASE", with separate CASE for different data types, and define the parameter as varchar type as below.

fld1="R"
strSQL= "SELECT ObjectNum, ObjName FROM qryVisMultiProcess ORDER BY " &_
"CASE WHEN ?='N' THEN ObjName END, " &_
"CASE WHEN ?='R' THEN ObjectNum END"

' -- Setup Parameter Query
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = application ("DBConStr")
objCmd.CommandType = adCmdText
objCmd.CommandText=strSQL
objCmd.Parameters.Append = objCmd.CreateParameter("ObjName", adVarChar, adParamInput, 100, fld1)
objCmd.Parameters.Append = objCmd.CreateParameter("ObjectNum", adVarChar, adParamInput, 100, fld1)
SET objRS = objCmd.execute(strSQL)
Set objCmd=Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources