Optional Output Parameter - SQL Server Forums

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 Programming

Optional Output Parameter

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-01 : 03:12:01

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

2242 Posts

Posted - 2013-04-01 : 03:35:04

--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..

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-01 : 05:08:23

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 05:40:49

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 05:51:35

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources