sp 파라메타 만들기

SELECT ', @P_' + COLUMN_NAME + SPACE(10) + CASE DATA_TYPE WHEN 'varchar' THEN UPPER(DATA_TYPE) + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX)) + ')'

WHEN 'numeric' THEN CASE WHEN NUMERIC_SCALE > 0

THEN UPPER(DATA_TYPE) + '(' + CAST(NUMERIC_PRECISION AS VARCHAR(2)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(1))

ELSE UPPER(DATA_TYPE) + '(' + CAST(NUMERIC_PRECISION AS VARCHAR(2)) + ')'

END

END

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TABLE_NAME AND DATA_TYPE IN ('varchar', 'numeric', 'char')

ORDER BY ORDINAL_POSITION