Stored Proc does not work upon execution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Stored Proc does not work upon execution

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2013-07-11 : 16:51:15

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-11 : 17:09:04

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 01:53:29

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources