return output parameter in sql server

Question

Here condition is true then it return true otherwise return false

ALTER PROCEDURE spAppliedJobOrnot

    --Here job is already applied then it return true otherwise return false
    @check bit OUTPUT,
    @UserId numeric,
    @JobId numeric

AS
BEGIN

    SET NOCOUNT ON;
    if exists (SELECT 1 FROM StudentInbox_Activities WHERE JobId=@JobId and StudentId=@UserId and 
    JobAppliedDate is not null)
        BEGIN
        SET @check=1  --The job is applied so check=true
        END
    ELSE
    BEGIN
        SET @check=0;
    END
END
GO

Here how to get the @check value

Adriaan Stander · Accepted Answer · 2014-04-08 06:26:02Z

3

When using OUTPUT params, you need to return the value in the calling statement.

Have a lok at this example

SQL Fiddle DEMO

Procedure to do whatever

CREATE PROCEDURE FOO(@BAR INT OUTPUT)
AS
BEGIN
    SET @BAR = -1
END;

And then calling statement

DECLARE @TADA INT = 0
EXEC FOO @TADA OUTPUT
SELECT @TADA

answered Apr 8, 2014 at 6:26

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BAdmin · Accepted Answer · 2014-04-08 06:39:23Z

2

Since you have @check as output parameter, you can get this value from your application too. If it is in C#, you can use something like the following,

SqlCommand cmd = new SqlCommand("SP_NAME");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", param1);
cmd.Parameters.AddWithValue("@param2", param2);

cmd.Parameters.Add("@check", SqlDbType.Int);
cmd.Parameters["@check"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery()
return Convert.ToInt32(cmd.Parameters["@check"].Value);

edited Apr 8, 2014 at 6:39

answered Apr 8, 2014 at 6:24

BAdmin

9371 gold badge11 silver badges19 bronze badges

Comments

Sharad · Accepted Answer · 2014-04-08 07:09:19Z

-1

Add the following code after your if- end statement, before procedure end statement

select @check

Then run the procedure.

Maybe this link might be helpful for your problem about ASP.Net.

edited Apr 8, 2014 at 7:09

answered Apr 8, 2014 at 6:27

Sharad

8138 silver badges15 bronze badges

4 Comments

prashanthi kontemukkala Over a year ago

I'm using asp.net how can i get @check value in asp.net

Steve Pettifer Over a year ago

That's not how output parameters work. See astander's answer for the SQL side and BAdmin's for the client side.

Sharad Over a year ago

@StevePettifer If we just want to check output, then it is fine (as I thought). Then again question was asked about using the output with asp.net. So the link. I've modified the tag to include asp.net.

Steve Pettifer Over a year ago

It's not though - doing select @check in the procedure would return a single value in a single record, not a value in an output parameter. whilst the question wasn't all that explicit it was clear that this was what the OP was asking as his proc was functionally correct. Your answer does not fulfill any of the requirements.

Collectives™ on Stack Overflow

return output parameter in sql server

3 Answers 3

SQL Fiddle DEMO

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related