none
Discover parameters for a sql proc? RRS feed

  • Question

  • Is there any way by which I can discover information about an sql procedure from a database? That is one part. Once discovered I would want to provide a form for passing arguments so that ultimately I can execute the procedure...
    Sunday, December 23, 2007 5:49 AM

All replies

  • Hello,

     

    I hope your database is in SQL Server 2005.

    It's possible to get the datatype of parameters of a stored procedure with SMO ( i don't know whether it's working when the stored procedure is encrypted ).

     

    You must add a reference to Microsoft.SqlServer.Management.ConnectionInfo, Microsoft.SqlServer.Management.SMO

    http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx

    we suppose :

    instance name in a variable alpha

    database         in a variable beta

    stored procedure name in a variable delta

     

    in VC#

    using Microsoft.SqlServer.Management.SMO;

     

    Server server = new Server(alpha);

    DataBase Db = server.DataBases[beta];

    StoredProcedure sp = db.StoredProcedures[delta];

    ParametersCollection parmcoll = sp.Parameters;

    int i = 0;

    foreach ( Parameter parm in parmcoll )

    {

    Console.Writeline("Parameter {0} : name : {1}  type {2} ",parm.name,parm.DataType.ToString();

    i++;

    }

     

    I wrote this code without consulting documentation but i've already written a procedure which gave me parameters type for stored procedures. But i'm unable for the moment to retrieve the code.I hope my memory is not too bad.

     

    Good luck and have a nice day

     

    Sunday, December 23, 2007 9:27 PM
  • With regards to sql server 2k or 2k5, I did a small excercise to find out what could have been done. I just opened a sql profiler session and then went over to visual studio ide and tried many things. I tried to add a stored procedure for inserting values into a database. On that dialog box I hit a button called refresh parameters and that gave me the parameters for that procedure I had given. I went over to the profiler and checked out what was executed and discovered a stored procedure was executed (sp_procedure_params_managed)...I think this should help to a better degree.
    Tuesday, March 4, 2008 4:11 PM