CODEWITHSUNDEEP
sqlstored-procedures
Sayamima
Sayamima

Reputation: 225

sql query iterating through query

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.

Upvotes: 1

Views: 211

Answers (4)

4b0
4b0

Reputation: 22323

try:

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

          END

Upvotes: 0

KM.
KM.

Reputation: 103587

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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 1

user596075
user596075

Reputation:

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.

Upvotes: 0

Related Questions