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
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
No comments:
Post a Comment