SQL Server: How to write and execute a prepared statement?

Question

In MySQL, we can generate the prepared statement using PreparedStatement.

I want to achieve the same functionality in SQL script. How to create the prepared statement and how to execute it? Please provide an example for that.

Solution

I would suggest using sp_executesql over exec for most dynamic SQL. sp_executesql is similar to MySQL's EXECUTE...USING in that it can take parameters rather than only concatenated strings, thus giving you a good defense against SQL injection. sp_executesql also allows SQL Server to reuse the query plan for more efficient querying. Here's an example:

exec sp_executesql
    @statement = N'select * from sys.databases where name = @dbname or database_id = @dbid',
    @parameters = N'@dbname sysname, @dbid int',
    @dbname = N'master',
    @dbid = 1

Some more info and examples can be found here.