How to write a parametrized query in management studio?

Question

From a client application I tyipically do:

select * from table where Name = :Parameter

and then before executing the query I do

:Parameter = 'John'

These parameters are not a Search&Replace but real parameters passed to the server. Since I need to test some of those queries in detail, how can I write the query in management studio?

I want to write the query with parameters and give a value to the parameter. How can this be done?

Update:

To remove confusion here I add info to better express myseld.

when I execute a normal query I see in sql server profiler

select * from table where Name = 'John'

while when I execute a parametrized query I see this:

exec sp_executesql N'select * from table 
where Name = @P1',N'@P1 varchar(8000)','John'

This is why I say it is not a search and replace.

The update does pretty much what I showed you in my Answer... — Adriaan Stander, Dec 10 '10 at 09:23
Ok you are right. So what I got is that when a client application uses parameters somehow it decalres variables with DELCARE. Very interesting, this helps in solving a problem I have. — UnDiUdin, Dec 10 '10 at 11:42
To explain you why I asked this question I redirect you to my final question: http://stackoverflow.com/questions/4408551/select-statment-performance-degradation-when-using-distinct-with-parameters — UnDiUdin, Dec 10 '10 at 11:53

score 43 · Accepted Answer · answered Dec 10 '10 at 08:53

43

How about something like

DECLARE @Parameter VARCHAR(20)
SET @Parameter = 'John'

SELECT *
FROM Table
WHERE Name = @Parameter

answered Dec 10 '10 at 08:53

Adriaan Stander

162,879
31
289
284

1

No this is "Serach and replace", in this way I am not telling sql_server that it is a parameter. – UnDiUdin Dec 10 '10 at 08:57
Please see my Update to the question so yuo get what i mean. – UnDiUdin Dec 10 '10 at 09:20
1

This answer can actually run with a different execution plan than the code as SQL Server is able to optimize the plan for the 'John' value – Eduardo Wada Jul 05 '18 at 15:50

Dennis T --Reinstate Monica-- · Answer 2 · 2019-10-21T17:57:40.223

8

Looks like you answered your own question when you updated it.

Rewriting here for future visitors who may be confused like I was. Below is how you write a parameterized query in SSMS. This helps if you want to analyze the execution plan for a parameterized query run by your code.

EXEC sp_executesql
N'

SELECT * FROM table_t 
WHERE first_name = @parameter

',
N'@parameter VARCHAR(8000)',
N'John'

edited Oct 21 '19 at 17:57

answered Sep 18 '18 at 18:56

Dennis T --Reinstate Monica--

739
6
19

score 0 · Answer 3 · edited Jul 13 '19 at 12:01

0

In addition to Adriaan Stander's answer, if you were using C# in your code for example, you should have ensured that you have passed the @parametervia encapsulating. Here is a code example:

using (SqlConnection conn = new SqlConnection(conString))
{
    conn.Open();

    SqlCommand cmd = new SqlCommand(userSql, conn);
    cmd.Parameters.AddWithValue("@parameter", parameter);


    conn.Close();

}

This code is intended to give you an idea and therefore isn't complete.

edited Jul 13 '19 at 12:01

double-beep

5,031
17
33
41

answered Nov 27 '17 at 11:53

Waheed Rafiq

460
1
6
17

This answer contradicts question tags. It's true but irrelevant if using SSMS. – Jirka Hanika Jan 10 '21 at 11:03

How to write a parametrized query in management studio?

3 Answers3

Linked