sql stored procedure - with in cursor get @@identity value for insert and use that again


I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that  line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after

any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit.  (#tblSalesOrders is a temporary table which containsset of  records from orginal table )

DECLARE
@soNo1 INT

 

DECLARE @CursorOrders CURSOR

SET @CursorOrders = CURSOR FAST_FORWARD

FOR

select fldSoNo

from #tblSalesOrders

declare @newSONO1 int

OPEN @CursorOrders

FETCH NEXT FROM @CursorOrders

INTO @soNo1

WHILE @@FETCH_STATUS = 0

BEGIN

----for each salesorder insert to salesorderline

insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;

 

 

set @newSONO1=SCOPE_IDENTITY;

-------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert --------------------------

SELECT @intErrorCode = @@ERROR

IF (@intErrorCode <> 0) GOTO PROBLEM

FETCH NEXT FROM @CursorOrders

INTO @soNo1

END

CLOSE @CursorOrders

DEALLOCATE @CursorOrders

0
Pathiya007
6/11/2008 12:02:05 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
959 Views

Similar Articles

[PageSpeed] 3
Get it on Google Play
Get it on Apple App Store

i didn;t get you entirely. but are you need to solve errors in this proc.

then try this

DECLARE @soNo1 INT
 
declare @intErrorCode int
DECLARE @CursorOrders CURSOR
SET @CursorOrders = CURSOR FAST_FORWARD

FOR
select fldSoNo

from #tblSalesOrders
declare @newSONO1 int

OPEN @CursorOrders
FETCH NEXT FROM @CursorOrders

INTO @soNo1
WHILE @@FETCH_STATUS = 0

BEGIN

----for each salesorder insert to salesorderline

insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus)
select fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus
from #tblSalesOrders where fldSoNo =@soNo1

 

 

set @newSONO1= @@identity
-------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert --------------------------

SELECT @intErrorCode = @@ERROR

IF (@intErrorCode <> 0) GOTO PROBLEM
FETCH NEXT FROM @CursorOrders

INTO @soNo1
END

CLOSE @CursorOrders
DEALLOCATE @CursorOrders
PROBLEM:
 select 'error'


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
6/11/2008 12:36:22 PM

syntax error in brackets for select  

insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;

any way thanks

 

0
Pathiya007
6/11/2008 1:00:06 PM
Reply:

Similar Artilces:

How to get comma seperated value from sql 2000 database using store procedure
Hi I have two table in database. I m using sql 2000 database..one is user and another is useraccess level table..Useraccess table i m storing userid and accesslever. User Table userid username userfirstname.......1        abc           steve2        xyz           paula3        pqr           Haian   Useraccess Table Userid &n...

How to get comma seperated value from sql 2000 database using store procedure
How to get comma seperated value from sql 2000 database using store procedure  Hi I have two table in database. I m using sql 2000 database..one is user and another is useraccess level table..Useraccess table i m storing userid and accesslever. User Table userid username userfirstname.......1        abc           steve2        xyz           paula3        pqr    ...

Get identity after insert using default using Text Sql Commands
I cannot for the life of me get the Identity after inserting a record into a table using the Table Adapater.I am using Text SQL Commands.I have configured the table adapter to "Refresh the Data Table" and the Insert SQL Command:INSERT INTO [Item] ([name], [description]) VALUES (@name, @description);SELECT itemId, name, description FROM Item WHERE (itemId = SCOPE_IDENTITY())The Visual Studio generated Insert Method used the following to execute the InsertCommand                int returnValue = this.Adapter.InsertComma...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Return Value when using CLR stored procedure. (Edit: For use in SQL side paging)
I have a stored procedure that I put together using the CLR.  The basic idea is the have a query that can accept any number of values.  I havent included the portion of the code that handles smalldatetime searches and I havent yet finished the logic to handle sql data type constraints but I have been working on paging using SQL's Row_Number().  The code below works but I havent been able to come up with a way to find out either how many pages the result set has or how many total rows in the result set.  I thought there might be a way to use a procedure's Return Va...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...

How do I get the Identity field value after a sql insert so the value can be included in the email sent upon submit?
I have  a form that gathers a bunch of data, inserts into a sql database via a stored procedure, and then emails a user some details.  How can I include the value of the unique identifier for the row just inserted into sql in the email?    Here's my code behind.. and I don't know how/where to query the db to get the value and insert it... Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click InsertConflictSearch.InsertParameters("SearchStatusID").DefaultValue = "1"InsertConflictSearch.Insert...

How do I use the return value from a SQL stored procedure?
I want to be able to use the returned number to set a Label accordingly.Something like this:if the return value == 1 then Label1.Text = "Correct" else Label1.Text = "Wrong" What is the correct syntax for this?  I couldn't find this answer. If you've just got one return parameter, I usually stick with ExecuteScalar. This will execute a stored procedure and return an object, which is taken from the first row and first column of whatever the stored procedure returns.So at the end of the stored procedure, after doing what you need to do, add a select stat...

Value that is returned if an insert is not performed from a SQL stored procedure.
I am using the following stored procedure to insert a value into the database. I am new to stored procedures so I need help. Basically this stored procedurewill only insert a value for "CustomerName" if it is not a duplicate value. So I noticed in My C# when I call this stored procedure, a negative one "-1"is returned if the insert is not performed because of a duplicate value. Is that correct? Should I be getting back a negative one "-1" ? You seeI thought that a Zero "0" would be returned if the insert was not performed.Not a negative one? SET ANS...

Get value from drop down list, use in sql insert
I'm trying to get the selected values of two drop down lists and a query string to use to find a matching record in the database. It doesn't like the way I've set the colour and size.  I need the ItemID from the sql query to be used in an insert statement immediately after this and writing to an invisible textbox is the only way I could come up with that may work. I haven't got it to go far enough to find out if it will put the results in a text box.I've used this way to get value from a textbox - Dim Name As TextBox = CType(ProductDetailsForm.FindControl("NameT...

Inserting Information into an SQL Database using Stored Procedures
Hello, I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address).  I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers. I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...

Web resources about - sql stored procedure - with in cursor get @@identity value for insert and use that again - asp.net.sql-datasource

Identity of the first male Muslim - Wikipedia, the free encyclopedia
There is some disagreement among Muslims , and among historians of Islam , as to the identity of the first male convert to Islam after Muhammad ...

'It's time for Aboriginal identity and story to be embraced by all Australians'
Some places in Perth were named after governors or other European elites, wives and family members or places in Britain. Frankly, many names ...

Case of mistaken identity: No, Iyad El-Baghdadi is not the leader of Daesh
Case of mistaken identity: No, Iyad El-Baghdadi is not the leader of Daesh

AOL's identity crisis: The company may ditch the 'AOL' brand
... with a bunch of different subsidiaries within it . AOL already has the different companies within it, it just needs to figure out the identity ...

Internal police records point to the identity of the officer who fatally shot Bettie Jones and Quintonio ...
The mother of five and the distraught teenager were shot and killed by police officer Robert Rialmo, according to a Reader analysis of internal ...

Lucasfilm Confirms Star Wars: The Force Awakens Stormtrooper TR-8R's Identity
Lucasfilm teased that despite the shot from Chewbacca's almost impossibly powerful bowcaster [...]

Elite Chicago HS Plans Seminars On ‘Racial Identity’ For MLK Day
Topics include 'Western Bias in Science' and 'Whitewashing in Cinema'

'The Flash' Season 2 Spoilers: Will Barry Reveal His Secret identity To Patty In Episode 10?
... (Shantel VanSanten) that he's the Scarlett Speedster. Patty (Shantel VanSanten) may finally find out Barry's (Grant Gustin) superhero identity ...

IRS Drops Proposed Charity Regulations After Identity Theft Risk Outcry
Facing 38,000 comments, the Internal Revenue Service withdraws its proposed alternate charitable substantiation regulations.

The 13 Most Nonsensical Theories About The Identity of Supreme Leader Snoke
Without a doubt, Supreme Leader Snoke has become one of the most fascinating and controversial figures in Star Wars: The Force Awakens . Played ...

Resources last updated: 1/18/2016 7:52:24 PM