A little while back I was doing a database migration. I was running on our development servers and once I was ready to deploy I needed to be able to change the server name from dev-sql1 to sql1 or whatever they were called. Now I was using linked servers, and calling them thus SELECT Count(*) FROM dev-sql1.dbo.Organisation So that meant either I would have to change the server names in umpteen million lines of code, or find another way. Thankfully there is another way and that is dynamic sql. Let us suppose we are calling a stored procedure, called GetNextId.

GetNextId takes one parameter in the form of an int, adds one to it and returns it as an output variable.


Create Procedure GetNextId @Id INT,@ReturnId INT OUTPUT AS BEGIN @ReturnId = @Id + 1 END

So if I am calling this procedure using dynamic sql I need to build a string to execute.

Declare @Id INT Declare @ReturnId INT Set @Id = 1 sp_executesql N'EXEC GetNextID @Id,N'@Id INT, @ReturnId INT' @ReturnId OUTPUT',@Id, @ReturnId OUTPUT


Hence now I could build my SQL statements using VARCHARS and execute them dynamically in this way. I stored the server name in a table and changed the name of the server when I deployed the migration database.