SQL functions

[Guest]

Do we need a datareader to get values from Sql Functions ?
my function has a return varchar(20) and pops an error that cannot return a
resultset , why?

 

[Cor Ligthert]

Raulavi,

What do you want to get returned.

To say it in another way.
When you returns something you have to store it in a valid value or object.

Cor

 

[Guest]

my sql function will return a varchar(20)...
return 'errdesc'

where errdesc will be a varchar
I wondr if I have to use the
return
or
select errdesc

Thanks

 

[Cor Ligthert]

Raulavi,

my sql function will return a varchar(20)...

A varchar is not a VBNet value.

Cor

 

[Guest]

I am using in my vb net
Label1.Text = odataReader.GetString(0).ToString

on my SQL storeProcedue
if my SQl ends with any
Select errdesc (defined as varchar(20) it works)
if I do
Return errdesc it does not.

Why?

 

[Cor Ligthert]

Raulavi,

Can you show a sample from that procedure that ends with

Select errdesc

Cor

 

[Guest]

ALTER PROCEDURE "SOS"."ValidateScrFields2"(in @fldNum integer)
begin
declare local temporary table tmp(
fldtoPOP integer null default 0,
errDesc long varchar null,
)
in system not transactional;
insert into tmp( fldtoPop,errDesc) values( @Fldnum,'errorxxxxxxxx') ;
select fldtoPOP,errDesc from tmp
end

I can read from vb the errDesc from the select.

If I change this procedure to a function with a return it pops the error I
describe earlier.

 

[Cor Ligthert]

Raulavie,

Although I see only a very slight relation with VBNet. Did you try it on SQL
itself?

Otherwise do I think that in this newsgroup you have more people who can
help you.

Adonet
news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

Web interface:
http://communities2.microsoft.com/c.../?dg=microsoft.public.dotnet.framework.adonet

SQL itself is absolute not in my interest and certainly not complex
statements with SQL. The same as Regex, do I not like it you know and use it
only when/because there are no alternatives.

Sorry that I cannot help you better.

Cor

 

[Ron Allen]

raulavi,
You will have to declare another parameter in your SqlCommand with a
Direction of ParameterDirection.ReturnValue and use this to fetch the value
for a return. The datareader only sees the passed back resultset not the
changed parameter values. You could then use ExecuteNonQuery to call the SP
and then get the return value using the appropriate parameter value.

Ron Allen

 

[Guest]

I beleive the return var type is the problem.

it only can return one var and has to be integer. (sybase help)