Simple way to creating the Store procedure parameters for the table in SQL Server

Simple way to creating the Store procedure parameters for the table in SQL Server

Step 1: Pass the Table name as parameter for the below query and execute

Using the code

//

// code

//

declare @TableName sysname = '@TableName'

declare @result varchar(max) = ''

SELECT

       @result = @result + '

@' + ColumnName + ' ' + ColumnType + ''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + ') =NULL,'

              ELSE typ.name + '=NULL,'

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @result

print @result