Use @return_value to UPDATE a table

  • GSquared (9/1/2011)


    Can you modify the proc?

    Yes. I can modify it... I know it is a matter of also getting it to return a varchar instead of an int as well...

  • If you want to return a varchar value you have to use an output parameter or make your last statement in the procedure a select statement. SQL returns an integer from stored procedure executions. Read this article about is from BOL. It does a far better job explaining it than I can.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (9/12/2011)


    If you want to return a varchar value you have to use an output parameter or make your last statement in the procedure a select statement. SQL returns an integer from stored procedure executions. Read this article about is from BOL. It does a far better job explaining it than I can.

    I'm sorry if I am not understand this properly, but my last statement IS a select statement... it's a select case statement, but still a select, right?

    Could someone just please show me, using my code that I provided, how I would do this? I would GREATLY appreciate it.

    Thanks,

    ~D

  • We are here to help, not do your job for you. Not trying to sound snarky but you are obviously trying to do something that you don't understand. I can't possibly provide a complete, tested example using your code because we don't have all the details. All we have is a stored proc. We don't have the ddl for the tables in the proc. We don't know what table you are trying to update. We don't how to join the unknown tables together.

    Based on your original stored proc and my previous examples see if this might get you closer.

    create table #MyOutput

    (

    MyOutput varchar(25)

    )

    insert #MyOutput

    exec [COB] @ItemCode = 'Itemcode', @DelDoc = 'DelDoc'

    update MyOtherTableThatNeedsResultsFromMyProc

    set SomeColumn = MyOutput

    from #MyOutput m

    join MyOtherTableThatNeedsResultsFromMyProc mot on m.ProbablyNeedAColumnToJoinFromInTheOriginalProc = m.ExistingKeyField

    Make sure you understand the code and not just force it to work. At 3am when your phone rings because something failed it is going to be YOU that has to support it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I would try to execute the SP on SSMS and see what returns:

    EXEC [dbo].[COB] @ItemCode = N'S270740', @DelDoc = N'0625117GWJK'

    --------------------------------------------------------------
    DBA or SQL Programmer? Who Knows. :unsure:

  • What about the changes below?

    USE [TransactionalData]

    GO

    DECLARE @return_value int

    declare @itm_cd varchar (30)

    declare @del_doc_num varchar (30)

    declare @ItemCode varchar (30)

    declare @DelDoc varchar (30)

    set @itm_cd = (select itm_cd from temp_sales_order_cbo where itm_cd = 'S270740')

    set @del_doc_num = (select del_doc_num from temp_sales_order_cbo where itm_cd = 'S270740')

    EXEC @return_value = [dbo].[COB]

    @ItemCode = @itm_cd,

    @DelDoc = @del_doc_num

    update temp_sales_order_cbo

    set received_date = @return_value

    where itm_cd = @itm_cd

    and del_doc_num = @del_doc_num

  • Chris Souchik (9/15/2011)


    What about the changes below?

    USE [TransactionalData]

    GO

    DECLARE @return_value int

    declare @itm_cd varchar (30)

    declare @del_doc_num varchar (30)

    declare @ItemCode varchar (30)

    declare @DelDoc varchar (30)

    set @itm_cd = (select itm_cd from temp_sales_order_cbo where itm_cd = 'S270740')

    set @del_doc_num = (select del_doc_num from temp_sales_order_cbo where itm_cd = 'S270740')

    EXEC @return_value = [dbo].[COB]

    @ItemCode = @itm_cd,

    @DelDoc = @del_doc_num

    update temp_sales_order_cbo

    set received_date = @return_value

    where itm_cd = @itm_cd

    and del_doc_num = @del_doc_num

    @return_value is an int. More often than not the return value is going to be 0. Which will implicitly convert to datetime as 1/1/1900.

    Seems like it would be a lot easier to declare those other parameters as output instead of populating them outside the proc. Add those fields to the select that would be returned from the proc. Then you can use those values to update the other table. This also gives the advantage of being able to support more than 1 record in the output which the original stored proc handles.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you all, but we are rewriting it...

    Cheers,

    ~D

Viewing 8 posts - 16 through 22 (of 22 total)

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