Select from inserted stmt returns an error

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 2005 Forums

Transact-SQL (2005)

Select from inserted stmt returns an error

Author

Topic

foralexstar
Starting Member

4 Posts

Posted - 2010-12-21 : 11:54:25

I have a stored procedure used by a Puller records program.
The purpose of it is to select one row and update a status field, so next time when it is executed, the same row would't be returned.

I am trying to avoid a potential table lock by using next script:



CREATE PROCEDURE [dbo].[sp_GetQueueItems] 
@SubscriptionID AS int 
AS 

UPDATE TOP (1) [Queue]
set [Queue].[Status] = 'P'
WHERE [Queue].[Status] = 'O'
and SubscriptionID=@SubscriptionID

SELECT * FROM INSERTED

GO

When I try too execute it, I receive an error:
Invalid object name 'INSERTED'
I do not understand why I am getting it. Normally, it is when my inserted is out of scope. But here, in my case, it is should be OK.
Any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-21 : 11:57:06

Do you actually have a table called INSERTED or are you trying to use the INSERTED trigger table? INSERTED trigger table is only available from within a trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

foralexstar
Starting Member

4 Posts

Posted - 2010-12-21 : 12:01:10

I am trying to use INSERTED trigger table. What would be solution in my case?

sanjnep
Posting Yak Master

191 Posts

Posted - 2010-12-21 : 12:07:54

You need to create a trigger and pass the value from inserted table to physical table. Inserted table can't access outside the scope of trigger.

go

create trigger tr_update
for update on xxx_tablename
as
truncate table xxx_already_table
insert into xxx_already_table from inserted

go

Now you can do
select * from xxx_already_table

Thanks,
Sanjeev

Kristen
Test

22859 Posts

Posted - 2010-12-21 : 12:13:15

UPDATE TOP (1) [Queue]
set [Queue].[Status] = 'P'
OUTPUT Inserted.Col1, Inserted.Col2, ... INTO @Updated
WHERE [Queue].[Status] = 'O'
and SubscriptionID=@SubscriptionID

SELECT * FROM @Updated

foralexstar
Starting Member

4 Posts

Posted - 2010-12-21 : 12:28:52

Thank you Kristen!
This is exactly what I need!

Alex.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-21 : 13:06:36

Lol. Thanks Kristen for reading more into the problem than we did!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources