Dynamic Parameters S.P.

Using a Stored procedure, i want to sort my records according to a dynamic
field... Could be sort by firstName, CompanyName, UserID... and so on...
Receiving value in @.orderingBy, but i can't seem to find a way to properly
integrate it into my query, juse into the 'order by'...
If any could help...
CREATE PROCEDURE [dbo].[SelectContactListTEST]
@.orderingBy nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid
FROM UserObject UO INNER JOIN
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN (0, 1)) AND
(UO.Hidden = 0)
ORDER BY "+ @.orderingBy + "How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"lp_rochon" wrote:

> Using a Stored procedure, i want to sort my records according to a dynamic
> field... Could be sort by firstName, CompanyName, UserID... and so on...
> Receiving value in @.orderingBy, but i can't seem to find a way to properly
> integrate it into my query, juse into the 'order by'...
> If any could help...
>
> CREATE PROCEDURE [dbo].[SelectContactListTEST]
> @.orderingBy nvarchar (255)
> AS
> SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid
> FROM UserObject UO INNER JOIN
> WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN (0, 1)) A
ND
> (UO.Hidden = 0)
> ORDER BY "+ @.orderingBy + "|||Thank alot, it does work..
But now, to next question:
I the same aspect, i got my 'where' statement that i need to be dynamic...
As:
WHERE (UO.customer_type = 'wholesaler')
could become:
WHERE (UO.customer_type = 'wholesaler') AND (UO.A = 'XX') AND (UO.B =
'ZZ')
to do that, i want to pass 1 variable (string) as:
WHERE (UO.customer_type = 'wholesaler') @.whereString
know how to do this?
"Alejandro Mesa" wrote:

> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
>|||And now to a new problem...
Calling my procedure, passing this
EXEC SelectContactListTEST '0','1','0','lastname','ASC'
I'm getting the following error:
erver: Msg 245, Level 16, State 1, Procedure SelectContactListTEST, Line 9
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'TEST' to a column of data type bit.
I really don't see were this 'TEST' column comes from...
See below for code
----
--
CREATE PROCEDURE [dbo].[SelectContactListTEST]
@.activeSearch bit,
@.activeSearch2 bit,
@.hiddenSearch bit,
@.orderingBy nvarchar (255),
@.orderingWay nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid, UO.company AS
company, UO.ContOwner AS contowner, UO.u_first_name AS firstname,
UO.u_last_name AS lastname, UO.u_email_address AS
email, UO.Hidden AS hidden, UO.Activated AS activated, UO.d_date_created AS
datecreated,
A.u_city AS city, A.u_country_code AS country,
A.u_region_code AS region
FROM UserObject UO INNER JOIN
Addresses A ON UO.g_user_id = A.g_id
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN
(@.activeSearch,@.activeSearch2)) AND (UO.Hidden = @.hiddenSearch)
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Activated
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Activated
end
END
GO
---
"Alejandro Mesa" wrote:

> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
>|||On Tue, 8 Feb 2005 09:03:01 -0800, lp_rochon wrote:

>And now to a new problem...
>
>Calling my procedure, passing this
>EXEC SelectContactListTEST '0','1','0','lastname','ASC'
>I'm getting the following error:
>erver: Msg 245, Level 16, State 1, Procedure SelectContactListTEST, Line 9
>[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
>nvarchar value 'TEST' to a column of data type bit.
>I really don't see were this 'TEST' column comes from...
Hi lp_rochon,
I guess it's in your data, as one of the last names.
Look at this part:

> case @.orderingWay
> when 'ASC' then
> CASE @.orderingBy
> WHEN 'company' then company
> WHEN 'lastname' then u_last_name
> WHEN 'country' then u_country_code
> WHEN 'region' then u_region_code
> WHEN 'city' then u_city
> WHEN 'email' then u_email_address
> WHEN 'activated' then Activated
> end
> END
I assume that all columns here are char, varchar nchar or nvarchar, except
for the Activated column, which I guess to be bit. The datatype of the
CASE is determined by looking at all datatypes used in the WHEN
expressions; if they are different (and no explicit conversion is used),
then datatype precedence decides which is converted to what. In this case,
SQL Server will attempt to convert all varchar columnsto bit, since bit
has a higher precedence than varchar.
Try changing your ORDER BY to either
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then CAST(Activated as char(1))
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then CAST(Activated as char(1))
end
END
or
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
end
END
DESC,
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'activated' then Activated
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
end
END,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'activated' then Activated
end
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)