0

I Have made a stored proc and i would like it to return true of false depending if the SQL statement is valid.

Create PROCEDURE [dbo].[CheckSQLStatement]
@SQL varchar(8000)
AS

SET NOEXEC ON
Exec @SQL 
SET NOEXEC OFF

This i what i have so far, when the statement is valid, in the SQL Server management studio, the results are Command(s) completed successfully. and if the statement i invalid it returns an error message, EG Incorrect syntax near 'Selec'.

Firstly how can i return a Value True of false if statement is valid.

Secondly how can i return the invalid error message?

I was thinking something like:

Create PROCEDURE [dbo].[CheckSQLStatement]
@SQL varchar(8000),
@IsValid bit OUTPUT,
@text NVARCHAR(1000)OUTPUT
AS

 SET NOEXEC ON
 Exec @SQL 
 SET NOEXEC OFF

Select @Isvalid
Select @Text

But i don't know how to set thos values?

Is there a better method?

Any help would be appreciated, Thanks in advance.

Pomster
  • 14,567
  • 55
  • 128
  • 204
  • Your question is tagged with SQLServer 2008. If by any chance you can switch to SQLServer 2012, you could use the so-called "Microsoft SQL Server 2012 Transact-SQL Language Service" and implement an extended stored proc to check your statement. See here for more info: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.aspx – David Brabant Jul 12 '12 at 09:04

1 Answers1

1

I was working on this problem, too, and my solution involved

   BEGIN TRANSACTION
   BEGIN TRY
       ... execute sql
       ROLLBACK TRANSACTION
   END TRY
   BEGIN CATCH
      WHILE @@TRANCOUNT > 0
         ROLLBACK

      ... check ERROR_*() functions
   END CATCH

Full TSQL code is on my blog, and there's an app for that.

devio
  • 36,858
  • 7
  • 80
  • 143