Sorting on call to Stored Procedure SQL Server 1

[ksbigfoot]

I am calling a stored procedure from my ASP page.
I need to sort the records based on which fields the user selects on the ASP page.
Is there a way I can pass in a string of field names and have the Stored Procedure sort based on these values
OR maybe perform some sort when the recordset is returned?
Thanks

 

[DotNetGnat]

change your stored procedure to accept input parameters which would be the field names on which you need to sort...

change the sql inside stored procedure to include these input parameters and sort on them...

-DNG

 

[ksbigfoot]

Howdy DNG,

My Stored procedure already accepts two parameters.
I tried both of these and I received errors both ways.
svcOrderBy contains the Fields to sort on
Eg.) "Field3 ASC, Field4 DESC"

CREATE PROCEDURE [spTest]
  @svcCreatedBy int,
  @svcValue int,
  @svcOrderBy VarChar(256)
AS
SELECT Field1, Field2, Field3, Field4
FROM Table1
WHERE Field1 = @svcCreatedBy
AND Field2 = @svcCreatedBy
ORDER BY @svcOrderBy;

svcOrderBy contains the whole string to use for sort
Eg.) "Order By Field3 ASC, Field4 DESC"

CREATE PROCEDURE [spTest]
  @svcCreatedBy int,
  @svcValue int,
  @svcOrderBy VarChar(256)
AS
SELECT Field1, Field2, Field3, Field4
FROM Table1
WHERE Field1 = @svcCreatedBy
AND Field2 = @svcCreatedBy
@svcOrderBy;

 

[DotNetGnat]

Can you show how you are calling the stored procedure from your ASP page...

-DNG

 

[ksbigfoot]

Would I be able to pass in a 3rd parameter and do a case statement against it and include the Order by based on that.

Eg.) Not correct format as I haven't wrote case statement in SQL Server

Case WHEN @svcOrderBy='1' THEN Field1 ASC, Field3 Desc
WHEN @svcOrderBy='2' THEN Field1 ASC, Field2 Asc
WHEN @svcOrderBy='3' THEN Field2 ASC, Field1 Desc
END

 

[DotNetGnat]

i would just say do this:

CREATE PROCEDURE [spTest]
  @svcCreatedBy int,
  @svcValue int,
  @svcOrderBy VarChar(256)
AS
SELECT Field1, Field2, Field3, Field4
FROM Table1
WHERE Field1 = @svcCreatedBy
AND Field2 = @svcCreatedBy
ORDER BY @svcOrderBy;

calling SP from ASP page

sql = " Exec spTest 100,1,'Field1 Asc,Field3 Desc' "

if you want to use case statement then you need to do this:

Case WHEN @svcOrderBy='1' THEN 'Field1 ASC, Field3 Desc'
     WHEN @svcOrderBy='2' THEN 'Field1 ASC, Field2 Asc'
     WHEN @svcOrderBy='3' THEN 'Field2 ASC, Field1 Desc'
  END

-DNG

 

[DotNetGnat]

oh BTW if order by is optional to the end user than you do this...

CREATE PROCEDURE [spTest]
  @svcCreatedBy int,
  @svcValue int,
  @svcOrderBy VarChar(256)=[red]'Field1 ASC, Field3 DESC'[/red]
AS
SELECT Field1, Field2, Field3, Field4
FROM Table1
WHERE Field1 = @svcCreatedBy
AND Field2 = @svcCreatedBy
ORDER BY @svcOrderBy;

the line in red is the default value...

-DNG

 

[ksbigfoot]

Howdy DNG,
Thanks, I am not sure why, but when I try the line
ORDER BY @svcOrderBy;
and I check the syntax, I get the following error:
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

But the case statement works great. Star to you.

Thanks again,
ksbigfoot

 

[DotNetGnat]

oh yeah...you are right...sorry i missed that one...you cannot use a variable in the order by clause the way you are trying to do...
try this...

CREATE PROCEDURE [spTest]
  @svcCreatedBy int,
  @svcValue int,
  @svcOrderBy VarChar(256)='Field1 ASC, Field3 DESC'
AS

Declare @sql varchar(8000)

Set @sql = ' SELECT Field1, Field2, Field3, Field4 FROM Table1 WHERE Field1 = @svcCreatedBy AND Field2 = @svcCreatedBy ORDER BY ' + @svcOrderBy;

exec(@sql)

-DNG

 

[ksbigfoot]

Howdy DNG,

I did get the same error message, even when I did the case statement.
I tried your code you just posted and I didn't get an error message, but I changed the code a bit. I will have to test to see if it is right

CREATE PROCEDURE [spTest]
  @svcCreatedBy int,
  @svcValue int,
  @svcOrderBy VarChar(256)='Field1 ASC, Field3 DESC'
AS

Declare @sql varchar(8000)

Set @sql = ' SELECT Field1, Field2, Field3, Field4 FROM Table1 WHERE Field1 = ' + @svcCreatedBy + ' AND Field2 =' + @svcCreatedBy + ' ORDER BY ' + @svcOrderBy;

exec(@sql)

 

[DotNetGnat]

i think i need some coffee now...sorry for missing those single quotes...ya give it a shot and let me know if it works for you...

-DNG

 

[ksbigfoot]

I am getting something weird happening. My webpage times out and it doesn't look like it is getting the right results back. Is there a way to see what is returned?

Here is how I am called my stored procedure from my ASP page.

strOrderBy = "Field1 ASC, Field3 DESC"
Set rs = CreateObject("ADODB.Recordset")
  Set objCmd=Server.CreateObject("ADODB.Command")
  Set objCmd.ActiveConnection = conn

  objCmd.CommandType = 4
  objCmd.CommandText = "spTest"

  objCmd.Parameters.Append objCmd.CreateParameter("@svcCreatedBy", 3)
  objCmd("@svcCreatedBy")= intCreatedBy
  objCmd.Parameters.Append objCmd.CreateParameter("@svcValue", 3)
  objCmd("@svcValue")= intValue
  objCmd.Parameters.Append objCmd.CreateParameter("@svcOrderBy", 3)
  objCmd("@svcOrderBy")= strOrderBy   
  rs.CursorLocation = 3
  rs.Open objCmd, , adLockPessimistic, adCmdText

 

[DotNetGnat]

hardcode the values to see if the sp is returning the results correctly...if it does then you need to make sure that pass the variables on your page correctly..

-DNG

 

[Chopstik]

Also, if it is timing out, use the CommandTimeout method and set it to whatever you think you may need.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[ksbigfoot]

I hardcoded the values in the Stored Procedure.
I cut my code down to next to nothing.

Set rs = CreateObject("ADODB.Recordset")
  Set objCmd=Server.CreateObject("ADODB.Command")
  Set objCmd.ActiveConnection = conn

  objCmd.CommandType = 4
  objCmd.CommandText = "spTest"
  'rs.CursorLocation = 3
  'rs.Open objCmd, , adLockPessimistic, adCmdText
  Set rs = objCmd.Execute   
 
  Response.Write "count:"
  Response.Write rs.Fields.Count
  Response.Write "-<br>-"
  Response.Write rs.Fields(i)
  Response.Write ":"

I get 0 for the Count.
I have tried numerous things to get this to work.

I can get my case statement to work if I use only one field and I don't put Asc or Desc behind it.

Are if statements allowed in stored procedures?