Jan 16, 2013

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

No comments:

Post a Comment