SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Stored Proc does not work upon execution
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dzabor
Posting Yak Master

USA
135 Posts

Posted - 07/11/2013 :  16:51:15  Show Profile  Send dzabor an AOL message  Reply with Quote
I have a stored procedure that will run correctly on our dev and stage box, however on production it will not work. They all have the same version of SQL server 2008.

I can run as a script on production, but when called from another stored procedure it will not execute.

Can someone look at the script below and tell me how I can alter the scipt to get the @DaysRemaining working. Maybe it is just the way the script is written?


USE [database]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[Subscription]
(
@OrderNumber FLOAT ,
@ProductCode VARCHAR(31) ,
@iMISid VARCHAR(10)
)
AS
--get remaining time on prior subscription
DECLARE @DaysRemaining INT

SELECT TOP ( 1 )
@DaysRemaining = DATEDIFF(dd, GETDATE(), Date)
FROM History
WHERE History_TYPE = 'SUB'
AND Date > GETDATE()
AND Code = @ProductCode
AND ID = @iMISid
ORDER BY EFFECTIVE_DATE DESC



DECLARE @maxseqn INT

SELECT @maxseqn = MAX(seqn) + 1
FROM History

UPDATE counter
SET last_value = @maxseqn
WHERE counter_name = 'History'

INSERT INTO [dbo].[History_rak]
( [SEQN] ,
[BT_ID] ,
[History_type] ,
[transaction_date] ,
[Code] ,
[ORDER_DATE] ,
[UF_1] ,
[OTHER_DESC] ,
[DESCRIPTION] ,
[Date] ,
[daysremaining] ,
[gl_substitue]
)
SELECT 1 AS SEQN ,
BT_ID ,
'SUB' AS History_type ,
GETDATE() AS transaction_date ,
p.Code ,
o.ORDER_DATE ,
CONVERT(INT, o.ORDER_NUMBER) AS UF_1 ,
p.OTHER_DESC ,
gt.DESCRIPTION ,
DATEADD(DD,
CONVERT(INT, gt.SUBSTITUTE)
+ ISNULL(@DaysRemaining, 0), o.ORDER_DATE) AS Date ,
@DaysRemaining AS daysremaining ,
CONVERT(INT, gt.SUBSTITUTE) AS gl_substitue
FROM Orders o
JOIN Order_Lines ol ON o.ORDER_NUMBER = ol.ORDER_NUMBER
JOIN Product p ON ol.Code = p.Code
JOIN Gen_Tables gt ON gt.CODE = p.Code
WHERE gt.TABLE_NAME = 'SUB'
AND o.ORDER_NUMBER = @OrderNumber

INSERT INTO History
( SEQN ,
id ,
History_type ,
transaction_date ,
Code ,
effective_date ,
UF_1 ,
Description ,
Other_code ,
Date
)
SELECT @maxseqn ,
BT_ID ,
'SUB' ,
GETDATE() ,
p.Code ,
o.ORDER_DATE ,
CONVERT(INT, o.ORDER_NUMBER) ,
p.OTHER_DESC ,
gt.DESCRIPTION ,
o.ORDER_DATE + CONVERT(INT, gt.SUBSTITUTE)
+ ISNULL(@DaysRemaining, 0)
FROM Orders o
JOIN Order_Lines ol ON o.ORDER_NUMBER = ol.ORDER_NUMBER
JOIN Product p ON ol.Code = p.Code
JOIN Gen_Tables gt ON gt.CODE = p.Code
WHERE gt.TABLE_NAME = 'SUB'
AND o.ORDER_NUMBER = @OrderNumber



SELECT '1' AS 'Status' ,
'' AS 'Message' ,
1 AS 'isWarning'

GO

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 07/11/2013 :  17:09:04  Show Profile  Reply with Quote
If it works in dev and staging that have the same version of SQL Server as the production, I don't think we can figure out the problem just by looking at the code. When you say "it will not work on production", what did you mean? Does it throw an error? If so what is the error message? Or does it complete successfuly but does not produce any results?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  01:53:29  Show Profile  Reply with Quote
the logic currently used for @daysremaining

ie

SELECT TOP ( 1 )
@DaysRemaining = DATEDIFF(dd, GETDATE(), Date)
FROM History
WHERE History_TYPE = 'SUB'
AND Date > GETDATE()
AND Code = @ProductCode
AND ID = @iMISid
ORDER BY EFFECTIVE_DATE DESC

will just take first record staisfying the given conditions from select.
so it may be that value got from production is different from what you're getting in dev/Staging

Also were you using same set of parameter values in production?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000