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.