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.

  • soumesh

    dynamic query

    • http://www.niteshluharuka.com Nitesh

      Soumesh – :)

Support us!

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

×