Adding Time to Rank

Using FoodMart, how can i add time to this:
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM SalesWhat platform is this?|||Brett,

This is an MDX statement for OLAP - Hard as nails to understand ..

msenoelo - are you wanting to set this up as a calculated measure or do you just want to see if the MDX is valid

If you want check the MDX then you can open up the sample mdx application from

Programs > SQL Server > Anaysis Services > Sample MDX Application

I think??

You then get a kind IDE that can use to parse MDX.

Or you can goto your cube

New calculated meause > Insert your code

Not totally sure as I don't have anaylsis services installed on my computer and MDX is total bloody mystery to me. Where did you get the MDX from ?

Here is a like to a more relevant forum:

http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=18

Cheerssql

Adding row numbers to results

If you have something like
select au_fname from authors order by au_fname
You get:
au_fname
--
Abraham
Akiko
Albert
Ann
Anne
Burt
Charlene
Cheryl
Dean
Dirk
Heather
Innes
Johnson
Livia
Marjorie
Meander
Michael
Michel
Morningstar
Reginald
Sheryl
Stearns
Sylvia
(23 row(s) affected)
is there a way to make the result put row numbers in front of the results?
Such as:
au_fname
--
1. Abraham
2. Akiko
3. Albert
4. Ann
...
Thanks,
TomYou can create a temorpary table variable
Declare @.Out Table
(RowID Integer Primary Key Identity Not Null,
fName VarChar(50))
Insert the data from your query into this table variable...
Insert @.Out(fName)
Select au_fname
from authors
order by au_fname
And then output the data from the table variable...
Select * From @.Out
Altogether Now...
Declare @.Out Table
(RowID Integer Primary Key
Identity Not Null,
fName VarChar(50))
-- --
Insert @.Out(fName)
Select au_fname
from authors
order by au_fname
-- --
Select * From @.Out
"tshad" wrote:

> If you have something like
> select au_fname from authors order by au_fname
> You get:
> au_fname
> --
> Abraham
> Akiko
> Albert
> Ann
> Anne
> Burt
> Charlene
> Cheryl
> Dean
> Dirk
> Heather
> Innes
> Johnson
> Livia
> Marjorie
> Meander
> Michael
> Michel
> Morningstar
> Reginald
> Sheryl
> Stearns
> Sylvia
> (23 row(s) affected)
> is there a way to make the result put row numbers in front of the results?
> Such as:
> au_fname
> --
> 1. Abraham
> 2. Akiko
> 3. Albert
> 4. Ann
> ...
> Thanks,
> Tom
>
>|||http://www.aspfaq.com/2427
http://www.aspfaq.com/
(Reverse address to reply.)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uaE6X#cJFHA.4056@.TK2MSFTNGP14.phx.gbl...
> If you have something like
> select au_fname from authors order by au_fname
> You get:
> au_fname
> --
> Abraham
> Akiko
> Albert
> Ann
> Anne
> Burt
> Charlene
> Cheryl
> Dean
> Dirk
> Heather
> Innes
> Johnson
> Livia
> Marjorie
> Meander
> Michael
> Michel
> Morningstar
> Reginald
> Sheryl
> Stearns
> Sylvia
> (23 row(s) affected)
> is there a way to make the result put row numbers in front of the results?
> Such as:
> au_fname
> --
> 1. Abraham
> 2. Akiko
> 3. Albert
> 4. Ann
> ...
> Thanks,
> Tom
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:enIr27dJFHA.3340@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2427
I actually did do the asp.net route, which was fairly easy, but it is good
to know the other ways as I am sure there are cases where it would be better
to get it from the database.
Thanks,
Tom
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uaE6X#cJFHA.4056@.TK2MSFTNGP14.phx.gbl...
>|||> I actually did do the asp.net route, which was fairly easy, but it is good
> to know the other ways as I am sure there are cases where it would be
better
> to get it from the database.
Like what? This number is just for presentation.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ehncValJFHA.656@.TK2MSFTNGP14.phx.gbl...
> better
> Like what? This number is just for presentation.
If I were filling a Grid and not writing code when my page loads to go
through the grid, it might be easier to just let Sql give it to me, for
example.
Tom

Adding Parameters to the ORDER BY Clause

What I'm wanting to do is create a report which allows you to sort by a
few different columns, and also choose if you want it sorted ASC or
DESC.
Basically, I'll have two parameters...
1) OrderBy (a number of different columns)
2) OrderDirection (ASC or DESC)
and I'll want to do a
SELECT -
FROM -
WHERE -
ORDER BY @.OrderBy @.OrderDirection
Obviously it doesn't work like this, or I wouldn't be asking for help!
I know you can use things like "Parameters!OrderBy.Value" but I was
unable to get this to work. Would somebody mind helping out?
Thanks!
-ScottI have always done this from within the RDL file during creation. It is in
one of the property dialog boxes, you may have to choose and advanced button
somewhere...It will be within the group header properties I think...
--
--Eric Cathell, MCSA
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||SELECT dbo.DatabaseName.*
FROM dbo.DatabaseName
WHERE (FieldName LIKE @.FieldName)
ORDER BY FieldName
Note: @.FieldName is "assigned" from the Report...Report Parameters menu bar
--
Message posted via http://www.sqlmonster.com|||I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||When using filters on a table/matrix group there's a ASC or DESC. But, can
you make that an expression? When I select it, there's no option to make it
an expression.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eeNwO3pEFHA.1188@.tk2msftngp13.phx.gbl...
> I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
> hope this helps
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <ScottWMcCarter@.gmail.com> wrote in message
> news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> > What I'm wanting to do is create a report which allows you to sort by a
> > few different columns, and also choose if you want it sorted ASC or
> > DESC.
> >
> > Basically, I'll have two parameters...
> >
> > 1) OrderBy (a number of different columns)
> > 2) OrderDirection (ASC or DESC)
> >
> > and I'll want to do a
> > SELECT -
> > FROM -
> > WHERE -
> > ORDER BY @.OrderBy @.OrderDirection
> >
> > Obviously it doesn't work like this, or I wouldn't be asking for help!
> > I know you can use things like "Parameters!OrderBy.Value" but I was
> > unable to get this to work. Would somebody mind helping out?
> >
> > Thanks!
> >
> > -Scott
> >
>|||I'm not sure if my original question has been answered - In the SQL
query, how am I going to add my parameters so that you can dynamically
change if it is being sorted by ASC or DESC? Thanks for all the help
so far, you guys are great!|||You can certainly do that in the query by using a dynamic query string
="SELECT ... " & IIF(Fields!Order.Value="Asc", "ASC", "DESC"). However, is
there any reason why you feel you need to do it in the query and not in the
report engine (post query)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<ScottWMcCarter@.gmail.com> wrote in message
news:1108412814.884740.156270@.g14g2000cwa.googlegroups.com...
> I'm not sure if my original question has been answered - In the SQL
> query, how am I going to add my parameters so that you can dynamically
> change if it is being sorted by ASC or DESC? Thanks for all the help
> so far, you guys are great!
>|||No, I just don't know how to do it in the report engine!! That is what
I'm looking for. Thanks!

Adding Order by on clustered index

Hi all

I recently noticed when trying to optimise a major query of a chess website
I am the webmaster of, that adding an order by for "gamenumber" which is a
clustered index field as in for example "order by timeleft desc, gamenumber
desc" actually speeded up the queries and reduced sql server 2000 timeouts.
I have an ASP error log and I am fairly sure that a dramatic reduction in
sql server timeouts is simply attributed to adding an extra seemingly
redundant order by field - which is the clustered index. Is this phenomena
at all possible or is it my imagination?!

Other special attributes of the query includes the use of "Top" to obtain a
maximum specified number of rows. Perhaps it is just the unique
characteristics of the query, but I would have thought that the less order
by fields would imply faster performance. Has anyone else noticed that a
seemingly redundant order by column on for example the clustered index
column, can actually help speed up queries?!

Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net> I have an ASP error log and I am fairly sure that a dramatic reduction in
> sql server timeouts is simply attributed to adding an extra seemingly
> redundant order by field - which is the clustered index. Is this
> phenomena
> at all possible or is it my imagination?!

Apparently, SQL Server is choosing a more efficient execution plan to
satisfy your ORDER BY specification. This implies that the plan without the
ORDER BY is sub-optimal and may be an indication that statistics need to be
updated. There may be other factors but it's difficult to say without DDL
and sample data that demonstrates the problem.

ORDER BY is never redundant. Regardless of any indexes, SQL Server can
return rows in any sequence it deems appropriate unless ORDER BY is
explicitly specified. Similarly TOP n is meaningless without ORDER BY. If
ORDER BY is not specified, any rows can be returned.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
news:cniegl$k8p$1$8300dec7@.news.demon.co.uk...
> Hi all
> I recently noticed when trying to optimise a major query of a chess
> website
> I am the webmaster of, that adding an order by for "gamenumber" which is a
> clustered index field as in for example "order by timeleft desc,
> gamenumber
> desc" actually speeded up the queries and reduced sql server 2000
> timeouts.
> I have an ASP error log and I am fairly sure that a dramatic reduction in
> sql server timeouts is simply attributed to adding an extra seemingly
> redundant order by field - which is the clustered index. Is this
> phenomena
> at all possible or is it my imagination?!
> Other special attributes of the query includes the use of "Top" to obtain
> a
> maximum specified number of rows. Perhaps it is just the unique
> characteristics of the query, but I would have thought that the less order
> by fields would imply faster performance. Has anyone else noticed that a
> seemingly redundant order by column on for example the clustered index
> column, can actually help speed up queries?!
> Best wishes
> Tryfon Gavriel
> Webmaster
> www.chessworld.net|||> Other special attributes of the query includes the use of "Top" to obtain a
> maximum specified number of rows. Perhaps it is just the unique

If you just added a Top N, that should have a dramatic impact.

> characteristics of the query, but I would have thought that the less order
> by fields would imply faster performance. Has anyone else noticed that a
> seemingly redundant order by column on for example the clustered index
> column, can actually help speed up queries?!

If the query was sorting on anything other than the clustered index,
or if the records were coming out in no particular order, and not in
order by the clustering, that will help, too.

A "clustered" table is saved in order, row-by-row, so when you want
your data back in order, it doesn't have to sort the data. Just do a
table scan, and they'll come back already sorted. So if you sort on
something else, you're giving up that optimization, and then having to
sort on something else, too.|||> A "clustered" table is saved in order, row-by-row, so when you want
> your data back in order, it doesn't have to sort the data. Just do a
> table scan, and they'll come back already sorted.

Just to be clear, SQL server may choose an access method that causes rows to
be returned out-of-sequence even with a simple query against a table with a
clustered index. ORDER BY *must* be specified when you want to ensure data
are returned in a particular order.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Forrest" <ValhallaPhotos@.gmail.com> wrote in message
news:9d371039.0411182019.1014f0ed@.posting.google.c om...
>> Other special attributes of the query includes the use of "Top" to obtain
>> a
>> maximum specified number of rows. Perhaps it is just the unique
> If you just added a Top N, that should have a dramatic impact.
>> characteristics of the query, but I would have thought that the less
>> order
>> by fields would imply faster performance. Has anyone else noticed that a
>> seemingly redundant order by column on for example the clustered index
>> column, can actually help speed up queries?!
> If the query was sorting on anything other than the clustered index,
> or if the records were coming out in no particular order, and not in
> order by the clustering, that will help, too.
> A "clustered" table is saved in order, row-by-row, so when you want
> your data back in order, it doesn't have to sort the data. Just do a
> table scan, and they'll come back already sorted. So if you sort on
> something else, you're giving up that optimization, and then having to
> sort on something else, too.|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
>> A "clustered" table is saved in order, row-by-row, so when you want
>> your data back in order, it doesn't have to sort the data. Just do a
>> table scan, and they'll come back already sorted.
> Just to be clear, SQL server may choose an access method that causes
> rows to be returned out-of-sequence even with a simple query against a
> table with a clustered index. ORDER BY *must* be specified when you
> want to ensure data are returned in a particular order.

Just to elaborate on Dan's comment a little more. In SQL Server 6.5 you
could be fairly sure that the rows would be returned in order of the
clustered index if you said things like:

SELECT * FROM tbl

But in SQL7 and later, SQL Server may open parallel streams on the table,
and the result is likely to be semi-ordered (which could be deceivable).
This is particularly true for large queries.

(In all versions of SQL Serger, a WHERE clause a on column with a non-
clustered index or a Select list covered by a non-covered index is likely
to use that index as the starting point for the return order.)

Thus, to echo Dan: you *must* specify ORDER BY if you want a certain order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding Formula in CRYSTAL REPORT

Hi,

I am using a Crystal reports in order to get the information from the database (ORACLE 9).
The source of data is the script which I added via add command using ORACLE OLE Provider.

I would like to say that now I am trying to implement CR formulas in order to give the
following message :
"NO USERS LOGIN"

I wanted that when there is no user connected to the database Crystal Report gives this message.

In order to that I created the following forumla from the formula Tab, but it didn't give any
message on crystal reoport, when no user login to the database:

Formula 1

if count ({command.USER}) < 0
then "No User Login to Database"

or

Formula 1

if count ({command.USER}) < 1
then "No User Login to Database"

I tried to drag this formula on various section of reports (Detail, Report header), but it
did'nt work.

But when I used greater than sign instead of using less than size, this formula works and
gives the message.

For Example:

Formula 2

if count ({command.USER}) > 0
then "User Login to Database"

Could someone please inform what changes I made in the formula 1 in order to display the message
"No User Login to Database", when there is no user login to database.

I also like to say that the routine which I am using in the Crystal Report gives me information
of several Users who are connecting to the database.

Thanks

DavidI think that's normal, because crystal create a connection to your database to verify the number of user connections. So I think that you must put the following formula in your report:

if count ({command.USER}) < 2
then "No User Login to Database"

To verify what I say just print the field {command.USER} to see his value.|||Hi ariqa,
Use the following formula

if isnull ({command.USER})
then "No User Login to Database"

Madhivanan|||Hi Machuet & Madhi,

Thanks for your cooperation.
Yours solution helped me.

Thanks

Regards

David

Adding Column To A Table At a Specific Position

Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:

>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>

Adding Column To A Table At a Specific Position

Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
On Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:

>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>

Adding Column To A Table At a Specific Position

Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
--
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>

adding an identity column to existing table

I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.

thx

Kat

The alter table didn't work. Just created a new table with the identity column and inserted into that. So never mind... Unless there is a different way to do this. I know, it has been a while for me.

Kat

|||

It should:

create table test
(
value varchar(10)
)
go
insert into test
select 'a'
union all
select 'b'
union all
select 'c'
go
select *
from test
/*
value
-
a
b
c
*/

alter table test
add testId int identity
go
select *
from test

Can you post the syntax/error you got if it doesn't work for you?

|||

Hi Louis,

This did work. I think my syntax was the problem when altering the table, which I discarded. I was trying to add a primary key constraint at the same time and my syntax was obviously messed up. I followed the BOL syntax for altering a table, adding an identity column along with a primary key constraint. It didn't appear to say that this was not possible unless I read the 'alter table' section incorrectly. Can you tell me if this is possible and what the correct 'alter table' add identitiy, and make it a primary key at the same time?

thx,

Kathleen

|||

Just add PRIMARY KEY:

create table test
(
value varchar(10)
)
go
insert into test
select 'a'
union all
select 'b'
union all
select 'c'
go
select *
from test
/*
value
-
a
b
c
*/

alter table test
add testId int constraint PKTest identity primary key
go
select *
from test

The constraintPKTest in bold italics is optional

|||

Even though we allow the syntax below:

alter table test
add testId int constraint PKTest identity primary key

I think it is a bug to allow the IDENTITY keyword in between the constraint definition. The correct syntax is:

alter table test
add testId int identity constraint PKTest primary key

I will file a bug internally for the invalid syntax.

|||That was a mistake. I can't believe I didn't notice that. I also can't believe it compiled :)

Adding an auto-increment column to existing table with a particular order

Hello all,

I'm using SS2K on W2k.

I'v got a table say, humm, "Orders" with two fields in the PK:
OrderDate and CustomerID. I would like to add an "ID" column which
would be auto-increment (and would be the new PK). But, I would really
like to have orders with the oldest OrderDate having the smallest ID
number and, for a same OrderDate, I'd to have the smallest CustomerID
first. So my question is:

How could I add an auto-increment column to a table and make it create
its values in a particular order (sort by OrderDate then CustomerID
here)?

In the real situation, the table I want to modify has around 500k
records and the PK has 5 fields and I want to sort on three of them.

Thanks for you help

YannickIDENTITY is the proper name for an auto-incrementing surrogate key
column in SQL Server.

In principle you should avoid using IDENTITY in any way that is
meaningful outside the database. In fact, don't show the IDENTITY
column to your users AT ALL in your applications. The main reason why
we use IDENTITY columns is to provide an immutable surrogate key. We
know from experience that once we expose data to users it may become
significant in some way, at which point its notional immutability may
be at risk because it is no longer just an attribute of the system - it
belongs to the outside world as well. Then we may have a problem
because an IDENTITY column is not updateable and because sometimes (for
example when you integrate data or perform multiple row INSERTs) you
can't always dictate its value directly on INSERT either.

So for those reasons I'd say that you shouldn't concern yourself about
the order in which the IDENTITY values are assigned. The order doesn't
matter much if only the developers and admins can see it.

If you want the cosmetic reassurance of seeing the values in the
"right" order you can try creating a new table and using INSERT SELECT
with an ORDER BY clause to populate it. Be aware that that method is
definitely NOT always guaranteed to generate the values in the correct
order - so don't count on it. You may also find that the effort of
creating and populating a new table negates any slight advantage of
"fixing" the IDENTITY sequence.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> If you want the cosmetic reassurance of seeing the values in the
> "right" order you can try creating a new table and using INSERT SELECT
> with an ORDER BY clause to populate it. Be aware that that method is
> definitely NOT always guaranteed to generate the values in the correct
> order - so don't count on it. You may also find that the effort of
> creating and populating a new table negates any slight advantage of
> "fixing" the IDENTITY sequence.

In fact, the word I have from Microsoft people is that INSERT SELECT
ORDER BY is guaranteed to produce the expected result.

This however does not apply to SELECT INTO.

I would recommend, nevertheless, to add an OPTION (MAXDOP 1) as an
extra precaution.

It is also worth pointing that the main reason for having IDENTITY
in opposition ro rolling your own, is that IDENTITY scales better in
an environment where there is a heavy insertion rate. When you roll
your own, inserts tend to get serialized.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

You don't believe everything MS tell you, do you? :-) Thanks due to Gert-Jan
for the following repro. Tested on SP4 (with or without MAXDOP) it doesn't
order the INSERT (YMMV). Admittedly it might sometimes be possible to order
*some* INSERTs but how are we supposed to know which will work, which won't
and which will fall apart after then next hotfix?

Hopefully the ORDER BY on INSERT nonsense will go away one day. It's plainly
a limitation rather than a feature.

--drop table Item
create table Item
(ItemID int not null primary key
,ItemGUID uniqueidentifier not null -- newid()
,ParentTopicID int null
,OwningUserID int null
,Name varchar(100) null
,SEName varchar(100) null
,ShortName varchar(50) null
,AboutDisabled bit null
,DisplayOrder int null
,Description varchar(200) null
,ShopDisabled tinyint null
,ShopNoCaption varchar(20) null
,GeneralShopDisabled tinyint null
,RelatedSitesDisabled tinyint null
,IsDisabled tinyint null
,CreatedOn datetime null
,VisitURL varchar(256) null
,VisitPrompt varchar(200) null
,ShopURL varchar(256) null
,Address1 varchar(100) null
,Address2 varchar(100) null
,Suite int null
,City varchar(30) null
,State char(2) null
,ZipCode char(5) null
,Country varchar(50)
,Phone varchar(20)
,FAX varchar(20)
,URL varchar(256)
,Email varchar(100)
,OvertureKeywords varchar(200)
,SE_Title varchar(100)
,SE_Description varchar(50)
,SE_Keywords varchar(50)
,ExtendedData varchar(100)
,SubmittedByUserID int
)
insert into Item (ItemID,IsDisabled,ZipCode,Name,itemguid,CreatedOn
,RelatedSitesDisabled,GeneralShopDisabled,ShopDisa bled,DisplayOrder,AboutDisabled
,ShortName
) select id,0,'abcd',name,newid(),dateadd(ms,-(id%2000000),getdate())
,id%2,id%3,id%4,1,0, left(name,15)
from sysobjects

--drop table ItemRatings
create table ItemRatings
(ItemID int not null primary key
,rating int not null
,HasComment tinyint not null
,TopicID int null
,userID int null
,userAge int null
,userGender char(1) null
,f7 varchar(20) null
,f8 varchar(20) null
,f9 varchar(20) null
,f10 varchar(20) null
,f11 varchar(20) null
,f12 varchar(20) null
,f13 varchar(20) null
,f14 varchar(20) null
,f15 varchar(20) null
,f16 varchar(20) null
,f17 varchar(20) null
,f18 varchar(20) null
,f19 varchar(20) null
,f20 varchar(20) null
)
insert into ItemRatings(ItemID,rating,HasComment,TopicID,userI D
,userAge,userGender,f7,f8,f9,f10,f11,f12)
select id,id%10,id%2,90,20069715,id%80,'M'
,'aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaa a','aaaaaaaaaa','aaaaaaaaaa'
from sysobjects
insert into ItemRatings(ItemID,rating,HasComment,TopicID,userI D
,userAge,userGender,f7,f8,f9,f10,f11,f12)
select id%2000000000+1000000,id%10,id%2,90,id%19,id%80,'M '
,'bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bb bbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bbbbbb bbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb'
from sysobjects

--drop table Alerts
create table Alerts
(AlertID int not null primary key
,ItemID int not null
,alertOwnerUserID int not null
,alertType int not null
,f4 char(200) null
)
insert into Alerts
select id,id,20069715,1,' '
from sysobjects

--drop TABLE #tmp
CREATE TABLE #tmp
(rownum int not null identity(1,1) primary key
,[ItemID] [int] NOT NULL
,[ItemGUID] [uniqueidentifier] NOT NULL
,[ParentTopicID] [int] NULL
,[OwningUserID] [int] NULL
,[Name] [varchar] (250) NOT NULL
,[SEName] [varchar] (250) NULL
,[ShortName] [varchar] (35) NOT NULL
,[AboutDisabled] [bit] NOT NULL
,[DisplayOrder] [int] NOT NULL
,[Description] [ntext] NULL
,[ShopDisabled] [tinyint] NOT NULL
,[ShopNoCaption] [tinyint] NULL
,[GeneralShopDisabled] [tinyint] NOT NULL
,[RelatedSitesDisabled] [tinyint] NOT NULL
,[IsDisabled] [tinyint] NOT NULL
,[CreatedOn] [datetime] NOT NULL
,[VisitURL] [ntext] NULL
,[VisitPrompt] [varchar] (50) NULL
,[ShopURL] [ntext] NULL
,[Address1] [varchar] (255) NULL
,[Address2] [varchar] (255) NULL
,[Suite] [varchar] (100) NULL
,[City] [varchar] (255) NULL
,[State] [varchar] (100) NULL
,[ZipCode] [varchar] (10) NULL
,[Country] [varchar] (100) NULL
,[Phone] [varchar] (50) NULL
,[FAX] [varchar] (50) NULL
,[URL] [ntext] NULL
,[Email] [varchar] (100) NULL
,[OvertureKeywords] [ntext] NULL
,[SE_Title] [ntext] NULL
,[SE_Description] [ntext] NULL
,[SE_Keywords] [ntext] NULL
,[ExtendedData] [ntext] NULL
,[SubmittedByUserID] [int] NULL
,[NumRatings] [int] not null
,[AvgRating] [decimal](6, 5) not null
,[NumComments] [int] not null
,[UserRating] [decimal](3,2) not null
,[AlertUser] [int] not null
)

-- set showplan_text on

INSERT #tmp (ItemID, ItemGUID, ParentTopicID, OwningUserID, Name,
SEName, ShortName, AboutDisabled, DisplayOrder,
Description, ShopDisabled, ShopNoCaption, GeneralShopDisabled,
RelatedSitesDisabled, IsDisabled, CreatedOn, VisitURL,
VisitPrompt, ShopURL, Address1, Address2, Suite, City, State, ZipCode,
Country, Phone, FAX, URL, Email,
OvertureKeywords, SE_Title, SE_Description, SE_Keywords, ExtendedData,
SubmittedByUserID, NumRatings, AvgRating,
NumComments, UserRating, AlertUser)
SELECT
i.*
, isnull(a.NumRatings,0) NumRatings
, isnull(a.AvgRating, 0) AvgRating
, isnull(a.NumComments, 0) NumComments
, isnull(r.rating, 0)
, isnull(al.alertOwnerUserID, -1)
FROM Item i (nolock)
join (
select
ir.itemid
, count(ir.rating) NumRatings
, avg(ir.rating*1.0) AvgRating
, sum(ir.HasComment) NumComments
from ItemRatings ir with (nolock)
where ir.TopicID = 90
and ir.userAge between 0 and 255
and ir.userGender = coalesce(null, ir.userGender)
group by ir.itemid
) a on i.itemid = a.itemid
left join ItemRatings r
on i.itemid = r.itemid and r.userid = 20069715
left join Alerts al
on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and
alertType = 1
WHERE i.IsDisabled = 0
and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, ''))
ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc

-- set showplan_text off

select top 100 rownum,avgrating,numratings,name
from #tmp
order by rownum

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> You don't believe everything MS tell you, do you? :-)

If MS says "we guarantee this", and I get an unexpected result, that's
a bug, for which I can require a hotfix, if there are business reasons
enough for it. If they say "this is undefined", I am left on my own in
the wilderness.

It might be that this was only for SQL 2005, but I'm quite sure that
it applied to SQL 2000 as well. I need to research this a little more.
(The repro does produce an ordered output when I test on SQL 2005.

> Thanks due to Gert-Jan for the following repro. Tested on SP4 (with or
> without MAXDOP) it doesn't order the INSERT (YMMV). Admittedly it might
> sometimes be possible to order *some* INSERTs but how are we supposed to
> know which will work, which won't and which will fall apart after then
> next hotfix?

With that reasoning, how could you trust SQL Server at all? After the
next hotfix, it may say that 2 + 2 = 5.

I recall that during the beta 2 phase of SQL 2000, someone in the beta
groups asked about this, because he had a case where he did not get the
expected results. But he also reported that with the RTM version, he did
get the correct results. I also recall that I tried to produce a repro
where the IDENTITY values did not follow the order. My plan was then
to open a case, in order to get a statement whether I had a bug or
not. However, I was never able to produce such a repro.

So saying that it "works sometimes" is quite an understatement. It works
most of the time, and in this particular case the question was about a
one-off thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Take a look at this KB:
http://support.microsoft.com/defaul...kb;en-us;273586

It gives an example and does indeed say that IDENTITY order is guraranteed.
What it doesn't do is explain whether this is true in the case of every
INSERT. The reason for my doubt is that this KB was changed - it used to
state clearly that the IDENTITY behaviour was undefined!

I think the question will be pretty academic soon. In 2005 the ROW_NUMBER
function is surely a better way (better defined at least) to generate a
numbered sequence.

--
David Portas
SQL Server MVP
--

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F6485618D6Yazorman@.127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
>> You don't believe everything MS tell you, do you? :-)
> If MS says "we guarantee this", and I get an unexpected result, that's
> a bug, for which I can require a hotfix, if there are business reasons
> enough for it. If they say "this is undefined", I am left on my own in
> the wilderness.
> It might be that this was only for SQL 2005, but I'm quite sure that
> it applied to SQL 2000 as well. I need to research this a little more.
> (The repro does produce an ordered output when I test on SQL 2005.
>> Thanks due to Gert-Jan for the following repro. Tested on SP4 (with or
>> without MAXDOP) it doesn't order the INSERT (YMMV). Admittedly it might
>> sometimes be possible to order *some* INSERTs but how are we supposed to
>> know which will work, which won't and which will fall apart after then
>> next hotfix?
> With that reasoning, how could you trust SQL Server at all? After the
> next hotfix, it may say that 2 + 2 = 5.
> I recall that during the beta 2 phase of SQL 2000, someone in the beta
> groups asked about this, because he had a case where he did not get the
> expected results. But he also reported that with the RTM version, he did
> get the correct results. I also recall that I tried to produce a repro
> where the IDENTITY values did not follow the order. My plan was then
> to open a case, in order to get a statement whether I had a bug or
> not. However, I was never able to produce such a repro.
> So saying that it "works sometimes" is quite an understatement. It works
> most of the time, and in this particular case the question was about a
> one-off thing.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Take a look at this KB:
> http://support.microsoft.com/defaul...kb;en-us;273586
> It gives an example and does indeed say that IDENTITY order is
> guraranteed.
> What it doesn't do is explain whether this is true in the case of every
> INSERT. The reason for my doubt is that this KB was changed - it used to
> state clearly that the IDENTITY behaviour was undefined!

Since it does insert any waivers, disclaimers etc, it is a natural
interpretation that you can expect INSERT SELECT to work under all
circumstances, and thus Gert-Jan's repro is a bug. Or the article is
in error.

> I think the question will be pretty academic soon. In 2005 the ROW_NUMBER
> function is surely a better way (better defined at least) to generate a
> numbered sequence.

I've checked my references for this statement, and the statement for
SQL 2005 is from a more reliable source. So I would not say that
row_number() is any better defined than IDENTITY. But I do agree for
many situations, row_number() is preferable. However, in this particular
case, Yannick wanted to populate an IDENTITY column, so a plain ORDER BY
is certainly simpler, as it relieves him from SET IDENTITY_INSERT ON.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks all for you time on this question.

I clearly understand that IDENTITY shouldn't mean anything and it is
not that important that it follows exactly my "sort wishes". It was
simply to make table hacking more consistant with other tables where I
sort by IDENTITY to see last insertions. Using the INSERT INTO...
SELECT... ORDER BY will do the job... I hope so! ;o)

Yannick

Adding a variable to a stored procedure

Hi,

I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.

Help!!!

I want to add @.OrderBy at the end of :

------
SELECT *
FROM Annonces
WHERE CategoryID = @.CategoryID AND DateOnCreate >= @.DateFinale
------

My declaration :

Declare @.OrderBy nvarchar(50)

If @.Sort = 'date_de'
set @.OrderBy = ' ORDER BY DateOnCreate DESC'
Else
set @.OrderBy = ' ORDER BY DateOnCreate ASC'

You want your code to be built dynamically within in the SQL it sounds like.

I don't think you can do that. Variables can not have inline code in SQL.

|||

You can use a CASE in the ORDER BY with limited flexibility. Do a quick google search or search for my previous post. I think I posted a similar sample a while back.

|||

Dynamic SQL is the classic solution to this, if you need complete flexibility, as Kelsey said. CASE is way less flexible.

You can also write the query in SQLCLR code, if you're using SQL Server 2005 and need lots of flexibility.

Or you can wait until SQL Server 2008, which reportedly will have this capability.

Don

|||

Ok thanks,

Now, how can i add this line

--> AND dbo.fn_GetDistance(@.CodePostal, UserNumber) <= @.Dist <--

In this code :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + '''' + @.OrderBy

I tried many things but all failed.

Thanks|||

In fact, if the dataset is small, your front end is the best place to do this dynamic sorting.

|||

This should give you an idea.:

Declare @.stmtvarchar(1000), @.CodePostalvarchar(10), @.CategoryIDint, @.DateFinaledatetime, @.orderbyvarchar(10)Select @.CodePostal ='567', @.CategoryID = 10, @.DateFinale =getdate(),@.orderby =' name asc'select @.stmt ='SELECT * , dbo.fn_GetDistance(''' + @.CodePostal +''', UserNumber) AS DistanceFROM AnnoncesWHERE CategoryID =' +convert(Varchar,@.CategoryID) +' AND DateOnCreate >= ''' +convert(varchar,@.DateFinale) +'''' + @.OrderBy

|||

Hi,

I tried :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + ''' AND dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) <=' + @.Dist + '' + @.OrderBy

But i get :

Conversion failed when converting the varchar value 'SELECT *, .........blablabla....... <=' to data type int.

I dont understand?

|||

ok i got it!!!

Finally...

My error was i tried to use an integer in a varchar statement.

So i use:

Set@.Distance =convert(Varchar, @.Dist)

And now i can do:

select@.stmt ='SELECT *, dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID ='+ @.CategoryID +' AND DateOnCreate >='''+ @.DateFinale +''' AND dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) <='+ @.Distance +''+ @.OrderBy