Generate stored procedure parameter list based on table order by table

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

  • Customer table? Change what customer table?

  • With a few changes, it runs on SQL2000: (table names go into the WHERE clause on line 35, eg 'tIndividuals') - Merci, Jean-Pierre - RM

    --Jean-Pierre Lebrasseur

    --date 04-12-2009

    --this script give you columns parameters order by table name and ordinal position by table.

    declare @sqlstmt char(500)

    declare @name char(50)

    declare @coldefinition char(50)

    declare CreateParam cursor for

    select 'declare @' + c.name, col_definition =

    case t.name

    when 'bigint' then 'bigint'

    when 'int' then 'int'

    when 'smallint' then 'smallint'

    when 'tinyint' then 'tinyint'

    when 'bit' then 'bit'

    when 'decimal' then 'decimal'

    when 'numeric' then 'numeric'

    when 'money' then 'money'

    when 'smallmoney' then 'smallmoney'

    when 'float' then 'float'

    when 'real' then 'real'

    when 'datetime' then 'datetime'

    when 'smalldatetime' then 'smalldatetime'

    when 'sql_variant' then 'sql_variant'

    when 'timestamp' then 'timestamp'

    when 'uniqueidentifier' then 'uniqueidentifier'

    when 'xml' then 'xml'

    else t.name + '(' + cast(c.prec as varchar(50)) + ')'

    end

    from sysobjects s

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

    inner join systypes t on t.xusertype = c.xtype

    where s.xtype='U' and

    (s.name = 'tIndividuals' or s.name = 'ProductStatus' or s.name = 'ProductBrandCode')

    order by s.name , c.colid

    open CreateParam

    fetch next from CreateParam into @name, @coldefinition

    while @@fetch_status = 0

    begin

    set @sqlstmt = ''

    set @sqlstmt = rtrim(@sqlstmt) + rtrim(@name) + ' ' + rtrim(@coldefinition)

    print rtrim(@sqlstmt)

    fetch next from CreateParam into @name, @coldefinition

    end

    close CreateParam

    deallocate CreateParam

    go

  • Thanks for the script.

Viewing 4 posts - 1 through 3 (of 3 total)

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