Simple Complex Stored Procedure
-
Hi,
i need help on this. i have a stored procedure which works fine.what i want to know is to is there a way i can run the execute statement in the stored procedure before the Set Statement in the stored procedure.
CREATE PROCEDURE [dbo].[Load_Audit_Excel]
(@Return_Code int Output)
AS
BEGIN
--set @Return_Code=0
--interfering with SELECT statements.
SET NOCOUNT ON;
EXEC msdb.dbo.sp_start_job @job_name='Audit'
END
begin
set @Return_Code=(SELECT count(*) FROM [Header$])
Select @Return_Code
end
-
I have a question, why?
-
Because i feel that its setting the values first and then executing the job .....but in my case i want to execute the job first and then check the condition and set value so how do i do that ???
-
Actually, two questions, after looking closer at the procedure, are you sure it works? Looks like the set and select for Result_Code are outside the procedure to me.
-
Yes but it still works
-
Humor me, try this and see if it does what you want:
CREATE PROCEDURE [dbo].[Load_Audit_Excel]
(@Return_Code int Output)
AS
BEGIN
SET NOCOUNT ON;
SELECT @Return_Code = count(*) FROM [Header$]
EXEC msdb.dbo.sp_start_job @job_name='Audit'
END
thanks
-
Thanks a lot, but for testing i changed the parameter as local varaible
now my proc looks like this
CREATE PROCEDURE [dbo].[Load_Audit_Excel]
AS
Declare @Return_Code int
BEGIN
SET NOCOUNT ON;
SELECT @Return_Code = count(*) FROM [Header$]
EXEC msdb.dbo.sp_start_job @job_name='Audit'
END
but when i execute the proc i want to see the value in @Return_Code but its only saying Job Started Successfully, that is the reason why i have set command used in my previous proc
wheni do set first and then do select it gives me the value in @Return_Code
Can u please help me out with this
Thanks
-
No. Do the proc the way I had it. the execute statement looks something like this then (read BOL to be sure of the syntax).
declare @myvalue int
exec dbo.Load_Audi_Excel @myvalue = @Return_Code
select @myvalue -- this is the value returned from the sproc.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply