Use of stored procedure SP_EXECUTE with examples in sql server

SP_EXECUTE is system procedure which is used to execute prepared query.  To use this stored procedure in sql server we must have to now handler of prepared query. I am explaining it by an example:

Creating a sample table and inserting some records into it:

CREATE TABLE tblStudent(

     ntStudentID BIGINT PRIMARY KEY IDENTITY,

     vcName VARCHAR(100),

     ntAge INTEGER

)

INSERT INTO tblStudent

VALUES('Scott',31),('Greg',42),('Alain',32)

We want to execute this query:

SELECT *

FROM tblStudent

WHERE ntStudentID = 2 AND vcName = 'Greg'

--Declaring the hanlder

DECLARE @Handler AS INTEGER

EXECUTE SYS.SP_PREPARE

     @Handler OUTPUT,

     N'@ntntStudentID AS BIGINT,

     @vcName AS VARCHAR(100)', --Parameters of query delimited by comma

     N'SELECT *

     FROM tblStudent

     WHERE ntStudentID = @ntntStudentID

          AND vcName = @vcName' --Query

--Executing

EXECUTE SYS.SP_EXECUTE @Handler, 2, 'Greg'

Output:

ntStudentID	vcName	ntAge
2	Greg	42

What is clustered index in sql server
What is non clustered index in sql server
When sql server is able to use indexes and when not
How to use database engine tuning advisor to get recommendations in sql server
Effect of collation in indexing in sql server.
Write sql queries in set based approach sql server
Sql server best practices for stored procedure