Solved

Stored procedure

Posted on 2009-04-08
10
203 Views
Last Modified: 2012-05-06
i have attached a stored procedure which i use in my project.


(i'm using this inside another stored procedure)
i need to get the value from
\\
	DECLARE @WaiterID int
	DECLARE @ServerDate datetime
	DECLARE @NewOrderID varchar(50)
 
	exec @NewMaxOrderID= dbo.SPGetMaxOrderID @ServerDate,@WaiterID
\\
 
how to get the value of @NewMaxOrderID

Open in new window

SP.txt
0
Comment
Question by:Rajeshk_cgm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24095634
change the procedure to this:
ALTER PROCEDURE [dbo].[SPGetMaxOrderID]
@Date Varchar(50)
, @WaiterID INT
, @MaxOrderID INT OUTPUT
AS
BEGIN
	DECLARE @OrderID Varchar(50)
	DECLARE @PerviousOrderID Int
	-- DECLARE @MaxOrderID Varchar(50) 
	SET @PerviousOrderID = 0
 
	SET @MaxOrderID = CASE WHEN DATEPART(DD, @Date) <= 9 THEN '0' + CAST(DATEPART(DD, @Date) AS VARCHAR) ELSE CAST(DATEPART(DD, @Date) AS VARCHAR) END + 
			   CASE WHEN DATEPART(MM, @Date) <= 9 THEN '0' + CAST(DATEPART(MM, @Date) AS VARCHAR) ELSE CAST(DATEPART(MM, @Date) AS VARCHAR) END + 
			  RIGHT (CAST(DATEPART(YYYY,@Date)  AS VARCHAR), 4) + '-' + CAST(@WaiterID AS VARCHAR)+ '-0'
 
	DECLARE GetOrderID CURSOR FOR
		SELECT DISTINCT OrderID FROM Sales_ProductDetails
		WHERE (OrderID LIKE CASE WHEN DATEPART(DD, @Date) <= 9 THEN '0' + CAST(DATEPART(DD, @Date) AS VARCHAR) ELSE CAST(DATEPART(DD, @Date) AS VARCHAR) END + 
			   CASE WHEN DATEPART(MM, @Date) <= 9 THEN '0' + CAST(DATEPART(MM, @Date) AS VARCHAR) ELSE CAST(DATEPART(MM, @Date) AS VARCHAR) END + 
			   RIGHT(CAST(DATEPART(YYYY, @Date)  AS VARCHAR), 4) + '-' + CAST(@WaiterID AS VARCHAR) + '-%')
	OPEN GetOrderID
	FETCH NEXT FROM GetOrderID INTO @OrderID
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF(@PerviousOrderID < dbo.FNGetSplitOrderID(@OrderID))
		BEGIN
			SET @PerviousOrderID = dbo.FNGetSplitOrderID(@OrderID)
			SET @MaxOrderID = @OrderID
		END
		FETCH NEXT FROM GetOrderID INTO @OrderID
	END
	CLOSE GetOrderID
	DEALLOCATE GetOrderID
 
	SELECT @MaxOrderID OrderID
END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24095635
and your calling code to this:
        DECLARE @WaiterID int
        DECLARE @ServerDate datetime
        DECLARE @NewOrderID varchar(50)
 
        exec dbo.SPGetMaxOrderID @ServerDate,@WaiterID, @NewMaxOrderID OUTPUT

Open in new window

0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24095651
in the SP you declare an out perameter and set a value for it in the SP.
read
http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
sample of out parameter
CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID
   FROM HumanResources.Employee
   WHERE EmployeeID = @employeeID
END
0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095656
the datatype should be varchar . because i need to reuse the @NewOrderID .
the value ll be like '08042009-7000-01-0002'
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095702
*sorry the above line should be as

the datatype should be varchar . because i need to reuse the @NewMaxOrderID.
the value ll be like '08042009-7000-01-0002'
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24095785
then, check out my suggested modifications.
it will be exactly what you need to do (output parameter)
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095884
if i use your suggection then i'm getting conversion error

'Conversion failed when converting the varchar value '08042009-7000-0' to data type int.'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24095924
sorry...

ALTER PROCEDURE [dbo].[SPGetMaxOrderID]
@Date Varchar(50)
, @WaiterID INT
, @MaxOrderID VARCHAR(30) OUTPUT

Open in new window

0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095967
thank u angelIII... but after this i need to do another operation in the same procedure ...
the id may be returned as '08042009-7000-01-0002' or '08042009-7001-0'  

how to split this using the '-' & i need to take the value '01' or '0' from it?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24096071
I don't know what you mean?
please try to understand how the parameter passing works, as from there, you can continue to develop ....
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question