in what situations the dynamic port number in sql server is used? I have the following script to find out what is the Static and dynamic port numbers:

--===============================
-- sys.dm_exec_connections
--===============================
SELECT 
[user]=suser_sname()
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

--===============================
-- Execute below script if SQL Server is configured with dynamic port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

--===============================
-- Execute below script if SQL Server is configured with static port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO




The following link mentions netstat -ano to check usage from the cmdline.

When is a Dynamic Port “dynamic”?

when is the dynamic port used?
Dynamic ports are used when a SQL Server instance is configured to use dynamic ports on the specified interface(s). By default, only named instances use dynamic ports as described in the documentation. Below is an excerpt from the doc:


  At startup, when an instance of SQL Server is configured to listen on
  dynamic ports, it checks with the operating system for an available
  port, and opens an endpoint for that port. Incoming connections must
  specify that port number to connect. Since the port number can change
  each time SQL Server starts, SQL Server provides the SQL Server
  Browser Service, to monitor the ports, and direct incoming connections
  to the current port for that instance. Using dynamic ports complicates
  connecting SQL Server through a firewall because the port number may
  change when SQL Server is restarted, requiring changes to the firewall
  settings. To avoid connection problems through a firewall, configure
  SQL Server to use a static port.


The SQL Server error log will also include messages indicating the listening interface IP addresses and ports.

EDIT:

As mentioned by @TiborKaraszi, SQL Server remembers that last used dynamic port and will continue to use that as the going forward unless there's a conflict, in which case a new one is used.