EXECUTE SP from Select sp_name from table

• jorge.aguilar21

  SSC Journeyman

  Points: 76

  More actions

  July 21, 2015 at 2:09 pm

  #319945

  HI everybody

  I try to execute several SP in a secuence defined in a table

  i have almost 20 Stores and i need to run it in secuence identified by process

  for example y have the table

  PROCESSORDSP_NAME

  PROC110execute prc_insert @name, @lastname, @birthdate

  PROC120execute prc_flag @id, @deleted

  PROC130execute prc_update @id, @name, @lastname, @birthdate

  PROC210execute prc_flag @id, @deleted

  PROC220execute prc_update @name, @newname

  but i dont know how to run it, because with the combination i have several process

  i attach the example that did, i you have any idea i would appreciate

  Regards

  George

• Alan Burstein

  SSC Guru

  Points: 61152

  More actions

  July 21, 2015 at 5:42 pm

  #1813984

  There's nothing attached. Keeping in mind I don't know how values are being assigned to what your stored procs are ingesting you could do something like this:

  -- sample table using your data

  DECLARE @table TABLE (PROCESS varchar(10) NOT NULL, ORD int NOT NULL, SP_NAME varchar(200) NOT NULL);

  INSERT INTO @table

  SELECT 'PROC1',10,'execute prc_insert @name, @lastname, @birthdate' UNION ALL

  SELECT 'PROC1',20,'execute prc_flag @id, @deleted' UNION ALL

  SELECT 'PROC1',30,'execute prc_update @id, @name, @lastname, @birthdate' UNION ALL

  SELECT 'PROC2',10,'execute prc_flag @id, @deleted' UNION ALL

  SELECT 'PROC2',20,'execute prc_update @name, @newname';

  DECLARE @sql varchar(8000)='';

  SELECT @sql = @sql+SP_NAME+';'+CHAR(13)

  FROM @table

  ORDER BY SP_NAME, ORD;

  -- PRINT @sql -- included for troubleshooting

  EXEC(@sql);

  This is not a solution but should give you one idea of how you can begin to address this request. A

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001