CODEWITHSUNDEEP
sql-serverparametersssms
UnDiUdin
UnDiUdin

Reputation: 15394

How to write a parametrized query in management studio?

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.

Upvotes: 32

Views: 98599

Answers (3)

Dennis T --Reinstate Monica--
Dennis T --Reinstate Monica--

Reputation: 769

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'

Upvotes: 11

Waheed Rafiq
Waheed Rafiq

Reputation: 482

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.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

How about something like

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

SELECT *
FROM Table
WHERE Name = @Parameter

Upvotes: 48

Related Questions