Can I view the syntax of a T-SQL proc-based SqlCommand stored procedure?

Question

I was wondering if anyone knows of a way to get the actual T-SQL that must be run by the SqlCommand object (with CommandType from StoredProcedure) before it executes ...

My script includes the optional saving of database operations to a file or MSMQ before the command is actually executed. My guess is that if you create SqlCommand as follows:

Using oCommand As New SqlCommand("sp_Foo") oCommand.CommandType = CommandType.StoredProcedure oCommand.Parameters.Add(New SqlParameter("@Param1", "value1")) oCommand.ExecuteNonQuery() End Using 

Terminates the execution of some T-SQL:

 EXEC sp_Foo @Param1 = 'value1' 

Is this assumption correct? If so, is it possible to somehow restore this actual T-SQL? My goal here is to take advantage of parsing, validation, etc. The use of the SqlCommand class, since I will use it anyway. Is it possible? Am I really wrong? Thanks in advance for any input!

Answer 1

Are you trying to record an SP call with its parameters in a friendly manner? Or do you want to save this text to run it as a script later?

I think you are out of luck in the second case, because, I do not think that SQLCommand calls SP with SQL when you use it in CommandType.StoredProcedure mode. If you look in the SQL Server profiler with the difference between CommandType.StoredProcedure with SP "sp_whatever" and CommandType.Text with "EXEC sp_whatever", this is a different interface - the RPC interface. I was always impressed that ADO.NET never did an SQL string in the first case. Therefore, you will never have to worry about injection at this point, because the parameters are always sent out of range - even if you have CommandType.Text with a parameterized request.

See the blog post .

If you are trying to perform logical operations, I think you could pass your SQLCommand object to some common object / method that you write, which extracts the command text and all parameters and puts them in a well-organized text form for logging.

Answer 2

SQL Profiler can be useful here. If you can run SP in SSMS when you run Profiler, you can configure it to view T-SQL executable commands.

Answer 3

Use SQL Profiler, connect to the server, use the TSQL_SPs template (after connecting to the server, you will get the "Trace Properties" field, the fourth line is the choice of your template).