SQL Server: way to see final query with filled parameters

Is there a way to see final query which is passed to SQL Server database from my C# app ?

For ex I got query:

SELECT * FROM mytable WHERE x = @yyyy;

This creates and SQLCommand object

SqlCommand cmd = new SqlCommand("SELECT * FROM mytable WHERE x = @yyyy");

Plus I need to开发者_开发问答 pass parameter:

cmd.Parameters.Add("@yyyy","MyValue");

What I want to see (in debug in C# or somewhere in SQL Server Management Studio) is this:

SELECT * FROM mytable WHERE x = MyValue

Where can I find such query ?!

Best regards

Where can I find such query ?!

You can't. Such a query never exists. The values are not substituted into the SQL.

I think actually sp_executesql is called, and this function accepts the parameters separately from the SQL. You can check this using SQL Profiler to see the actual SQL.

Update:

ORDER BY @descOrAsc

Your problem is that parameters can only be used in certain places where expressions are allowed. DESC is not an expression - it is a reserved word. You cannot use a parameter containing the string "DESC" instead of writing the keyword DESC in the query.

Also, you haven't specified which column to order by.

You can run the SQL Server Profiler and see all the queries that get executed, to see whats happening (and copy paste these into the Sql Server Management Studio to do tests etc)

I would expect the query to be passed to SQL Server with the parameters. There should be no need for anything to ever create a full SQL-only query. It makes no sense to do so, as it just means more conversions for either the client, the server or both. On the server side, the query processor is going to want to parse the query into clauses with values - if the command can pass those values directly, where's the advantage on converting them into the SQL statement, only to have the server parse them into separate values again?

1.You can use SQL Profiler. (here you can see all process) 2.You can write all your queries to SQL Server table. And then you can always get queries from this table.