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

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.

  • soumesh

    dynamic query

    • http://www.niteshluharuka.com Nitesh

      Soumesh – :)