How can we extract each query(Statement) in a procedure??

• mailatunni

  Valued Member

  Points: 73

  More actions

  March 26, 2012 at 10:52 am

  #270738

  Consider the scenario :

  DECLARE @var1=

  'Create proc example1

  AS

  begin

  Select * from table1

  select * from table2 t2 inner join

  table3 t3 on t2.id=t3.id

  print 'a'

  select 'b'

  end'

  while passing this procedure in to a procedure i need result like

  statements inside the procedure :

  1. Select * from table1

  2. select * from table2 t2 inner join table3 t3 on t2.id=t3.id

  3. print 'a'

  4. select 'b'

  ie i need to parse all the statements in a procedure while passing the procedure as a parameter to a procedure..

  Please help me...

  Thanks in Advance...

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 26, 2012 at 11:04 am

  #1464434

  In T-SQL that would be near-impossible beyond trivial statements. I would suggest a CLR procedure using what's shown here

  http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx (assuming those DLLs can be used within SQLCLR)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• sturner

  SSC-Insane

  Points: 22411

  More actions

  March 26, 2012 at 12:58 pm

  #1464476

  If you would delimit each distinct statement with a semi colon you could just execute the whole string and produce the results you want.

  The probability of survival is inversely proportional to the angle of arrival.