Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate stored procedure parameter list based on table order by table Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 1:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 21, 2010 8:27 AM
Points: 1, Visits: 7
Comments posted to this topic are about the item Generate stored procedure parameter list based on table order by table
Post #832037
Posted Monday, December 28, 2009 7:25 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 10:09 AM
Points: 94, Visits: 217
Customer table? Change what customer table?
Post #839405
Posted Monday, December 28, 2009 8:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:22 PM
Points: 69, Visits: 348
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

Post #839426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse