How to use an OUTPUT parameter and check if a record exists

  • I have this stored proc and what i want to do is check is a record already exists and if it exist return an output parameter

    and output it to an aspx page.

    If i use the @status varchar(200) OUTPUT

    and run the stored proc i get an error :expects parameter '@status', which was not supplied.

    If i uncomment it out and just use print like so:

    print 'already exists'

    I do get a desired result.

    Any ideas what i'm missing..

    thanks a lot

    -------------------------------------------

    alter procedure [dbo].[checkComms]

    @CommID int,

    @username varchar(50),

    @status varchar(200) OUTPUT

    as

    if exists

    (SELECT 1

    FROM [Northwind].[dbo].[Comms]

    where storeCommID=@CommID and [UserName] = @username)

    begin

    set @status ='Already Exists'

    print @status

    --print 'already exists'

    return

    end

    Insert into [storeComms] (CommID, UserName)

    Values (@storeCommID,@UserName)

    --print @status

    go

  • There are a few things to check on the asp side as from what I can see you proc looks fine, other that if something doesn't exist then a null is returned.

    So make sure you can accept nulls in the asp side.

    Make sure you have declared @status as an Ouput Param on your asp side.

    Actually could you show us the code for your proc call?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Patrick Ige (4/15/2009)


    I have this stored proc and what i want to do is check is a record already exists and if it exist return an output parameter

    and output it to an aspx page.

    If i use the @status varchar(200) OUTPUT

    and run the stored proc i get an error :expects parameter '@status', which was not supplied.

    If i uncomment it out and just use print like so:

    print 'already exists'

    I do get a desired result.

    Any ideas what i'm missing..

    thanks a lot

    -------------------------------------------

    alter procedure [dbo].[checkComms]

    @CommID int,

    @username varchar(50),

    @status varchar(200) OUTPUT

    as

    if exists

    (SELECT 1

    FROM [Northwind].[dbo].[Comms]

    where storeCommID=@CommID and [UserName] = @username)

    begin

    set @status ='Already Exists'

    print @status

    --print 'already exists'

    return

    end

    Insert into [storeComms] (CommID, UserName)

    Values (@storeCommID,@UserName)

    --print @status

    go

    alter procedure [dbo].[checkComms]

    @CommID int,

    @username varchar(50),

    @status varchar(200) OUTPUT

    as

    if exists

    (SELECT 1

    FROM [Northwind].[dbo].[Comms]

    where storeCommID=@CommID and [UserName] = @username)

    begin

    set @status ='Already Exists'

    --print 'already exists'

    end

    Insert into [storeComms] (CommID, UserName)

    Values (@storeCommID,@UserName)

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • I think the OP wanted the return there so that no records are inserted...

    otherwise good move taking it out but that shouldn't change the nature of the output param should it?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Thanks kshitij

    But i can't see any difference in the code i posted with the one you posted?

    Am i blind ๐Ÿ™‚

  • the return was removed from within the if statement!

    Did that fix your problem?

    If it did I would be interested to know why?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Patrick Ige (4/15/2009)


    Thanks kshitij

    But i can't see any difference in the code i posted with the one you posted?

    Am i blind ๐Ÿ™‚

    I removed the return statement from the stored procedure. now the stored procedure is perfect for the OUTPUT variable. what you have to do is: on the Data Access layer fetch this output variable as:

    command.parameter["OUTPUT variable"].value;

    and you will get the expected result.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • why does .NET fall over when you have a return in your code?

    Thanks for the solution, just hoping for an explanation as well

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • krayknot (4/15/2009)


    I removed the return statement from the stored procedure.

    Why? Now the stored procedure doesn't work - it will insert a row whether it's required or not.

    krayknot (4/15/2009)


    now the stored procedure is perfect for the OUTPUT variable.

    What was wrong with it before?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I'm with Chris...

    Oh another thing is to maybe SET NOCOUNT ON at the beginning of the proc.

    This sounds wierd but I have seen some ASP and PHP code fall over when it things it's getting multiple result sets.

    Worth a try, but like I said please post the ASP code otherwise we can't tell why...

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Christopher Stobbs (4/15/2009)


    why does .NET fall over when you have a return in your code?

    Thanks for the solution, just hoping for an explanation as well

    If you are using any variable(s) as an OUTPUT varialbe in stored procedure, it means that variable will carry the output of the stored procedure. The same thing you can also do with return but if you will use return then how you will get the result inside the .NET code as there is no OUTPUT varialbe in the stored procedure. Thus, here there is no requirement of the return command.

    Then where to use return??

    If you want to fetch a dataset from the stored procedure, use return as

    Select from table

    return

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • krayknot (4/15/2009)


    If you are using any variable(s) as an OUTPUT varialbe in stored procedure, it means that variable will carry the output of the stored procedure.

    Are you saying that you can only capture one type of output from a stored procedure? What if you want an OUTPUT variable, a result set, and the value returned from the RETURN statement, all from the same call? Are you saying that .NET can't handle this?

    krayknot (4/15/2009)


    The same thing you can also do with return but if you will use return then how you will get the result inside the .NET code as there is no OUTPUT varialbe in the stored procedure. Thus, here there is no requirement of the return command.

    The OP's code has an OUTPUT parameter, his problem is most likely in calling the sproc from his app. He's not interested in the value returned by the RETURN statement. He does however require RETURN in his sproc for it to work correctly.

    krayknot (4/15/2009)


    Then where to use return??

    If you want to fetch a dataset from the stored procedure, use return as

    Select from table

    return

    So... .NET can't get a dataset from a stored procedure unless it's got a RETURN statement after the SELECT?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris (4/15/2009)


    krayknot (4/15/2009)


    If you are using any variable(s) as an OUTPUT varialbe in stored procedure, it means that variable will carry the output of the stored procedure.

    Are you saying that you can only capture one type of output from a stored procedure? What if you want an OUTPUT variable, a result set, and the value returned from the RETURN statement, all from the same call? Are you saying that .NET can't handle this?

    In my experience it can, though I'm not a .NET expert.

    So... .NET can't get a dataset from a stored procedure unless it's got a RETURN statement after the SELECT?

    That it definitely can do. I never have a RETURN in my procs and so far I've had no hassles with them in .NET. Of course, I may be doing something wrong.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • kryknot is wrong.

    SQL will always return RETURN value. You have no control over it. The only thing you can do is to change the default value of 0 to anything else. And the RETURN keyword is not required to retrieve anything from the database.

    On the original problem. You must declare a SQLParamater in your .NET code for your SP output parameter. In SQL the OUTPUT parameter is not output only, If it is declared without a default value then the SQL will treat it as an required parameter and will give you an error if you do not supply it. You must be careful to clear it at the begining of your SQL code because if for whatever reason your .NET code assigns a value to it before you call the SP it will be passed into your SP.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • This is how I would write the procedure:

    alter procedure [dbo].[checkComms]

    @CommID int,

    @username varchar(50),

    @status varchar(200) OUTPUT

    as

    begin

    set nocount on;

    set @status = 'Does Not Exist';

    if exists (select 1

    from

    [Northwind].[dbo].[Comms]

    where

    storeCommID=@CommID

    and [UserName] = @username)

    begin

    set @status = 'Already Exists';

    end

    else begin

    Insert into [storeComms] (CommID, UserName)

    Values (@storeCommID,@UserName)

    end

    end

    What I haven't seen, and was requested, is the SQL being used to call the procedure.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply