query for this sp - 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

query for this sp

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-03-29 : 06:58:17

kindly help on the query for this sp

CREATE PROCEDURE [dbo].[usp_pkg_audit_execute] (@execute_type char(3))
AS
BEGIN
If @execute_type ='PRE' then
INSERT INTO ssis_audit(package_name,status,start_time) VALUES(@package_name, '1',GETDATE())
SELECT @ssis_audit_id=CAST(SCOPE_IDENTITY() AS INT)
else if @execute_type ='POS' then
INSERT INTO ssis_audit(package_name,status,start_time) VALUES(@package_name, '2',GETDATE())
end if
END

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 07:12:57

What is the problem ? Where is the declaration for @package_name

I've fixed some typos......

1) Pass @package_name VARCHAR(10) as Input Param

2) If @execute_type ='PRE'
BEGIN
INSERT INTO ssis_audit(package_name,status,start_time) VALUES(@package_name, '1',GETDATE())
SELECT @ssis_audit_id=CAST(SCOPE_IDENTITY() AS INT)
END
else if @execute_type ='POS'
BEGIN
INSERT INTO ssis_audit(package_name,status,start_time) VALUES(@package_name, '2',GETDATE())
end
END

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-03-29 : 07:47:17

Thanks bandi

quote:
Originally posted by bandi

What is the problem ? Where is the declaration for @package_name

I've fixed some typos......

1) Pass @package_name VARCHAR(10) as Input Param

2) If @execute_type ='PRE'
BEGIN
INSERT INTO ssis_audit(package_name,status,start_time) VALUES(@package_name, '1',GETDATE())
SELECT @ssis_audit_id=CAST(SCOPE_IDENTITY() AS INT)
END
else if @execute_type ='POS'
BEGIN
INSERT INTO ssis_audit(package_name,status,start_time) VALUES(@package_name, '2',GETDATE())
end
END

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 08:05:14

quote:
Originally posted by shanmugaraj

Thanks bandi

Declare @ssis variable inside Procedure...

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-29 : 13:48:47

you dont need if ...else construct. just below would do

INSERT INTO ssis_audit(package_name,status,start_time)
SELECT @package_name,CASE @execute_type WHEN 'PRE' THEN '1' WHEN 'POS' THEN '2' END,GETDATE()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources