How to get the value of a parameter programmatically

Hi,,
Say I have connected to the webs ervice and extracted all the parameters of
a particular report ,,how can I show the default bvalue of those parameter
if any'
thanks for your help,,here is my code:
ReportingService rs = new ReportingService(enviromental_Vars.LocalPath);
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//string report = this.cmbReports.Items[this.cmbReports.SelectedIndex].ToString();
bool forRendering = false;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
try
{
parameters = rs.GetReportParameters(enviromental_Vars.ReportName, historyID,
forRendering, values, credentials);
if (parameters != null)
{
}
}There is a property on the ReportParameter class with the name of
DefaultValues, and of type string[].
You could use something along the lines of:
foreach(ReportParameter p in parameters)
{
string[] defaults = p.DefaultValues;
// work on defaults...
}
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 12 Nov 2004 19:12:19 -0800, "ALI-R" <newbie@.microsoft.com>
wrote:
>Hi,,
>Say I have connected to the webs ervice and extracted all the parameters of
>a particular report ,,how can I show the default bvalue of those parameter
>if any'
>thanks for your help,,here is my code:
>ReportingService rs = new ReportingService(enviromental_Vars.LocalPath);
>rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
>//string report =>this.cmbReports.Items[this.cmbReports.SelectedIndex].ToString();
>bool forRendering = false;
>string historyID = null;
>ParameterValue[] values = null;
>DataSourceCredentials[] credentials = null;
>ReportParameter[] parameters = null;
>try
>{
>parameters = rs.GetReportParameters(enviromental_Vars.ReportName, historyID,
>forRendering, values, credentials);
>if (parameters != null)
>{
>
>}
>}
>

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the return value of a stored procedure

I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end:
Return Scope_Identity()

I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.

I have intReturn defined as an integer

I've tried :
Dim retValParam As OleDbParameter = cmd.Parameters.Add("@.RETURN_VALUE", OleDbType.Integer)
retValParam.Direction = ParameterDirection.ReturnValue
intReturn=cmd.Parameters("@.RETURN_VALUE").Value

whenever I add this section - I get an error that there are too many arguments for the sProc.

I've tried:
intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.

What is the best way to do this in the code part (VB.Net)?

Thanks ahead of time

check the second part ofthis article

|||

Turned out, I didn't even use a parameter for the Returnvalue -

I ended up using something I'd tried before :
lg=cmd.ExecuteScalar....and it worked.

sql

how to get the parameters information of UDFs?

I can get the list of UDF from sysobjects. Any simple way to get the
parameter informations?
Txselect r.specific_schema, r.specific_name, p.ordinal_position,
p.parameter_mode, p.parameter_name, p.data_type,
coalesce(p.character_maximum_length, p.numeric_precision) as parm_size
from information_schema.routines r
join information_schema.parameters p on
r.specific_schema=p.specific_schema and r.specific_name=p.specific_name
where r.routine_type='FUNCTION'
order by r.specific_name, p.ordinal_position
nick wrote:
> I can get the list of UDF from sysobjects. Any simple way to get the
> parameter informations?
> Tx
--
Please post DDL, sample data and desired results from that sample data.
Otherwise, all answers can be considered to be nothing more than guesses.
Trey Walpole|||nick (nick@.discussions.microsoft.com) writes:
> I can get the list of UDF from sysobjects. Any simple way to get the
> parameter informations?
For table UDF:
select * from syscolumns
where name like '@.%'
and id = object_id('yourfun')
The condition on name is required to keep the parameters apart from the
output columns.
For a scalar UDF, you may want to take out that condition, if you also
want information about the return value. (Which has a blank name.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to get sql procedure parameters count and their type?

Hi All,
i am using DBLib and VC++. i want to know the number of parameters
of a procedure at run time. is it posible if yes then how can i do this.
thanks.Use ADO's OpenSchema method with the schema ID: adProcedureParameters|||is there another way of getting parameter count other than ADO.
actually we dont use ADO. some winodws api etc.

thanks for reply.

omar|||Check out INFORMATION_SCHEMA.ROUTINE_COLUMNS

How to get parameters in XSLT from template files...

I'd like to get the HTTP parameters to a form in both the XML template and
the XSLT.
I know that I can use <sql:param> to get the parameter for use in my SQL.
But why doesn't it also get passed to the XSLT as <xsl:param> values for me
to use?
PerWe cannot pass the variables to the stylesheet that way unfortunately. The
best way is to add them to the XML document that you pass to the stylesheet
and then take them and use them throughout your XSLT.
Best regards
Michael
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:uc4rlHEJFHA.904@.tk2msftngp13.phx.gbl...
> I'd like to get the HTTP parameters to a form in both the XML template and
> the XSLT.
> I know that I can use <sql:param> to get the parameter for use in my SQL.
> But why doesn't it also get passed to the XSLT as <xsl:param> values for
> me to use?
> Per
>

How to get parameters in XSLT from template files...

I'd like to get the HTTP parameters to a form in both the XML template and
the XSLT.
I know that I can use <sql:param> to get the parameter for use in my SQL.
But why doesn't it also get passed to the XSLT as <xsl:param> values for me
to use?
Per
We cannot pass the variables to the stylesheet that way unfortunately. The
best way is to add them to the XML document that you pass to the stylesheet
and then take them and use them throughout your XSLT.
Best regards
Michael
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:uc4rlHEJFHA.904@.tk2msftngp13.phx.gbl...
> I'd like to get the HTTP parameters to a form in both the XML template and
> the XSLT.
> I know that I can use <sql:param> to get the parameter for use in my SQL.
> But why doesn't it also get passed to the XSLT as <xsl:param> values for
> me to use?
> Per
>

how to get names and types of parameters of stored procedure

Hi everybody!
I need to get names and types of parameters of stored procedures from some
system tables.
( not from SQLDMO object ) . Does somebody know in what system table(s)
these data stored?
ThanksYou can find this information in the information_schema.parameters view.
--
Jacco Schalkwijk
SQL Server MVP
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:D7FE4664-3595-4EC7-8EE3-C453B4823E74@.microsoft.com...
> Hi everybody!
> I need to get names and types of parameters of stored procedures from
> some
> system tables.
> ( not from SQLDMO object ) . Does somebody know in what system table(s)
> these data stored?
> Thanks
>|||There is also a stored procedure, sp_proc_columns, I believe, that will
parse out that information for you.
Sincerely,
Anthony Thomas
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23dhtDeB6EHA.1564@.TK2MSFTNGP09.phx.gbl...
You can find this information in the information_schema.parameters view.
--
Jacco Schalkwijk
SQL Server MVP
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:D7FE4664-3595-4EC7-8EE3-C453B4823E74@.microsoft.com...
> Hi everybody!
> I need to get names and types of parameters of stored procedures from
> some
> system tables.
> ( not from SQLDMO object ) . Does somebody know in what system table(s)
> these data stored?
> Thanks
>|||Thanks to both of you :)
"AnthonyThomas" wrote:
> There is also a stored procedure, sp_proc_columns, I believe, that will
> parse out that information for you.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
> in message news:%23dhtDeB6EHA.1564@.TK2MSFTNGP09.phx.gbl...
> You can find this information in the information_schema.parameters view.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:D7FE4664-3595-4EC7-8EE3-C453B4823E74@.microsoft.com...
> > Hi everybody!
> >
> > I need to get names and types of parameters of stored procedures from
> > some
> > system tables.
> > ( not from SQLDMO object ) . Does somebody know in what system table(s)
> > these data stored?
> >
> > Thanks
> >
> >
>
>

how to get names and types of parameters of stored procedure

Hi everybody!
I need to get names and types of parameters of stored procedures from some
system tables.
( not from SQLDMO object ) . Does somebody know in what system table(s)
these data stored?
Thanks
You can find this information in the information_schema.parameters view.
Jacco Schalkwijk
SQL Server MVP
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:D7FE4664-3595-4EC7-8EE3-C453B4823E74@.microsoft.com...
> Hi everybody!
> I need to get names and types of parameters of stored procedures from
> some
> system tables.
> ( not from SQLDMO object ) . Does somebody know in what system table(s)
> these data stored?
> Thanks
>
|||There is also a stored procedure, sp_proc_columns, I believe, that will
parse out that information for you.
Sincerely,
Anthony Thomas

"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23dhtDeB6EHA.1564@.TK2MSFTNGP09.phx.gbl...
You can find this information in the information_schema.parameters view.
Jacco Schalkwijk
SQL Server MVP
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:D7FE4664-3595-4EC7-8EE3-C453B4823E74@.microsoft.com...
> Hi everybody!
> I need to get names and types of parameters of stored procedures from
> some
> system tables.
> ( not from SQLDMO object ) . Does somebody know in what system table(s)
> these data stored?
> Thanks
>
|||Thanks to both of you
"AnthonyThomas" wrote:

> There is also a stored procedure, sp_proc_columns, I believe, that will
> parse out that information for you.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
> in message news:%23dhtDeB6EHA.1564@.TK2MSFTNGP09.phx.gbl...
> You can find this information in the information_schema.parameters view.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:D7FE4664-3595-4EC7-8EE3-C453B4823E74@.microsoft.com...
>
>