|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedure questionI am still fairly new to SQL server and I am trying to make a stored
procedure that I can do the following: run select statement on a table update a table return the data that was selected in line 1. Update -- TIA Altman wrote:
> I am still fairly new to SQL server and I am trying to make a stored You'll need to supply more information than that for a specific solution. In> procedure that I can do the following: > > run select statement on a table > update a table > return the data that was selected in line 1. > > Update > the meantime, look at this: http://www.aspfaq.com/show.asp?id=2201 Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. I have a stored procedure that I want to return everything that hasn't been
marked as being retrieved yet. In the process I need to mark the data that it has been retrieved. Select * from myTable where recieved = 0 update myTable set received = 1 where received = 0 <now return what was selected in the first line> I can't do my select second because it wouldn't return anything. -- Show quoteTIA Altman "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:Oz35gQK4FHA.1396@TK2MSFTNGP12.phx.gbl... > Altman wrote: >> I am still fairly new to SQL server and I am trying to make a stored >> procedure that I can do the following: >> >> run select statement on a table >> update a table >> return the data that was selected in line 1. >> >> Update >> > You'll need to supply more information than that for a specific solution. > In > the meantime, look at this: > http://www.aspfaq.com/show.asp?id=2201 > > Bob Barrows > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > The result from the initial select statement will get returned. Try it:
create procedure GetUnRetrieved AS SET NOCOUNT ON Select * from myTable where recieved = 0 update myTable set received = 1 where received = 0 Run the procedure. See that the resultset is returned. Bob Barrows Altman wrote: Show quote > I have a stored procedure that I want to return everything that > hasn't been marked as being retrieved yet. In the process I need to > mark the data that it has been retrieved. > > Select * from myTable where recieved = 0 > update myTable set received = 1 where received = 0 > <now return what was selected in the first line> > > I can't do my select second because it wouldn't return anything. > > -- > TIA > > Altman > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:Oz35gQK4FHA.1396@TK2MSFTNGP12.phx.gbl... >> Altman wrote: >>> I am still fairly new to SQL server and I am trying to make a stored >>> procedure that I can do the following: >>> >>> run select statement on a table >>> update a table >>> return the data that was selected in line 1. >>> >>> Update >>> >> You'll need to supply more information than that for a specific >> solution. In >> the meantime, look at this: >> http://www.aspfaq.com/show.asp?id=2201 >> >> Bob Barrows >> >> -- >> Microsoft MVP -- ASP/ASP.NET >> Please reply to the newsgroup. The email account listed in my From >> header is my spam trap, so I don't check it very often. You will get >> a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. It is not returning anything for me
CREATE PROCEDURE getOrders ( @sys_id varchar ) AS begin transaction select * from exportOrders where system_id = @sys_id and status = 0 for xml auto, xmldata, elements update orders set status = 10 where status = 0 commit GO eport orders is a view that contains pretty much all of the fields in the orders table plus some extra Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:uDHlrZL4FHA.3540@TK2MSFTNGP10.phx.gbl... > The result from the initial select statement will get returned. Try it: > > create procedure GetUnRetrieved AS > SET NOCOUNT ON > Select * from myTable where recieved = 0 > update myTable set received = 1 where received = 0 > > Run the procedure. See that the resultset is returned. > > Bob Barrows > Altman wrote: >> I have a stored procedure that I want to return everything that >> hasn't been marked as being retrieved yet. In the process I need to >> mark the data that it has been retrieved. >> >> Select * from myTable where recieved = 0 >> update myTable set received = 1 where received = 0 >> <now return what was selected in the first line> >> >> I can't do my select second because it wouldn't return anything. >> >> -- >> TIA >> >> Altman >> >> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:Oz35gQK4FHA.1396@TK2MSFTNGP12.phx.gbl... >>> Altman wrote: >>>> I am still fairly new to SQL server and I am trying to make a stored >>>> procedure that I can do the following: >>>> >>>> run select statement on a table >>>> update a table >>>> return the data that was selected in line 1. >>>> >>>> Update >>>> >>> You'll need to supply more information than that for a specific >>> solution. In >>> the meantime, look at this: >>> http://www.aspfaq.com/show.asp?id=2201 >>> >>> Bob Barrows >>> >>> -- >>> Microsoft MVP -- ASP/ASP.NET >>> Please reply to the newsgroup. The email account listed in my From >>> header is my spam trap, so I don't check it very often. You will get >>> a quicker response by posting to the newsgroup. > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > Nevermind something got messed up with my view and it wasn't running
correctly. Thanks Show quote "Altman" <NotGiven@SickOfSpam.com> wrote in message news:Oeuz3wL4FHA.2828@tk2msftngp13.phx.gbl... > It is not returning anything for me > > CREATE PROCEDURE getOrders > ( @sys_id varchar > ) > AS > begin transaction > select * from exportOrders where system_id = @sys_id and status = 0 for > xml auto, xmldata, elements > update orders set status = 10 where status = 0 > commit > GO > > eport orders is a view that contains pretty much all of the fields in the > orders table plus some extra > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:uDHlrZL4FHA.3540@TK2MSFTNGP10.phx.gbl... >> The result from the initial select statement will get returned. Try it: >> >> create procedure GetUnRetrieved AS >> SET NOCOUNT ON >> Select * from myTable where recieved = 0 >> update myTable set received = 1 where received = 0 >> >> Run the procedure. See that the resultset is returned. >> >> Bob Barrows >> Altman wrote: >>> I have a stored procedure that I want to return everything that >>> hasn't been marked as being retrieved yet. In the process I need to >>> mark the data that it has been retrieved. >>> >>> Select * from myTable where recieved = 0 >>> update myTable set received = 1 where received = 0 >>> <now return what was selected in the first line> >>> >>> I can't do my select second because it wouldn't return anything. >>> >>> -- >>> TIA >>> >>> Altman >>> >>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message >>> news:Oz35gQK4FHA.1396@TK2MSFTNGP12.phx.gbl... >>>> Altman wrote: >>>>> I am still fairly new to SQL server and I am trying to make a stored >>>>> procedure that I can do the following: >>>>> >>>>> run select statement on a table >>>>> update a table >>>>> return the data that was selected in line 1. >>>>> >>>>> Update >>>>> >>>> You'll need to supply more information than that for a specific >>>> solution. In >>>> the meantime, look at this: >>>> http://www.aspfaq.com/show.asp?id=2201 >>>> >>>> Bob Barrows >>>> >>>> -- >>>> Microsoft MVP -- ASP/ASP.NET >>>> Please reply to the newsgroup. The email account listed in my From >>>> header is my spam trap, so I don't check it very often. You will get >>>> a quicker response by posting to the newsgroup. >> >> -- >> Microsoft MVP -- ASP/ASP.NET >> Please reply to the newsgroup. The email account listed in my From >> header is my spam trap, so I don't check it very often. You will get a >> quicker response by posting to the newsgroup. >> >> > > >I am still fairly new to SQL server and I am trying to make a stored I'm confused. This last step is exactly what the first step does. Can you >procedure that I can do the following: > > run select statement on a table > update a table > return the data that was selected in line 1. give a better explanation of what you are trying to do? Why does the data being returned have to wait until after the update? Wouldn't it then, make more sense to say: UPDATE a table SELECT from a table ? I need to return the data that is not updated.
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23NRlARK4FHA.3444@tk2msftngp13.phx.gbl... > >I am still fairly new to SQL server and I am trying to make a stored > >procedure that I can do the following: >> >> run select statement on a table >> update a table >> return the data that was selected in line 1. > > I'm confused. This last step is exactly what the first step does. Can > you give a better explanation of what you are trying to do? Why does the > data being returned have to wait until after the update? Wouldn't it > then, make more sense to say: > > UPDATE a table > SELECT from a table > > ? > >I need to return the data that is not updated. You're going to have to provide more details because I still have no idea what you mean. Please read: http://www.aspfaq.com/5006  |
|||||||||||||||||||||||
Other interesting topics