Transactions in a view - stored proc in select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transactions in a view - stored proc in select

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-01-21 : 18:33:49

I basically need to call a procedure to retrieve nextnumbers for an insert operation on my table....Could I use a view as shown below...

CREATE VIEW vwGetNextItemID
AS
begin tran

update dbo.NextNumber
set NextNumber = NextNumber + 1
where TableName = 'Customers'
if @@rowcount = 0
begin
insert into dbo.extNumber(TableName, NextNumber)
values('Customers', 1)
select 1
end
else
begin
select NextNumber
from NextNumber
where TableName = 'Customers
end
commit tran

this was originally a stored proc.

I'm trying to avoid using a cursor, so I want my insert to be contained within a select statement...

eg: insert into mytable(col1, col2)
select @col1, (select viewresult)

The main problem Is that I need to get this next number from a select into statement during inserts and this number is needed for each row of data inserted. I can't use a function obviously.

is there a way I can call a stored procedure within a select statement can I use transactions within a view...I need the transaction to maintain the numbering integrity?

Please help guys....

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-21 : 20:21:15

I sincerely dont understand the whole picture, But am wondering if you are trying to get the last inserted auto incremeneted value in a table in the transaction ?

If this is the case have you tried using the scope_identity() function ???

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-21 : 20:29:20

A view can only contain a SELECT statment.

No UPDATE, INSERT, DELETE, EXECUTE, BEGIN TRANSACTION, IF, DECLARE, BEGIN, END, etc.

CODO ERGO SUM

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-21 : 20:46:59

maybe its best you explain what you are trying to achieve.

There might be a better way of killing a fly, than to use missles

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-01-21 : 21:00:10

quote:
Originally posted by afrika

maybe its best you explain what you are trying to achieve.

There might be a better way of killing a fly, than to use missles

Thank you guys,

I basically have a stored procedure that reads a table containing a two columns, one col is table names and the other col is the last number used for that table...this we implemented to get away from using identity functionality...that was a long debate, but we settled for this procedure that tracks the last inserted number for each table that needed a kind of auto increment value in one of its columns.

Now, I am trying to perform inserts to one of the tables that uses this in-house nextnumber generator.

so I basically want to do something like

INSERT INTO mytable ( col1, col2)
SELECT col1, (get next number from stored proc here)
FROM mytable

I'm trying to avoid using a cursor and calling the getnewnumber proc for each row.....

i.e duplicate each row in the table, but change the autonumber column

the getnewnumber is same as logic as in the view

CREATE proc GetNextItemID @tableName varchar(50), @newnumber int output
AS
begin tran

update dbo.NextNumber
set NextNumber = NextNumber + 1
where TableName = @tableName
if @@rowcount = 0
begin
insert into dbo.extNumber(TableName, NextNumber)
values(@tableName, 1)
select @newnumber = 1
end
else
begin
select @newnumber = NextNumber
from NextNumber
where TableName = @tableName
end
commit tran

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-21 : 22:02:48

I think you will have many occasions to regret the design you have chosen. There are many reasons for this:
1. You have created one table that will be involved in virtually every transaction. This is an invitation to deadlocks and blocked transactions.
2. To maintain the table correctly, you will have to limit yourself to single row inserts. This will further increase the changes for deadlocks and blocks.
3. Any incorrectly handled transaction in any stored procedure can result in all system users blocked.
4. You are limiting system performance by serializing all transactions through updates to the same rows in a single table.

I am speaking from the experience of having to repair a system where the designers made this serious mistake, so I urge you to reconsider. Otherwise, you should start developing your skill at tracing cascading blocks, because you will need it.

Why do you feel you have to get away from “using identity functionality”? What do you feel that you have to do that identity will not provide?

CODO ERGO SUM

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-01-22 : 01:11:43

quote:
Originally posted by Michael Valentine Jones

I think you will have many occasions to regret the design you have chosen. There are many reasons for this:
1. You have created one table that will be involved in virtually every transaction. This is an invitation to deadlocks and blocked transactions.
2. To maintain the table correctly, you will have to limit yourself to single row inserts. This will further increase the changes for deadlocks and blocks.
3. Any incorrectly handled transaction in any stored procedure can result in all system users blocked.
4. You are limiting system performance by serializing all transactions through updates to the same rows in a single table.

I am speaking from the experience of having to repair a system where the designers made this serious mistake, so I urge you to reconsider. Otherwise, you should start developing your skill at tracing cascading blocks, because you will need it.

Why do you feel you have to get away from “using identity functionality”? What do you feel that you have to do that identity will not provide?

CODO ERGO SUM

We just need to take control of the auto generated numbers, nothing aginst the Identity functionality...of course we use the identity function for other scenarios...just not in this particular instance because of our peculiar needs

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-22 : 02:38:45

I agree with Michael, there are simpler ways of doing things. As spirit1 says "dont fight the flow"

Hi tchinedu,
i noticed a few things about your auto generated numbers.

1. Its generated once
2. Its incremented (after generation) by one
Solution: You could create an auto incremented int value and increment with 1, your autoID column does not have to start with 1, you can start as you wish

You are reinventing the wheel

3. One question? Do these tables have a primary key ?

I also would advice: "Your reconsider your design"

You initially start of with implementing a veiw to solve this solution is flawed and in "my opinion" this is not the convention

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources