Sql Stored Procedure Validation In .net Front End (similar To Parse Option In Ssms)

My front end application has a textarea where the user can enter the stored procedure. I want to validate the SP, similar to the option 'Parse' in SSMS (Ctrl+F5). Do we have any Li

Solution 1:

In SQL Server, you can run a command which cause the next command not execute.

To do exactly what the parse button does, use

SET NOEXEC ON;

Any command after that, will not execute and only will be evaluated/compiled. To go back to normal use below

SET NOEXEC OFF;

So to simplify your application and not using SMO, use below strategy:

Surround your query with SET NOEXEC ON and SET NOEXEC OFF

-- string yourSqlStatement  = "SET NOEXEC ON; " + textarea.Text + " SET NOEXEC OFF;"
-- execute the yourSqlStatement using your programming language

SQL Server Reference

Solution 2:

You could use Microsoft.SqlServer.SqlManagementObjects or (SMO) in short

You would need to call

var options = new Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions
{
  BatchSeparator = "GO",
  CompatibilityLevel = Microsoft.SqlServer.Management.SqlParser.Common.DatabaseCompatibilityLevel.Version140 // depedning on the compatibilty level of your database.
};
var output = Microsoft.SqlServer.Management.SqlParser.Parser.Parser.Parse(inputString, options);

if(output.Errors.Any())
{
   // handle errors
}

This is the same code that is executed from SSMS when you execute the Parse command.

Why you shouldn't do this on a SQL server:

• SQL Injection
• Don't be dependent on a SQL Server
• Don't burn precious SQL Server CPU with things that are not ment for SQL server. (SQL server is an expensive part of your infrastructure running it in C# is way cheaper.)
• Use custom compatibility levels, independent of the SQL Server configuration.
• options.TransactSqlVersion could also be used if you want to parse for Azure SQL

Solution 3:

One more option, you can think of is, create the procedure as temporary stored procedure, which is only for current session. Once you close the connection, the session is lost.

msdn reference on CREATE PROCEDURE

Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Temporary names cannot be specified for CLR procedures.

The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

Usage scenario of Temporary stored procedure

CREATEPROCEDURE #TempProcedureName
ASBEGIN
...
END

You will come to know of all the errors, similar to permanent stored procedure. But, the procedure is not actually created in the database. If the enduser actually wants to create procedure, post successful check, the procedure can be created permanently.