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...
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.
3 Answers
How about something like
DECLARE @Parameter VARCHAR(20)
SET @Parameter = 'John'
SELECT *
FROM Table
WHERE Name = @Parameter
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'
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
