Procedure Running Dynamic Query in SQL Server
Hello to everyone,
In this article, I will give you information about the procedure that runs dynamic queries in SQL Server.
I don’t know if there is a need for such a thing in SQL Server, but I tried to write it saying that maybe I will need it in the future and I wrote it. I wrote it as a procedure.
It takes three parameters. Database name, Table Name and Schema name.
You can make it even more dynamic if you wish. This may vary completely depending on the job you are doing.
Create the following procedure and run the codes. I had Northwind and AdventureWorks databases as an example and I tried it on these databases. You can try it on any database you want.
CREATE PROCEDURE GetDynamicQueryProcedure @DatabaseName VARCHAR(50), @TableName VARCHAR(50), @SchemaName VARCHAR(50) AS BEGIN DECLARE @SQLSentence VARCHAR(500); SET @SQLSentence = ' SELECT * FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ''; EXEC (@SQLSentence); END; --Use of the Procedure EXEC dbo.GetDynamicQueryProcedure @DatabaseName = 'Northwind', @TableName = 'Products', @SchemaName = 'dbo'; EXEC dbo.GetDynamicQueryProcedure @DatabaseName = 'AdventureWorks', @TableName = 'Department', @SchemaName = 'HumanResources';
Once you create and run the procedure, you will get the following results.
As you can see, there are two different databases and I brought results from two different databases on the same screen.
Good luck to everyone in business and life.