T-SQL to call the SQL Parse "function"

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  November 2, 2012 at 9:12 am

  #1555530

  GilaMonster (11/2/2012)

  ---

  Not even with SET NOEXEC ON?

  And THANKS a ton Gail! This got us on the right track at least. 🙂

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  November 2, 2012 at 9:48 am

  #1555563

  Jason Selburg (11/2/2012)

  ---

  Eugene Elutin (11/2/2012)

  ---

  Try this:

  DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

  SET @SQL = 'SET NOEXEC ON;

  ' + @SQL + '

  SET NOEXEC OFF;'

  BEGIN TRY

  EXEC (@SQL)

  END TRY

  BEGIN CATCH

  PRINT 'Error here: ' + ERROR_MESSAGE()

  END CATCH

  SET NOEXEC OFF;

  Almost there!!!!!

  It seems multi-line statements/batches seem to have problems and are returned as invalid. :hehe:

  Do you mean multi-line statements/batches separated with "GO"?

  If yes, you should be aware that "GO" is just a batch separator but it is not part of T-SQL.

  So even one line valid "batch" with "GO" is not valid. Try this:

  SELECT 1;

  GO

  And this:

  EXEC ('

  SELECT 1;

  GO')

  And see the difference...

  One of the ways to make it work for you could be separating all you batches by "GO" into separate SQL's statements to validate.

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 2, 2012 at 9:56 am

  #1555567

  SET PARSEONLY ON/OFF works as well.

  i'm having some fun trying to get a CLR to work as a proof of concept; i'll post it as soon as it returns results the way i'd expect.

  DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

  SET @SQL = 'SET PARSEONLY ON;

  ' + @SQL + '

  SET PARSEONLY OFF;'

  BEGIN TRY

  EXEC (@SQL)

  END TRY

  BEGIN CATCH

  PRINT 'Error here: ' + ERROR_MESSAGE()

  END CATCH

  SET NOEXEC OFF;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  November 2, 2012 at 3:35 pm

  #1555674

  Lowell (11/2/2012)

  ---

  SET PARSEONLY ON/OFF works as well.

  i'm having some fun trying to get a CLR to work as a proof of concept; i'll post it as soon as it returns results the way i'd expect.

  DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

  SET @SQL = 'SET PARSEONLY ON;

  ' + @SQL + '

  SET PARSEONLY OFF;'

  BEGIN TRY

  EXEC (@SQL)

  END TRY

  BEGIN CATCH

  PRINT 'Error here: ' + ERROR_MESSAGE()

  END CATCH

  SET NOEXEC OFF;

  I don't believe that will work if the @SQL created a stored procedure like Jason wanted though. You'd probably get an error something about the CREATE PROCEDURE statement needing to be the first statement in the batch.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)