Generate stored procedure parameter list based on table

  • Thomas Cournane

    SSC Journeyman

    Points: 81

    Comments posted to this topic are about the item Generate stored procedure parameter list based on table

  • Paras@ISRC

    Grasshopper

    Points: 24

    It is very good stored procedure for tables having many columns and complex names.

    One suggestion is - Instead of using default length of 5o for varchar use c.length column to get actual lenght.

  • Thomas Cournane

    SSC Journeyman

    Points: 81

    Thank you Paras. I've made the enhancement.

  • tcooper-613006

    Grasshopper

    Points: 19

    I get "incorrect syntax near "c.length" when trying this.

    Tim

    paras.halingale (12/4/2009)


    It is very good stored procedure for tables having many columns and complex names.

    One suggestion is - Instead of using default length of 5o for varchar use c.length column to get actual lenght.

  • Thomas Cournane

    SSC Journeyman

    Points: 81

    Hey,

    I've just had to use this again and have made some changes, I realise I only really need to the length of the column for text columns so here it is:

    select c.name as name,

    ' = ',

    '@' + c.name as _name_param,

    c.name + '_DESC' as name_ordered,

    t.name + '(' + cast(c.prec as varchar(50)) + ')' as [col_def],

    coldefinition =

    case t.name

    when 'nvarchar' then 'nvarchar' + '(' + cast(c.prec as varchar(50)) + ')'

    when 'varchar' then 'nvarchar' + '(' + cast(c.prec as varchar(50)) + ')'

    when 'char' then 'char' + '(' + cast(c.prec as varchar(50)) + ')'

    when 'nchar' then 'nchar' + '(' + cast(c.prec as varchar(50)) + ')'

    else t.name

    end

    + ' = NULL'

    from sysobjects s

    inner join sys.syscolumns c on s.id = c.id

    inner join sys.types t on t.user_type_id = c.xtype

    where s.xtype='U'

    and s.name = 'PolicyZoneMembership'

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply