| Author |
Topic |
|
weitzhandler
Yak Posting Veteran
64 Posts |
 Posted - 2009-01-22 : 08:34:02
|
I want that the default value or binding column should be this query:DECLARE @SortOrder int SELECT TOP (1) @SortOrder = SortOrder FROM JobContactHeader IF ISNULL (@SortOrder, '') = '' print 0 ELSE PRINT @SortOrder + 1 I am also not sure that the SQL I wrote is so correct, any code improvements will be appreciated as well.thank you!Shimi |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2009-01-22 : 08:41:01
|
| [code]Select Top 1 IsNull(SortOrder, -1) + 1 from JobContactHeader[/code]As far as I know, default expression has to be constant. I don't think you will be able to use query output as a default.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-22 : 08:49:42
|
| or you need to create a scalar function to return this value and use it as default value of column. |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2009-01-22 : 09:39:29
|
| First of all, thanks for the code improvement it was indeed helpful.2, I don't care to make a scalar function, the thing is that i have this 'SortOrder' col. in many tables and i want them to check in the current table that the data is now inserted to.as i said before im not a sql expert and i donno how to it, any examples will really appreciatedthanks all of you for your timeShimi |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2009-01-22 : 09:48:55
|
| actully, i need the function to get the the top of the SortOrder col. not the top of the ID col.actually, I think I have to create a trigger (or whatever it is, maybe it should be in addition to the 'def value or binding' prop.) in for all these table that when inserting a row it reorders all the rows in the column, e.g. when a row was inserted and the sortorder is 55 and there is already 55 in the table, it should push all the folloing 'sortorder' values +1 and vice versa (i.e. if user deletes a col all the values in the sortorder col in all the rows that the sortorder value > deleting row's sortorder should be - in their sortorder.thanks to all of youi hope im not drivin you nutsi just wanna learnthanksShimi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-22 : 10:36:13
|
| nope...trigger is a bad choice. you should be trying to do this reordering logic within insert sp itself. |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2009-01-22 : 10:55:10
|
quote:
Originally posted by visakh16
nope...trigger is a bad choice. you should be trying to do this reordering logic within insert sp itself.
the thing is that in my DB are many tables with this SortOrder col. i want to have some internal (in the server) mechanism that controls this column for all the tables when inserting/deleting/updating.any ideas will be appreciated.thank you.Shimi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-22 : 10:59:10
|
| wont you be having seperate insertion sps for each table? |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2009-01-22 : 11:03:07
|
 there are so many[:'(]Shimi |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2009-01-22 : 11:24:49
|
| isn't there any solution!?Shimi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-22 : 12:07:26
|
| actually whats the purpose of this sortorder column?even without it you can simply retrieve the records in the order you want using order by clause in select. even if you want to get them in order of insertion, you can add an identity column or an audit trail column like datemodified. |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2009-01-24 : 14:00:08
|
| I have a 'header' table (one to many, and a 3rd table that has the ID of both tables to relate between them).I want the user should be able to sort the rows according to his needs.then look at what I wrote in my previous post "actully, i need the function to get the the top of the SortOrder col. not the top of the ID col.".thank you so much!Shimmy |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-25 : 08:09:36
|
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Need help in using 'Default value or binding'