?
Solved

Returned value from stored procedure - where is it coming from?

Posted on 2008-06-12
4
Medium Priority
?
201 Views
Last Modified: 2010-04-23
Hi

I'm updating a website and I'm looking at code that I didn't write :)

I see code that inserts data into a table in a database.
It's fairly straightforward.
The table 'news' has a primary key which is an identity - 'news_rid'

In the Visual Basic.Net website I see this line.

_newsid = Convert.ToInt32(SqlHelper.ExecuteScalar(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"), "usp_News_Add", contentid, _homepageflag, UserName))
               
I assumed that this inserted a row and assigned the newly created 'news_rid' into _newsid

Is it right to think that?

If so how does the stored procedure return the news_rid?
I don't see an output parameter in the stored procedure (below)

Thanks!
ALTER PROCEDURE [dbo].[usp_News_Add]
(
	@contentid int,
	@homepageflag bit,
	@updatedby nvarchar(55)
)
AS
 
	DECLARE @Error int
	DECLARE @lineID int
	DECLARE @ptrval binary(16)	
 
	declare @updatedid int
	select @updatedid= users_rid from users where users_login = @updatedby
	SET @Error = @@ERROR
 
            IF @Error != 0 GOTO ERROR_HANDLER
 
             BEGIN TRANSACTION 
 
	Insert INTO news
		(
		content_rid,
		news_homepage,
		UpdatedBy,
		deleted_flag
	)
	Values(
		@contentid,
		@homepageflag,
		@updatedid,
		0
	)
	
            COMMIT TRANSACTION           
 
SELECT @@identity AS ID  
 
ERROR_HANDLER:
 
            IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
 
            RETURN @Error

Open in new window

0
Comment
Question by:hmcgeehan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Author Comment

by:hmcgeehan
ID: 21767113
Just to help I did this


                HttpContext.Current.Response.Write("[" & _newsid & "]<br>")
                _newsid = Convert.ToInt32(SqlHelper.ExecuteScalar(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"), "usp_News_Add", contentid, _homepageflag, UserName))
                HttpContext.Current.Response.Write("[" & _newsid & "]<br>")
                HttpContext.Current.Response.End()

and got this ..

[0]
[9]

so it is assigning the value I just don't understand how :)
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21767137
you will get value as table which contains last inserted row's ID

0
 
LVL 12

Accepted Solution

by:
renjurdevan earned 100 total points
ID: 21767144
ExecuteScalar method will return @@identity value! ie, last inserted row's identity value!
0
 

Author Closing Comment

by:hmcgeehan
ID: 31466462
Easy when you know how!

Thanks so much!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question