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
152 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

12 Experts available now in Live!

Get 1:1 Help Now