September 12, 2011 at 12:16 pm
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...
September 12, 2011 at 12:29 pm
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/
September 14, 2011 at 10:55 am
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
September 14, 2011 at 11:09 am
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/
September 14, 2011 at 3:53 pm
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:
September 15, 2011 at 12:11 pm
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
September 15, 2011 at 12:51 pm
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/
September 16, 2011 at 12:54 pm
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