October 23, 2012 at 11:42 am
Hello,
Here's my issue, I need to execute a procedure which needs a parameter, that parameter needs to be retrieved dynamically then another command must be built with the proc + paramvalue end executed.
Here's my code, I'm not far from succeeding but I can't get the outputvariable, it retrieves NULL although the output from the exec sp_executesql shows the right value. Then I'll need to execute the @procname with the value retrieved as a parameter.
Thanks for any help,
P.
DECLARE SETVAL_C CURSOR FOR
select name from sys.procedures where name like '%_SETVAL';
declare @refid_col varchar (20);
declare @tabname varchar(30);
declare @procname varchar(30);
declare @sqlcommand nvarchar(200);
declare @max int;
DECLARE @outputVariable INT;
OPEN SETVAL_C;
FETCH NEXT FROM SETVAL_C INTO @procname;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @outputVariable INT;
select @tabname=substring(@procname,1,master.dbo.f_firstposition(@procname,'_',1)-1)
select @refid_col=substring(@procname,master.dbo.f_firstposition(@procname,'_',1)+1,len(@procname)-master.dbo.f_firstposition(@procname,'_',2)-1)
set @sqlcommand='Select max('+ @refid_col +') FROM ' + @tabname +' '
exec sp_executesql @sqlcommand , N'@output INT OUTPUT', @outputVariable OUTPUT
select @outputVariable
then...not coded yet but:
exec @procname @outputvariable
FETCH NEXT FROM SETVAL_C INTO @procname;
END;
CLOSE SETVAL_C;
DEALLOCATE SETVAL_C;
GO
October 23, 2012 at 12:13 pm
Found the solution myself, here it is if you need it:
DECLARE SETVAL_C CURSOR FOR
select name from sys.procedures where name like '%_SETVAL';
declare @refid_col varchar (20);
declare @tabname varchar(30);
declare @procname varchar(30);
declare @sqlcommand nvarchar(200);
declare @sqlcommand2 nvarchar(200);
DECLARE @retval varchar(10);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @outputVariable INT;
OPEN SETVAL_C;
FETCH NEXT FROM SETVAL_C INTO @procname;
WHILE @@FETCH_STATUS = 0
BEGIN
select @tabname=substring(@procname,1,master.dbo.f_firstposition(@procname,'_',1)-1)
select @refid_col=substring(@procname,master.dbo.f_firstposition(@procname,'_',1)+1,len(@procname)-master.dbo.f_firstposition(@procname,'_',2)-1)
select @sqlcommand='Select @retvalOUT = max('+ @refid_col +') FROM ' + @tabname +' '
SET @ParmDefinition = N'@retvalOUT varchar OUTPUT';
exec sp_executesql @sqlcommand , @ParmDefinition, @retvalOUT=@retval OUTPUT;
select @retval
select @sqlcommand2= 'exec ' + @procname + ' ' + @retval
print @sqlcommand2
exec sp_executesql @sqlcommand2
FETCH NEXT FROM SETVAL_C INTO @procname;
END;
CLOSE SETVAL_C;
DEALLOCATE SETVAL_C;
GO
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply