| Author |
Topic |
|
HC
Starting Member
4 Posts |
 Posted - 2006-03-10 : 07:13:36
|
| HiI need to create a function that will return a sequence number. The reason I need this to be a function is that I will use the function to create a view. In Oracle I would do something like this:create sequence seq; create or replace function f return number as l_seq number; begin select seq.nextval into l_seq from dual; return l_seq; end; / create or replace view v as select f from dual;Since Sql Server does not have a sequence object I have created a sequence table with a identity column and a procedure that inserts a dummy variable and returns the identity id. This works ok but to get my sequence number I must declare a variable, execute the procedure and select the return value. I need the procedure to be a function so that I can use it to create my view. Since I cannot use DML in a function and I cannot call a procedure from a function I am stuck. Do I have to create an extended procedure ? Any help is appreciated |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-10 : 07:24:21
|
| I think you should look at what you are trying to do rather than how to do it.I suspect your objective could be met in a different way.You are trying to update data from a select statement which doesn't sound sensible.Why do you want to do it from a view?It sounds like you just want a sequence number on each entry in a resultset maybe?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
HC
Starting Member
4 Posts |
Posted - 2006-03-10 : 07:39:36
|
I need it to be a view because I am going to use it in a 3rd party product (Documentum) and I am only allowed to register tables and views in Documentum.The sollution is quite nice with Oracle sequences but I agree that updating data with a select can be dirty. I could do it without the view but I still need to be able to select a sequence number in one go and to be sure that the sequence number is unique quote:
Originally posted by nr
I think you should look at what you are trying to do rather than how to do it.I suspect your objective could be met in a different way.You are trying to update data from a select statement which doesn't sound sensible.Why do you want to do it from a view?It sounds like you just want a sequence number on each entry in a resultset maybe?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-10 : 08:35:51
|
| Again, it may be best to explain to us what you need to do in detail, with an example, rather than how you think it should be accomplished. |
|
|
|
HC
Starting Member
4 Posts |
Posted - 2006-03-10 : 08:50:40
|
quote:
Originally posted by jsmith8858
Again, it may be best to explain to us what you need to do in detail, with an example, rather than how you think it should be accomplished.
I want to be able to get the next sequence number by either selecting from a view or selecting from a function:From within Documentum using their DQLq.setDQL("select f from dm_dbo.v;");Or simply sending a sql statement in a string (from a Java program):sql = "SELECT repno_seq_nextval";stmt = theConn.createStatement();rs = stmt.executeQuery(sql); |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-10 : 09:08:49
|
| How are you defining "get the next sequence number" ? Are you talking about a table with an IDENTITY primary key and you want to know what the next number will be before inserting a row? If so, why do you need to know this? Are you aware of SCOPE_IDENTITY() and built-in SQL Server functions that return the last created IDENTITY column in a table *after* you insert a row? You still are not explaining your situation to well. Remember, we don't know your system and what you are working on, we can only help you with the information you provide to us. At this point, all we can do is keep guessing as to what you need. |
|
|
|
HC
Starting Member
4 Posts |
Posted - 2006-03-10 : 09:22:19
|
quote:
Originally posted by jsmith8858
How are you defining "get the next sequence number" ? Are you talking about a table with an IDENTITY primary key and you want to know what the next number will be before inserting a row? If so, why do you need to know this? Are you aware of SCOPE_IDENTITY() and built-in SQL Server functions that return the last created IDENTITY column in a table *after* you insert a row? You still are not explaining your situation to well. Remember, we don't know your system and what you are working on, we can only help you with the information you provide to us. At this point, all we can do is keep guessing as to what you need.
I am using SCOPE_IDENTITYMy SQLServer proc looks like this:create procedure repno_seq_nextval(@nextval int out)asinsert into repno_seq DEFAULT VALUESset @nextval = SCOPE_IDENTITY()I have no problems getting the next value but I want to call this procedure with a function so that I can get the next value in a single select but that does not seem possible. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-10 : 09:30:12
|
| >>I have no problems getting the next value but I want to call this procedure with a function so that I can get the next value in a single select but that does not seem possible.Why do you need the "next value" in a single select? Can you please give us some actual details about what you are doing? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-12 : 13:16:47
|
| I don't thinkj what you are describing is possible (in fact it doesn't sound meaningful).You ought to think about the objective again.A select doesn't update persistent values but you can calculate and return a value.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Sequence from a function