how to determine programatically what output of a sp will be?

Using Sql Srv 7

I know I can use the system sp's sp_stored_procedures and sp_sproc_columns
to determine all the sps in a db, and what input parms there are for a
particular sp... but... if the sp returns a result set, is there a way to
find out the stru of that in a similar manner?Am Wed, 5 Oct 2005 09:04:14 -0500 schrieb spiffo:

> Using Sql Srv 7
> I know I can use the system sp's sp_stored_procedures and sp_sproc_columns
> to determine all the sps in a db, and what input parms there are for a
> particular sp... but... if the sp returns a result set, is there a way to
> find out the stru of that in a similar manner?

I never put time in finding an answer for this question but my first
thought is, that it cannot be possible, because i can write a procedure
like this:

create stored procedure myRandom as
if datepart(s,getdate()) % 2 = 1 select 'field_1', 1
else select 123, 'last'

So what is the struct of the resultset? It changes every second!

bye,
helmut

How to determine if stor proc parameter is output or input

I know that you can retrieve whether a parameter is for output buy way
of the "isoutparam" field, but is there anything that tells you whether
a parameter is input/output?
thanks(jw56578@.gmail.com) writes:
> I know that you can retrieve whether a parameter is for output buy way
> of the "isoutparam" field, but is there anything that tells you whether
> a parameter is input/output?

The only true output-only value is the return value. All parameters are
input/output. This is T-SQL, not Ada. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am using a .net Parameter builder, to build paramter objects based
on system information. When it sees a parameter used for input and
output, it "isoutparam" indicates that it is an output, so its
"Direction" attribute is assigned a value of output. But if i want to
use it as an input, it doesn't work.|||(jw56578@.gmail.com) writes:
> I am using a .net Parameter builder, to build paramter objects based
> on system information. When it sees a parameter used for input and
> output, it "isoutparam" indicates that it is an output, so its
> "Direction" attribute is assigned a value of output. But if i want to
> use it as an input, it doesn't work.

That builder seems to have a bug. :-)

The only "parameter" that should have Direction.IsOutput is the return
value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How to define a user variable on Execute Sql Task?

Hi everyone,

How to define a Input variable in a Execute Sql Task?

I've defined a User::Inicio variable which contains 4 as value.

In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:

UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]

or

UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]

Anyway, I'm stuck, both did not work

Thanks in advance for your comments

Enric,

Use an expression in SQlStatementSource property of your Execute SQL task to build your SQL statement:

"UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = " @.[User::Inicio]

Rafael Salas

|||

Hi Rafael,

Thanks for your quick answer but it doesn't work.

[Execute SQL Task] Error: Executing the query "UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [@.User::Inicio]" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Let me know, I can imagine that's a silly thing..

|||

Well, the error talks abour the ResulSet porperty; what is your value for that? what is you set that to None. Also i think you do not need anything in your parameter tab since the SQL statement is being created by the expression

RAfael Salas

|||Rafael is telling you to set an Expression for the SQLStatementSource property and not set the property value directly. Looks like you set the SQLStatementSource directly to "UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = " + @.[User::Inicio]. To set an expression for the SQLStatementSource property click on the Expressions node on the left hand side of the Execute SQL Task Editor dialog.|||

Hi,

You mean you want to use the user variable in your query right?

Refer this:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

|||Thanks to all of you