Solved

Can you guys tell me if this is returning the correct data from a Stored Procedure? Expected value returned, success with 0 value returned

Posted on 2006-11-11
6
153 Views
Last Modified: 2011-10-03
I have executed a query in against the Db, we are having a issue of data not being returned correctly and I am trying to isolate it. The query is listed below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[usp_i_admin_insert_catitem]
@cid int,
@catid int,
@iid int
AS
begin tran
      declare @ins_error int
      Select @ins_error = 0
      insert into t_catitem (iid, catid)
      values (@iid, @catid)
      select @ins_error = @@error
      
      IF @ins_error = 0
      BEGIN
            COMMIT TRAN
            select 'success' as status
      END
      ELSE
      BEGIN
               ROLLBACK TRAN
            select 'error' as status
      END

If I execute the stored procedure against the DB, it queries it against the db table t_catitem, however I have to pass a null value for cid as the table only has ciid, catid, and iid, so I pass a null value for cid, i put in 9 for catid and 1248 for iid (which I have verified in the DB as being there and correct) and I get a "return success" and a value "0" I would assume I should get a value back, can you guys help me figure this out, I am working on a big project because our programmer fell out and I don't have a strong background in asp .net. Your help would be greatly appreciated!
0
Comment
Question by:blakmoon91
6 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17923391
Since your Stored Procedure neither Returns a Value, nor has a Parameter declared as either InOu or Out, how can you expect to get ANY value back from the Stored Procedure?

AW
0
 

Author Comment

by:blakmoon91
ID: 17923403
Arthur,
Appreciate the follow up, I have very little knowledge as explained before, how would you suggest going about fixing this? Can you explain further about this?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17923556
what value would you like to return, as this SP is doing a simple insert - that results in no specific value as a result.

In addition, since the SP never makes use of the first parameter, two questions/observations come immediately to mind:

1) Why does your Stored procedure have three input parameters, when only two of them are used?

2) since the first paramater is not used in any way, it makes ABSOLUTELY NO DIFFERENCE whatsoever, what value you give it.  cid = NULL, cid = 9, cid = 123456, cid = 999999999  all will result in exactly the same final outcome.  why worry about what value you pass as CID?

AW
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 375 total points
ID: 17924187
i dont think there is any prob with ur proc if u intented to return succes when the proc succeed and error when it fails then u are on the right track

all u got to do is


SqlConnection Con = new SqlConnection(ur Connection string);
            SqlCommand FCommand = new SqlCommand("usp_i_admin_insert_catitem", Con);
            Con.Open();
            FCommand.CommandType = CommandType.StoredProcedure;

//add ur parameters
            object obj = FCommand.ExecuteScalar();
            Con.Close();
            if (obj != null)
                MessageBox.Show(obj.ToString());
0
 
LVL 6

Expert Comment

by:so3
ID: 17924757
in your procedure why do you use
Select @ins_error = 0
select @ins_error = @@error

instead of
set@ins_error = 0
set @ins_error = @@error

and also as Arthur_Wood  said if you don't use the @cid  remove it from the parameter list
0
 
LVL 21

Expert Comment

by:mastoo
ID: 17925399
If you get an @@ERROR <> 0, your c# code is going to get a SqlException raised.  You need to have the if statement like you do in your proc to handle the commit/rollback, but returning the "success" or "error" won't be very meaningful since your code already needs to be able to catch the sqlexception.  Same kind of thing with returning an error status - it doesn't add much with your example.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now