Output Parameters in SP not running as expected

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

SQL Server 2000 Forums

SQL Server Development (2000)

Output Parameters in SP not running as expected

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-09-02 : 17:03:53

Hello board I have a SP that returns 3 parameters , I can print these parameters once the sp is run but I cant use them within a if statement why is this please ?

All Parameters are returned correctly but it seems only the first if statement runs ( If @match = 1 ) when @match = 1 , else none of the underlying If statemnts work although all the correct values are in the parameters specified

The following piece of code is my calling code :

USE master
DECLARE @istat varchar(20)
DECLARE @ostat varchar(20)
DECLARE @match int

EXEC lrdatawarehouse.dbo.insertitemsales
@itemsalestatus = @istat output,
@offersalestatus = @ostat output,
@nomatch = @match output

--***************************************************************************************************************************************************
If @match = 1
BEGIN
EXEC xp_sendmail @recipients = 'Jim@xxx.co.uk ',
@query = ' SELECT Orphanisale as [Orphan Itemsales],
OrphanOfferSale as [Orphan Offersales]
FROM lrdatawarehouse.dbo.NoMatches ',
@copy_recipients = 'Matthew@xxx.co.uk ; Tim@xxx.co.uk ',
@subject = 'Unmatched Itemsales Stores vs Offersales Stores Files',
@message = '
There is orphaned stores in one of todays itemsales or offersales
files excluding store 189,
***********************************************************************
These orpahans will be taken out and will need to be imported
manually when they have a legitimate relation :
***********************************************************************
The following information is available :

' ,

@separator = ' ',
@attach_results = 'false', @width = 80

END

--***************************************************************************************************************************************************
If @istat = 'MissingIsaleNormal' and @ostat = 'OfferSuccess'
BEGIN
EXEC xp_sendmail @recipients = 'Jim@xxx.co.uk ',
@query = ' SELECT Storenumber as [Store Isale File Missing or contains No data], DateInserted as [Date and Time Import Attempt] FROM lrdatawarehouse.dbo.itemsales_stores_input_error

SELECT StoreNumber as [Successful Item Sale Imports] , DateImport as [Date and Time Imported] FROM lrdatawarehouse.dbo.itemSalesImportStoreSuccess ',
@copy_recipients = 'Matthew@xxx.co.uk ; Tim@xxx.co.uk ',
@subject = 'Missing Isale File on todays Datawarehouse import',
@message = '
There is missing isale files from todays import into the Datawarehouse,
***********************************************************************
This import procedure will run again at 17:00 and then import all available files
into the cube and ignore the ones that are not available :
***********************************************************************
The following information is available :

' ,
@separator = ' ',
@attach_results = 'false', @width = 80

begin
WAITFOR TIME '17:00'
EXEC lrdatawarehouse.dbo.insertitemsales
@itemsalestatus = @istat output,
@offersalestatus = @ostat output

EXEC sp_updateJCTest
end

END

--***************************************************************************************************************************************************
IF @istat = 'MissingIsaleSunday' and @ostat = 'OfferSuccess'
BEGIN
EXEC xp_sendmail @recipients = 'Jim@xxx.co.uk',
@query = ' SELECT Storenumber as [Store Isale File Missing or contains No data], DateInserted as [Date and Time Import Attempt] FROM lrdatawarehouse.dbo.itemsales_stores_input_error

SELECT StoreNumber as [Successful Item Sale Imports] , DateImport as [Date and Time Imported] FROM lrdatawarehouse.dbo.itemSalesImportStoreSuccess ',
@copy_recipients = 'Matthew@xxx.co.uk ; Tim@xxx.co.uk ',
@subject = 'Missing Isale File on Mondays Datawarehouse import',
@message = '
There is missing isale files from todays import into the Datawarehouse,
***********************************************************************
This import procedure will run again at 17:00 and then import all available files
into the cube and ignore the ones that are not available :
***********************************************************************
The following information is available :

' ,
@separator = ' ',
@attach_results = 'false', @width = 80

begin
WAITFOR TIME '17:00'
EXEC lrdatawarehouse.dbo.insertitemsales
@itemsalestatus = @istat output,
@offersalestatus = @ostat output

EXEC sp_updateJCTest
end

END

--

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-03 : 02:31:53

[code]
WAITFOR TIME '17:00'
[/code]
Though the code must be executing but, due to delay u r not able to see the output of it..??

Just put the Print Statement in the other 2 conditions, and check whether they are satisfying the conditions.

Chirag

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-09-03 : 11:02:51

missing a silly... begin... end... thank you for pointing that out Chirag muchly appreciatted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources