We help IT Professionals succeed at work.

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

blakmoon91
blakmoon91 asked
on
Medium Priority
182 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!
Comment
Watch Question

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

Author

Commented:
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?
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
CERTIFIED EXPERT
Commented:
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());

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
so3

Commented:
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

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.