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

3 years ago

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.

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: