Skip to content

Auto generating parameters for SQL procedure

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
declare  @procedure varchar(50) = '[dbo].[test]'

select  
    name,
    type_name(user_type_id) Type,  
    case when type_name(system_type_id) = 'uniqueidentifier' 
    then precision else OdbcPrec(system_type_id, max_length, precision) end Length,  
    OdbcScale(system_type_id, scale) scale,
    case  
        when type_name(user_type_id) ='int'     then    '<itm model="param" name="'+replace(name,'@','')+'" type="number" ></itm>'
        when type_name(user_type_id) ='bigint'  then    '<itm model="param" name="'+replace(name,'@','')+'" type="number" ></itm>'
        when type_name(user_type_id) ='datetime'  then  '<itm model="param" name="'+replace(name,'@','')+'" type="date" ></itm>'
        when type_name(user_type_id) ='bit'     then    '<itm model="param" name="'+replace(name,'@','')+'" type="checkbox" ></itm>'
        else '<itm model="param" name="'+replace(name,'@','')+'" type="text" ></itm>'
    end item1

from sys.parameters where object_id = object_id(@procedure)