October 13, 2011 at 3:14 pm
Hello All,
Given this procedure:
CREATE PROCEDURE uspSub
@ID INT
AS
SELECT getdate()
How can I call this procedure using sp_executesql and determine whether the called procedure executed successfully.
DECLARE @String nvarchar(4000)
DECLARE @params nvarchar(4000)
DECLARE@Procedure varchar (150)
DECLARE @ID INT
--I would like to set this flag to Y if procedure uspSub ran successfully
DECLARE @SuccessFlag char(1)
SET @ID =1
SET @Procedure = 'uspSub'
SET @String = 'EXEC ' + @Procedure + ' ' + CONVERT(varchar(9),@ID)
SET @params = '@ID int'
EXECUTE sp_executesql @String, @params, @ID
Thanks if you can help...
October 13, 2011 at 3:31 pm
IF @@ERROR was 0 (zero) it could be asusmed all was well.
However, can I ask a different question, why do you want to go about building the command dynamically?
CEWII
October 13, 2011 at 3:39 pm
>>However, can I ask a different question, why do you want to go about building the command dynamically?
I could be calling multiple procedures here in (excuse me for saying this) a cursor. So given a set of stored procedures I want to execute a subset of them based on scheduling considerations and other business logic.
So the related question is, given a dynamic set of procedures can I call them without a cursor or dynamic SQL?
My bottom line with cursors is never go there with data manipulation but I have used them for calling DTEXEC with dynamic parameters and in this case for calling procedures.
October 13, 2011 at 3:46 pm
--CREATE PROCEDURE uspSub
--@ID INT
--AS
--SELECT getdate()
--CREATE PROCEDURE uspSub2
--@ID INT
--AS
--SELECT getdate()
DECLARE @String nvarchar(4000)
DECLARE @params nvarchar(4000)
DECLARE @Procedure varchar (150)
DECLARE @ID INT
--I would like to set this flag to Y if procedure uspSub ran successfully
DECLARE @SuccessFlag char(1)
SET @ID =1
SET @Procedure = 'uspSub'
SET @String = 'EXEC ' + @Procedure + ' ' + CONVERT(varchar(9),@ID)
SET @params = '@ID int'
DECLARE My_Cursor CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR
--Here's where I select multiple procedures using my logic
SELECT 'uspSub' UNION SELECT 'uspSub2'
--open the cursor
OPEN My_Cursor
--insert the first record in the cursor
FETCH NEXT FROM My_Cursor
INTO
@Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Procedure
SET @String = 'EXEC ' + @Procedure + ' ' + CONVERT(varchar(9),@ID)
SET @params = '@ID int'
EXECUTE sp_executesql @String, @params, @ID
--retrieve the next record in the cursor
FETCH NEXT FROM My_Cursor
INTO
@Procedure
END
--close and deallocate the cursor
CLOSE My_Cursor
DEALLOCATE My_Cursor
October 13, 2011 at 3:49 pm
declare @retval int;
exec @retval = dbo.my_storedproc;
if @retval = 0
print 'execution successful';
else
print 'execution failed';
Look up EXECUTE statement in Books Online.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply