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:
- SQL SERVER – Results of Dynamic SQL Into A Variable
- SQL SERVER – Dynamic SQL and Temporary Tables
- SQL SERVER – Dynamic SQL and Global Temporary Tables
- SQL SERVER – One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?
- Sort Query Using Dynamic Variables Without EXEC – Interview Question of the Week #074
- SQL SERVER – How to See Scripts Executing in sp_executesql?
- SQL SERVER – SCRIPT – SQL Listening on Dynamic Port?
This learning experience is an ongoing process and the best way I learn is by doing a search on the blog from time to time. Long ago I had written a blog post to find the port number on which SQL Server is listening: SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running
Reference:Â Pinal Dave (https://blog.sqlauthority.com)