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 Programming
 Optional Output Parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 04/01/2013 :  03:12:01  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
I need to have Optional Output parameter with passing int based on case. need help on declaring @ssis_audit_id variable for below SP.

CREATE PROCEDURE [dbo].[usp_pkg_audit_execute] 
				(
				@ssis_audit_id INT OUTPUT ,
				@package_name VARCHAR(200) = NULL,
				@execute_type char(3)
				)
AS
	If @execute_type ='PRE' 
	Begin
		INSERT INTO ssis_audit	( package_name) VALUES (@package_name)
        	SELECT @ssis_audit_id=CAST(SCOPE_IDENTITY() AS INT)
	END		
	ELSE IF @execute_type ='POS' 
	BEGIN 
		UPDATE ssis_audit SET  end_time=GETDATE() WHERE ssis_audit_id=@ssis_audit_id
	END 
END


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/01/2013 :  03:35:04  Show Profile  Reply with Quote
--Run the following cases for above Procedure...
--Let us know where you got issue
DECLARE @ssis_audit_id INT
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'
GO
DECLARE @ssis_audit_id INT = AuditIdValue (Ex: 3)
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'POS'
GO
DECLARE @ssis_audit_id INT = AuditIdValue (Ex: 3)
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'

I meant to say @ssis_audit_id INT OUTPUT is working as optional output param..
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 04/01/2013 :  05:08:23  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
DECLARE @ssis_audit_id INT = AuditIdValue (Ex: 3)
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'

In this the AuditIdValue is not yet arrived. i am getting it thru sp and then later i pass the parameter for 'POS'

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/01/2013 :  05:40:49  Show Profile  Reply with Quote
By selecting that output param, we can use later
DECLARE @ssis_audit_id INT
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'
SELECT @ssis_audit_id
GO
DECLARE @ssis_audit_id INT = 1
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'POS'
SELECT @ssis_audit_id
GO
DECLARE @ssis_audit_id INT
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'
SELECT @ssis_audit_id
GO
DECLARE @ssis_audit_id INT = 2
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'
SELECT @ssis_audit_id;

SELECT * FROM ssis_audit;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  05:51:35  Show Profile  Reply with Quote
As far as I understand you dont require taking anything out for update (POS)

so this should be what you're looking at

DECLARE @ssis_audit_id INT 
EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'
GO
DECLARE @ssis_audit_id INT = 1
EXEC usp_pkg_audit_execute @ssis_audit_id, NULL, 'POS'
GO
DECLARE @ssis_audit_id INT = 2
EXEC usp_pkg_audit_execute @ssis_audit_id, NULL, 'PRE'
GO

...


------------------------------------------------------------------------------------------------------
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.05 seconds. Powered By: Snitz Forums 2000