Geeks With Blogs
David Christiansen | davidchristiansen.com

I find this code really handy when dealing with a stored procedure that requires a tonn of input and output parameters. This stored procedure will write out the code required which you can copy and paste into your VB or VBScript code.

It wouldn't require many changes to write out code for .NET.

To get it working, simply change sp_changeMeStoredProcedure to your stored procedure name and run the code within Query Analyzer or similar.

/*** Create ADODB.Command parameters ***/
select
   'cmd.Parameters.Append cmd.CreateParameter("' + rtrim(sc.Name) + '", ' +
   case st.name
      when 'bit' then 'adBoolean'
      when 'char' then 'adChar'
      when 'datetime' then 'adDate'
      when 'decimal' then 'adDecimal'
      when 'float' then 'adDecimal'
      when 'int' then 'adInteger'
      when 'money' then 'adCurrency'
      when 'real' then 'adDecimal'
      when 'smallint' then 'adSmallInt'
      when 'text' then 'adVarChar'
      when 'tinyint' then 'adTinyInt'
      when 'uniqueidentifier' then 'adGUID'
      when 'varchar' then 'adVarchar'
      else st.name
   end
   + ', ' +
   case sc.isoutparam
      when 1 then 'adParamInputOutput'
      else 'adParamInput'
   end
   + ', ' +
   case st.name
      when 'char' then IsNull(cast(sc.length as varchar(4)), '')
      when 'varchar' then IsNull(cast(sc.length as varchar(4)), '')
      when 'text' then '2147483647'
      else ''
   end
   + ', ' + replace(rtrim(sc.Name),
'@', '') + ')'
from syscolumns sc inner join systypes st on sc.xtype = st.xtype
where id = object_id('sp_changeMeStoredProcedure') order by colorder

Posted on Wednesday, November 10, 2004 7:19 PM | Back to top

Copyright © David Christiansen | Powered by: GeeksWithBlogs.net