How to Execute a Query Stored in a variable in Stored Procedure

November 29th, 2012 | Posted by Nitesh Luharuka in General | SQL Server

Friends,

Today, I was working with SQL Server and found an interesting situation where I wanted to store my query in a variable in stored procedure and after preparing the query based on parameters, execute the query at the last. I came up with an interesting solution for this. Let us consider the below stored procedure that displays all records from table Student from a stored procedure.

CREATE PROCEDURE sp_TestVariableQuery
AS
BEGIN
        SELECT * FROM Student
END

Now, the below stored procedure returns theĀ  same output as the above stored procedure, but it will use a variable to store the query first and will then execute the query present in the variable.

CREATE PROCEDURE sp_TestVariableQuery
AS
BEGIN
	DECLARE @Query nvarchar(200)
	SET @Query= 'SELECT * FROM Student'
	EXECUTE (@Query)
END

Both the stored procedure returns the same output when executed. Hope you find this tip useful while working. Let me know your views/thoughts via comments section.

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

2 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Support us!

If you like this site please help and make click on any of these buttons!

×