Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql

Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category,event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJCREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
--
David Portas
SQL Server MVP
--|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
--
David Portas
SQL Server MVP
--|||Hey guys,
How do I create views in MSSQL where the source is coming from an Oracle
table?
Thanks.
neil|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt = 'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
--
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net|||Using the Oracle OLEDB Provider, either create a linked server or use
OPENROWSET and specify the connection string.
--
David Portas
SQL Server MVP
--

Dynamic Data Source Expression

When i attempt to create a dynamic data source using an expression,
having tested the report with a constant data source string, the report
compilation fails with this error:
[rsUnexpectedCompilerError] An unexpected error occurred while
compiling expressions. Native compiler return value: '[BC32017]
Comma, ')', or a valid expression continuation expected.'
I am using Visual Studio 2005.
Simply changing the data source string from:
Data source = xxx;intial catalog=yyy
to:
=Data source = xxx;intial catalog=yyy
causes this error - i.e. I am not even referencing any parameters.
Has anyone encountered this before? Any resolutions?Amendment:
=Data source = xxx;intial catalog=yyy
should read:
="Data source = xxx;intial catalog=yyy"

Dynamic AND!

Hi all,
I am wanting to add an optional and statement to a query.
Not quite sure how to go about it.
My attempt so far:
SELECT
bsc_assmnt_area_id, name, parent_id
FROM
bsc_assmnt_areas
WHERE
parent_id = @.pid
CASE
WHEN parent_id = 0 Then And mine_id = @.mid
END
Would appreciate some insight on how to do this?
Cheers,
AdamSomething like this:
http://www.eggheadcafe.com/PrintSea...asp?LINKID=469
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Adam Knight" <dev@.brightidea.com.au> wrote in message
news:%23g8Px69kFHA.3436@.tk2msftngp13.phx.gbl...
> Hi all,
> I am wanting to add an optional and statement to a query.
> Not quite sure how to go about it.
> My attempt so far:
> SELECT
> bsc_assmnt_area_id, name, parent_id
> FROM
> bsc_assmnt_areas
> WHERE
> parent_id = @.pid
> CASE
> WHEN parent_id = 0 Then And mine_id = @.mid
> END
> Would appreciate some insight on how to do this?
> Cheers,
> Adam
>|||SELECT bsc_assmnt_area_id, name, parent_id
FROM bsc_assmnt_areas
WHERE parent_id = @.pid
AND mine_id = (CASE WHEN parent_id = 0 THEN @.mid ELSE mine_id END)
is one way of doing it.|||http://www.sommarskog.se/dyn-search.html
"Adam Knight" <dev@.brightidea.com.au> wrote in message
news:%23g8Px69kFHA.3436@.tk2msftngp13.phx.gbl...
> Hi all,
> I am wanting to add an optional and statement to a query.
> Not quite sure how to go about it.
> My attempt so far:
> SELECT
> bsc_assmnt_area_id, name, parent_id
> FROM
> bsc_assmnt_areas
> WHERE
> parent_id = @.pid
> CASE
> WHEN parent_id = 0 Then And mine_id = @.mid
> END
> Would appreciate some insight on how to do this?
> Cheers,
> Adam
>