SQL: Generate Dapper Params from SQL Server Objects

FromProcedure.sql

DECLARE @procedure_name sysname = 'dbo.procedure_name'
SELECT 
	OBJECT_SCHEMA_NAME([p].[object_id]) AS [schema],
	OBJECT_NAME([p].[object_id]) AS [procedure_name],
    [p].[name] AS [parameter_name],
	CONCAT('sqlParams.Add("', [p].[name] ,'", ', [p].[name], ', ', [fn].db_type, ');')
FROM sys.[parameters] AS [p]
INNER JOIN sys.[types] AS [t] 
	ON [p].[system_type_id] = [t].[system_type_id]
	AND [p].[user_type_id] = [t].[user_type_id]
CROSS APPLY (
	SELECT 
		CASE t.name
			-- String types with length
			WHEN 'char' THEN CONCAT('DbType.AnsiStringFixedLength, size: ', [p].max_length)
			WHEN 'varchar' THEN 
				CASE WHEN [p].max_length = -1 THEN 'DbType.AnsiString, size: -1' -- MAX
				ELSE CONCAT('DbType.AnsiString, size: ', [p].max_length)
				END
			WHEN 'nchar' THEN CONCAT('DbType.StringFixedLength, size: ', [p].max_length/2)
			WHEN 'nvarchar' THEN 
				CASE WHEN [p].max_length = -1 THEN 'DbType.String, size: -1' -- MAX
				ELSE CONCAT('DbType.String, size: ', [p].max_length/2)
				END
			WHEN 'text' THEN 'DbType.AnsiString, size: -1'
			WHEN 'ntext' THEN 'DbType.String, size: -1'

			-- Numeric types with precision and scale
			WHEN 'decimal' THEN CONCAT('DbType.Decimal, precision: ', [p].[precision], ', scale: ', [p].[scale])
			WHEN 'numeric' THEN CONCAT('DbType.Decimal, precision: ', [p].[precision], ', scale: ', [p].[scale])

			-- Integer types
			WHEN 'bit' THEN 'DbType.Boolean'
			WHEN 'tinyint' THEN 'DbType.Byte'
			WHEN 'smallint' THEN 'DbType.Int16'
			WHEN 'int' THEN 'DbType.Int32'
			WHEN 'bigint' THEN 'DbType.Int64'

			-- Float/Real types
			WHEN 'real' THEN 'DbType.Single'
			WHEN 'float' THEN 'DbType.Double'
			WHEN 'money' THEN 'DbType.Currency'
			WHEN 'smallmoney' THEN 'DbType.Currency'

			-- Date/Time types
			WHEN 'date' THEN 'DbType.Date'
			WHEN 'smalldatetime' THEN 'DbType.DateTime'
			WHEN 'datetime' THEN 'DbType.DateTime'
			WHEN 'datetime2' THEN 'DbType.DateTime2'
			WHEN 'datetimeoffset' THEN 'DbType.DateTimeOffset'
			WHEN 'time' THEN 'DbType.Time'

			-- Binary types
			WHEN 'binary' THEN CONCAT('DbType.Binary, size: ', [p].max_length)
			WHEN 'varbinary' THEN 
				CASE WHEN [p].max_length = -1 THEN 'DbType.Binary, size: -1' -- MAX
				ELSE CONCAT('DbType.Binary, size: ', [p].max_length)
				END
			WHEN 'image' THEN 'DbType.Binary, size: -1'

			-- Other types
			WHEN 'uniqueidentifier' THEN 'DbType.Guid'
			WHEN 'xml' THEN 'DbType.Xml'
			WHEN 'sql_variant' THEN 'DbType.Object'

			ELSE 'DbType.Object' -- Default fallback
		END AS [db_type]
) AS [fn]
WHERE [p].[object_id] = OBJECT_ID(@procedure_name)
ORDER BY [p].[parameter_id]

FromTable.sql

DECLARE @table_name sysname = 'dbo.tablename'

SELECT 
	OBJECT_SCHEMA_NAME([c].[object_id]) AS [schema],
	OBJECT_NAME([c].[object_id]) AS [table_name],
    [c].[name] AS [column_name],
	CONCAT('sqlParams.Add("@', [c].[name] ,'", ', [c].[name], ', ', [fn].db_type, ');')
FROM sys.[columns] AS [c] 
INNER JOIN sys.[types] AS [t] 
	ON [c].[system_type_id] = [t].[system_type_id]
	AND [c].[user_type_id] = [t].[user_type_id]
CROSS APPLY (
	SELECT 
		CASE t.name
			-- String types with length
			WHEN 'char' THEN CONCAT('DbType.AnsiStringFixedLength, size: ', c.max_length)
			WHEN 'varchar' THEN 
				CASE WHEN c.max_length = -1 THEN 'DbType.AnsiString, size: -1' -- MAX
				ELSE CONCAT('DbType.AnsiString, size: ', c.max_length)
				END
			WHEN 'nchar' THEN CONCAT('DbType.StringFixedLength, size: ', c.max_length/2)
			WHEN 'nvarchar' THEN 
				CASE WHEN c.max_length = -1 THEN 'DbType.String, size: -1' -- MAX
				ELSE CONCAT('DbType.String, size: ', c.max_length/2)
				END
			WHEN 'text' THEN 'DbType.AnsiString, size: -1'
			WHEN 'ntext' THEN 'DbType.String, size: -1'

			-- Numeric types with precision and scale
			WHEN 'decimal' THEN CONCAT('DbType.Decimal, precision: ', c.precision, ', scale: ', c.scale)
			WHEN 'numeric' THEN CONCAT('DbType.Decimal, precision: ', c.precision, ', scale: ', c.scale)

			-- Integer types
			WHEN 'bit' THEN 'DbType.Boolean'
			WHEN 'tinyint' THEN 'DbType.Byte'
			WHEN 'smallint' THEN 'DbType.Int16'
			WHEN 'int' THEN 'DbType.Int32'
			WHEN 'bigint' THEN 'DbType.Int64'

			-- Float/Real types
			WHEN 'real' THEN 'DbType.Single'
			WHEN 'float' THEN 'DbType.Double'
			WHEN 'money' THEN 'DbType.Currency'
			WHEN 'smallmoney' THEN 'DbType.Currency'

			-- Date/Time types
			WHEN 'date' THEN 'DbType.Date'
			WHEN 'smalldatetime' THEN 'DbType.DateTime'
			WHEN 'datetime' THEN 'DbType.DateTime'
			WHEN 'datetime2' THEN 'DbType.DateTime2'
			WHEN 'datetimeoffset' THEN 'DbType.DateTimeOffset'
			WHEN 'time' THEN 'DbType.Time'

			-- Binary types
			WHEN 'binary' THEN CONCAT('DbType.Binary, size: ', c.max_length)
			WHEN 'varbinary' THEN 
				CASE WHEN c.max_length = -1 THEN 'DbType.Binary, size: -1' -- MAX
				ELSE CONCAT('DbType.Binary, size: ', c.max_length)
				END
			WHEN 'image' THEN 'DbType.Binary, size: -1'

			-- Other types
			WHEN 'uniqueidentifier' THEN 'DbType.Guid'
			WHEN 'xml' THEN 'DbType.Xml'
			WHEN 'sql_variant' THEN 'DbType.Object'

			ELSE 'DbType.Object' -- Default fallback
		END AS [db_type]
) AS [fn]
WHERE c.[object_id] = OBJECT_ID(@table_name)
	AND [c].[is_identity] = 0
	AND [c].[is_computed] = 0
ORDER BY [c].[column_id]

Could have written the data type conversion as a function, but I did not want to have to create a function if not needed.