Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

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

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
blakmoon91
Asked:
blakmoon91
1 Solution
 
Arthur_WoodCommented:
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
 
blakmoon91Author 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?
0
 
Arthur_WoodCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Gautham JanardhanCommented:
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
 
so3Commented:
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
 
mastooCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now