Hi

I was wondering if anybody could help me with this. I have a stored procedure that returns a recordset to an asp page. I pass values @paremeter.... to filter the stored procedure by a querystring. Now what i would like to do is have a dynamic order by on the stored procedure, where the user is able to click a hyperlink which passes a query string with an order by command that will filtere the recordset that is returned by the stored procedure. Can anybody advise?

Thanks

Marcus

Hi Mark

I've got something that could help you test it in MS SQL Northwind DB, give it a go and post back if you have any questions:

<pre id=code><font face=courier size=2 id=code>
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetProductsNorthwind]
/* Add the parameters for the stored procedure here.
I have used INT as the datatypes to hide your DB design
from your users as you are using the QS to pass values.*/
@Param1 INT = NULL,
@OrderBy INT = NULL,
@SortOrder INT = NULL
AS
BEGIN
-- @Param1 is set to category id to return multiple records
-- this could be your @parameter. If NULL return no data!
IF NOT @Param1 IS NULL
BEGIN

DECLARE @DynSQL NVARCHAR(1000)

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SET @DynSQL =
'SELECT
dbo.Products.ProductID,
dbo.Products.ProductName,
dbo.Products.SupplierID,
dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit,
dbo.Products.UnitPrice,
dbo.Products.UnitsInStock,
dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel,
dbo.Products.Discontinued
FROM
dbo.Products
WHERE
(dbo.Products.CategoryID = ' + CAST(@Param1 AS NVARCHAR(5)) + ') '

-- Evaluate if @OrderBy value is null
IF NOT @OrderBy IS NULL
BEGIN

-- Variables to hold column name and sort order
DECLARE
@Column NVARCHAR(20),
@Sort NVARCHAR(4)

-- Evaluate order by column
SET @Column =
CASE @OrderBy
WHEN 0 THEN 'ProductID'
WHEN 1 THEN 'ProductName'
WHEN 2 THEN 'SupplierID'
WHEN 3 THEN 'CategoryID'
WHEN 4 THEN 'QuantityPerUnit'
WHEN 5 THEN 'UnitPrice'
WHEN 6 THEN 'UnitsInStock'
WHEN 7 THEN 'UnitsOnOrder'
WHEN 8 THEN 'ReorderLevel'
WHEN 9 THEN 'Discontinued'
ELSE NULL
END

-- Evaluate sort order value
SET @Sort =
CASE @SortOrder
WHEN 0 THEN 'ASC'
WHEN 1 THEN 'DESC'
ELSE 'ASC'
END

SET @DynSQL = @DynSQL + N'ORDER BY dbo.Products.' + @Column + ' ' + @Sort

END

EXECUTE sp_executesql @DynSQL

END

ELSE
BEGIN
RETURN
END

END


</font id=code></pre id=code>

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee

Thanks for that.

I ended up using if statements to do the sort and that worked fine, however i sewem to be now having problems with a date filter that i have on the stored procedure,

I have setup a stored procedure that seems to work ok, however it does not appear to be filtering the dates correctly and i am getting back nulls when i enter some dates that i know should return values

SELECT DISTINCT
dbo.TblEmisDemographics.ExtractID, dbo.TblEmisDemographics.PracticeID, dbo.TblEmisDemographics.PatientID,
dbo.TblEmisDemographics.Surname, dbo.TblEmisDemographics.Forename, dbo.TblEmisDemographics.DOB, dbo.TblEmisDemographics.Sex,
dbo.TblEmisDemographics.HouseNumber, dbo.TblEmisDemographics.HouseName, dbo.TblEmisDemographics.No_Street,
dbo.TblEmisDemographics.Village, dbo.TblEmisDemographics.Town, dbo.TblEmisDemographics.Postcode, dbo.TblEmisDemographics.NHSNumber,
dbo.TblEmisDemographics.RegisteredGP, dbo.TblEmisDemographics.UsualGp, dbo.TblEmisDemographics.RegistrationStatus,
dbo.TblEmisDemographics.DateDeath, dbo.TblEmisDemographics.CombinedID,
dbo.TblEmisDemographics.Deleted
FROM dbo.TblEmisDemographics LEFT OUTER JOIN
dbo.TblEmisImms ON dbo.TblEmisDemographics.CombinedID = dbo.TblEmisImms.CombinedID

WHERE dbo.TblEmisDemographics.PracticeID = @PracticeID and dbo.TblEmisDemographics.extractid = @extractid and (dbo.TblEmisDemographics.Deleted = 0) AND (dbo.TblEmisImms.ID IS NOT NULL) AND (dbo.TblEmisImms.Deleted = 0) AND (dbo.TblEmisDemographics.Surname Like '%'+@Surname+'%' and (DOB &gt;= CONVERT(DATETIME, @Starting, 103) AND DOB &lt;= CONVERT(DATETIME, @Ending, 103)))




order by dbo.TblEmisDemographics.Surname desc


Any ideas where i may be going wrong?

Thanks Marcus

Hi Mark

Just a guess, is your join type throwing out the results?

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee

The Stored procedure works fine, until the point were it needs to filter by date and then it becomes a little messed up, im thinking if it is a problem with the convert date 103?

Thanks

Hi Mark

Yeah you're probably right. Substitute 102 for 103, 102 = ANSI standard, 103 = UK, only convert the output in the select statement to 103 rather than the where clause, should fix it no problem.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee

In the end i set the value

@starting and @ ending to variables instead of querystring and did the following

&lt;%' To and From Date manipluation

dim sTo
dim sToDay
Dim sToMonth
Dim sToYear

dim sFrom
dim sFromDay
Dim sFromMonth
Dim sFromYear

'To
sToDay = left(request.QueryString("TBTo",2)
sToYear = right(request.querystring("TBto",4)
sToMonth = right(left(request.querystring("TBTo",5),2)

if not sToDay = "" then
sTo = sToYear&"-"&sToMonth&"-"&sToDay
else
sTo =""
end if
'From
sFromDay = left(request.QueryString("TBFrom",2)
sFromYear = right(request.querystring("TBFrom",4)
sFromMonth = right(left(request.querystring("TBFrom",5),2)


if not sFromDay = "" then
sFrom = sFromYear&"-"&sFromMonth&"-"&sFromDay
else
sFrom =""
end if

'response.write(sFrom)




%&gt;

Thanks