SQL SERVER – Input and Output Parameter for Dynamic SQL – Simple Example

July 23, 2020

Today we are going to a very simple example of the Input and Output Parameter for Dynamic SQL. I have been working on SQL Server for almost 20 years now and then I end up in the situation where I do not remember syntax and do a search on the internet. Recently I ended up in a similar situation while helping my client during Comprehensive Database Performance Health Check.

SQL SERVER - Input and Output Parameter for Dynamic SQL - Simple Example OutputParameter-800x271

Today we will be looking at three examples. Each example will be representing the dynamic SQL and Input, Output and Input/Output parameters together.

Dynamic SQL and Input Parameter

-- Example of Input Parameter
DECLARE @inputParam INT = 3176;
DECLARE @sql NVARCHAR(MAX) = N'SELECT TOP 1 [OrderID]
FROM [WideWorldImporters].[Sales].[Orders]
WHERE ContactPersonID = @id_in';
EXEC sys.sp_executesql @sql, N'@id_in INT', @inputParam;
GO

Dynamic SQL and Output Parameter

-- Example of Output Parameter
DECLARE @outputParam INT;
DECLARE @sql NVARCHAR(MAX) = N'SELECT TOP 1 @id_out = [OrderID]
FROM [WideWorldImporters].[Sales].[Orders]
WHERE ContactPersonID = 3176';
EXEC sys.sp_executesql @sql, N'@id_out INT OUT', @outputParam OUT;
SELECT @outputParam OutParamValue
GO

Dynamic SQL and Input/Output Parameter

-- Example of Input/Output Parameter
DECLARE @inputParam INT = 3176;
DECLARE @outputParam INT;
DECLARE @sql NVARCHAR(MAX) = N'SELECT TOP 1 @id_out = [OrderID]
FROM [WideWorldImporters].[Sales].[Orders]
WHERE ContactPersonID = @id_in';
EXEC sys.sp_executesql @sql, N'@id_in INT, @id_out INT OUT',
@inputParam, @outputParam OUT;
SELECT @outputParam OutParamValue
GO

Well, there you go. I believe now in the future if I ever need a template or T-SQL where I need to pass input or output parameters, I will have examples ready. I am personally going to bookmark this blog post and keep it available with me.

Here are a few additional blog posts on the same topic: