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.
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 THANKSSHANMUGARAJnshanmugaraj@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 issueDECLARE @ssis_audit_id INT EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'GODECLARE @ssis_audit_id INT = AuditIdValue (Ex: 3)EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'POS'GODECLARE @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'THANKSSHANMUGARAJnshanmugaraj@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 laterDECLARE @ssis_audit_id INT EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'SELECT @ssis_audit_idGODECLARE @ssis_audit_id INT = 1EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'POS'SELECT @ssis_audit_idGODECLARE @ssis_audit_id INT EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'SELECT @ssis_audit_idGODECLARE @ssis_audit_id INT = 2EXEC 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 atDECLARE @ssis_audit_id INT EXEC usp_pkg_audit_execute @ssis_audit_id OUT, NULL, 'PRE'GODECLARE @ssis_audit_id INT = 1EXEC usp_pkg_audit_execute @ssis_audit_id, NULL, 'POS'GODECLARE @ssis_audit_id INT = 2EXEC usp_pkg_audit_execute @ssis_audit_id, NULL, 'PRE'GO... ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|