| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_help_params | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_help_params 4 ** 5 ** Servant sproc to sp_help to pull out column related information 6 ** for a procedure, trigger, or SQL-J functions. It is assumed that the 7 ** caller has validated tha the object exists, and is one of these kinds. 8 ** If not, you will just get no rows back, which will still be ok. 9 ** (Currently, this is just an internal proc. If it gets to be used as 10 ** user-visible proc, we need updates to check for @objname etc.) 11 ** 12 ** Parameters: 13 ** @objname - Name of object, possibly multi-part 14 ** 15 ** Returns: Nothing. 16 { 17 */ 18 create procedure sp_help_params(@objname varchar(767) 19 ) as 20 begin 21 declare @sptlang int 22 , @grp_count int 23 , @selectlist_str varchar(80) 24 , @orderby_str varchar(40) 25 26 -- Does the sproc have any parameters? It not, nothing to do here. 27 if not exists (select 1 28 from syscolumns c, systypes t 29 where c.id = object_id(@objname) 30 and c.usertype *= t.usertype) 31 return 0 32 33 select @sptlang = @@langid 34 35 if (@sptlang != 0) -- User has defined a non-default language 36 begin 37 if not exists (select * from master.dbo.sysmessages 38 where error between 17100 and 17109 39 and langid = @@langid) 40 select @sptlang = 0 41 end 42 43 set nocount on 44 45 end 46 -- Parameter_name _can_ be NULL for SQL-J functions, as per 47 -- the SQL-J spec, but currently we don't allow that. Even 48 -- if we do so in the future, the name will come out as 49 -- NULL, which is ok. Now, we do convert() to varchar() NULL 50 -- as this is used to create a new table, and inserting NULL 51 -- name will cause a problem at run-time. 52 -- 53 select Parameter_name = convert(varchar(255) NULL, c.name), 54 Type = isnull(convert(char(30), x.xtname), 55 isnull(convert(char(30), 56 get_xtypename(c.xtype, c.xdbid)), 57 t.name)), 58 Length = c.length, 59 Prec = c.prec, 60 Scale = c.scale, 61 Param_order = c.colid, 62 Grp_number = c.number, 63 rtype = t.type, 64 utype = t.usertype, 65 66 /* 67 ** Handle the case where upgraded databases 68 ** have syscolumns.status2 as NULL. Report 69 ** those as 'unknown' parameter types. 70 */ 71 Mode = case c.status2 72 when NULL then "unknown" 73 else (select convert(nvarchar(20), mi.description) 74 from master.dbo.spt_values vi 75 , master.dbo.sysmessages mi 76 where vi.number = c.status2 77 and vi.msgnum = mi.error 78 and isnull(mi.langid, 0) = @sptlang 79 and mi.error between 17583 and 17586) 80 end 81 into #helpproc 82 from syscolumns c, systypes t, sysxtypes x 83 where c.id = object_id(@objname) 84 and c.usertype *= t.usertype 85 and c.xtype *= x.xtid 86 87 /* If it is a procedure group, grp_count>1 */ 88 select @grp_count = count(distinct Grp_number) 89 from #helpproc 90 91 /* Handle National Characters */ 92 update #helpproc 93 set Length = Length / @@ncharsize 94 where (rtype = 47 and utype = 24) 95 or (rtype = 39 and utype = 25) 96 97 /* Handle unichar/univarchar */ 98 update #helpproc 99 set Length = Length / @@unicharsize 100 where rtype in (select type from systypes 101 where name in ('unichar', 'univarchar')) 102 103 /* Handle unsigned types by outputing user syntax */ 104 update #helpproc 105 set Type = "unsigned " + 106 substring(Type, charindex("u", Type) + 1, 30) 107 where utype in (44, 45, 46) 108 109 /* For display, group by group number too in case 110 ** it is a procedure group 111 */ 112 if (@grp_count = 1) 113 begin 114 select @selectlist_str = "Parameter_name,Type,Length,Prec,Scale,Param_order,Mode" 115 , @orderby_str = "order by Param_order asc" 116 end 117 else 118 begin 119 select @selectlist_str = "Parameter_name,Type,Length,Prec,Scale,Param_order,Mode,Grp_number" 120 , @orderby_str = "order by Grp_number asc, Param_order asc" 121 end 122 123 exec sp_autoformat @fulltabname = #helpproc, 124 @selectlist = @selectlist_str, 125 @orderby = @orderby_str 126 drop table #helpproc 127
exec sp_procxmode 'sp_help_params', 'AnyMode' go Grant Execute on sp_help_params to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..systypes read_writes table tempdb..#helpproc (1) reads table sybsystemprocs..sysxtypes reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..sp_autoformat  reads table tempdb..syscolumns (1)  read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_autoformat reads table master..syscolumns (1)  reads table tempdb..systypes (1) reads table master..systypes (1) calls proc sybsystemprocs..sp_namecrack reads table master..sysmessages (1) reads table master..spt_values (1) CALLERS called by proc sybsystemprocs..sp_showtext_sproc_tagline called by proc sybsystemprocs..sp_showtext_output called by proc sybsystemprocs..sp_showtext called by proc sybsystemprocs..sp_helptext_usage called by proc sybsystemprocs..sp_helptext called by proc sybsystemprocs..sp_helptext called by proc sybsystemprocs..sp_help |
