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

3 Answers

How about something like

DECLARE @Parameter VARCHAR(20)
SET @Parameter = 'John'
SELECT *
FROM Table
WHERE Name = @Parameter

3

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.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy