sql query iterating through query

Question

I wrote a stored procedure which returns contract status for anauthor. If author doesn't exist i'll return -900. So, this is what i did

  CREATE PROCEDURE AUthorContract
    @user varchar(10)
       AS
          IF(@user NOT IN(select Au_id from Authors))
           BEGIn
               RETURN -900
          END
      ELSE
       BEGIN
         select contract from from Authors where Au_id = @user
       END

       GO

However, when I give a valid author id as input, it returns -900 and if i give some id it goes into else and just returns 0 . So, I reversed them and entered correct id, but it still just returns 0 and not the contract value. Can u please help me out the mistake i have been doing. The DB is pubs.

KM. · Accepted Answer · 2011-11-10 16:37:56Z

stored procedure can "return" three types of things:

int return value, via RETURN command
output parameter value, via parameter list: @YourPartameter anydatatype OUTPUT
result set: SELECT * FROM YourTable

your procedure does not have a RETURN command for normal exit, so it defaults to a value of zero. You need to check the result set in your application if the return value is zero.

try this to RETURN the contact value:

CREATE PROCEDURE AUthorContract
    @user varchar(10)
       AS
       DECLARE @contract int
          IF(@user NOT IN(select Au_id from Authors))
           BEGIn
               RETURN -900
          END
      ELSE
       BEGIN
         select @contract=contract from from Authors where Au_id = @user
       END

       RETURN @contract

       GO

try this to use an OUTPUT parameter:

CREATE PROCEDURE AUthorContract
    @user varchar(10), @contract varchar(30) OUTPUT
       AS
          IF(@user NOT IN(select Au_id from Authors))
           BEGIn
               RETURN -900
          END
      ELSE
       BEGIN
         select @contract=contract from from Authors where Au_id = @user
       END

       RETURN 0

       GO

Here is the result set, with an explicit return:

CREATE PROCEDURE AUthorContract
    @user varchar(10)
       AS
          IF(@user NOT IN(select Au_id from Authors))
           BEGIn
               RETURN -900
          END
      ELSE
       BEGIN
         select contract from from Authors where Au_id = @user
       END

       RETURN 0

       GO

SQLMenace · Accepted Answer · 2011-11-10 16:33:17Z

1

one way

CREATE PROCEDURE AUthorContract
    @user varchar(10)
       AS
          IF NOT EXISTS(select 1 from Authors WHERE Au_id = @user )
           BEGIN
               SELECT -900 as contract   -- this will return a result set
          END
      ELSE
       BEGIN
         select contract  from Authors where Au_id = @user
       END

       GO

but a return status is not the same as a result set if the proc is successful you will get 0, with return you can only use integers, use an output parameter if you need to return varchar

answered Nov 10, 2011 at 16:33

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Comments

user596075 · Accepted Answer · 2011-11-10 16:32:07Z

0

You're not returning the contract value. You are just selecting a scalar value to be outputed. The stored proc is returning 0 because it completed execution without a return value of something other than that.

Also, not sure if it is a typo, but you have an extra from in your select clause.

answered Nov 10, 2011 at 16:32

user596075

Comments

4b0 · Accepted Answer · 2011-11-10 17:08:14Z

0

try:

IF(@user NOT IN(select Au_id from Authors))
           BEGIn
               declare @a int
               set @a= -900
               return @a
               --print @a

          END

edited Nov 10, 2011 at 17:08

answered Nov 10, 2011 at 16:49

4b0

22.4k30 gold badges97 silver badges143 bronze badges

Collectives™ on Stack Overflow

sql query iterating through query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related