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

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.

Margheritamargi

If you would have asked me instead of the other one answering this question #11983001, I could have told you that, also. I think, when you use a site extensively it's also a matter of politeness to learn how to use it. – Fossick

While there are certainly issues w/ the OP's participation on SO, is it really "difficult to tell what is being asked here?" – Larcenous

Answer 1

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.

Larcenous