how to return a string variable in sp ?

balaji t

hi all, i ve created a sp which returns a output variable of type varchar.In dotnet ive given like this EmployeeDataContext dc = new EmployeeDataContext (); string employeeid = null; string outemployeeid = Convert.Tostring(dc.sp_empl(empname,deptname,ref employeeid)); return outemployeeid ; The stored procedure is executing correctly and returns a varchar value like EMP0001. Whereas while assigning to outemployeeid it is throwing runtime error like cannot convert int to string. Can any1 help me ?

T.Balaji

Judah Gabriel Himango

What does dc.sp_empl return? That's what your converting to a string, not the employeid reference field.

Life, family, faith: Give me a visit. From my latest post: "The themes and truths of the Jewish holidays follow God's complete plan for this world. They are the root from which Christianity sprang and the historical reasons the church had for leaving them behind were unsound." Judah Himango

balaji t

hi, i found out the problem.actually its not LINQ issue.Issue is with sql server ..The sp is not returning a string variable as output variable ..can u let me know how to return a string variable from sp in sql?A function returns a string variable whereas a sp doesnt return.y? for e.g I have created a sp create procedure sp_abc( @a INT @b out VARCHAR(10) ) as begin set @b = 'ABCD' end How to get the value of b in the front end?

T.Balaji

Brady Kelly

Add a string output parameter for your @b to the command object you are using to execute the proc.

Elusive problem with IIS7 static content.

balaji t

hi, Even after giving @b in the front end im unable to access a string variable.i think generally its not possible 2 return a string as output in SP in sql server.Dont u find this as smething strange and shocking?

T.Balaji

Brady Kelly

No, I find you must be doing something wrong, which is not strange and shocking; it happens to all of us quite often. This works perfectly for me:

        DataClasses1DataContext dc = new DataClasses1DataContext("Data Source=ABRAXAS;Initial Catalog=Sales;Integrated Security=True");
        string n = "";
        dc.apCustomer\_GetName(1, ref n);
        MessageBox.Show(n);

Elusive problem with IIS7 static content.

balaji t

hi, thanx buddy...its working well with normal sql connection parameters but i want the same in LINQ ..when i call like this it displays zero for variable 'b'. kindly help me.how to incorporate in LINQ?

T.Balaji

Brady Kelly

I don't know what you mean. I just showed you how do do it in LINQ! The stored proc returns a value using a ref parameter. You are ignoring the ref parameter completely, and trying to use a non-existent return value.

string employeeid = null;
string outemployeeid = Convert.Tostring(dc.sp_empl(empname,deptname,ref employeeid));

Why are you throwing employeeid away? It's not just there to look nice.

Unscrambling Eggs: Decompiling ASP.NET

balaji t

hi, Im not getting the desired result after giving Convert.tostring.i tried tat also. Ill put my doubt clearly.Can u tell me whether it is possible to return a string value as output in the front end using a Stored Procedure? Ive created a stored procedure like this CREATE PROCEDURE [dbo].[StoredProcedure1] ( @a int, @b varchar(10) output ) AS BEGIN set @b = 'RE00001' --set @b = 20 --return @b END in LINQ ------- i call the sp like this DC1DataContext obj = new DC1DataContext(); string b = null; string c = obj.StoredProcedure1(1, ref b).ToString(); I face 2 issues 1.if I give return @b(thats y commented) then i get error since sp doesnt return a string 2.if i dont give return then in the code behind i get 0 as the value of c. can u let me know whether is it possible to return a string using SP? if so how?

T.Balaji

Brady Kelly

I have already told you exactly how to do this. You obviously have no clue what a 'ref' parameter is for, as despite my advice above, you continue to ignore it. What the hell do you think the variable 'string b' is there for? It doesn't look nice, so it must be there for a reason, which you continue to ignore.

Unscrambling Eggs: Decompiling ASP.NET

balaji t

hi kelly, relax urself!! do 1 thing ive sent both the sp and the front end code. try it out and let me know.im not ignoring ref parameter.if u have to make changes in d code pls do it for me dude. CREATE PROCEDURE [dbo].[StoredProcedure1] ( @a int, @b varchar(10) output ) AS BEGIN set @b = 'RE00001' --set @b = 20 --return @b END in LINQ ------- i call the sp like this DC1DataContext obj = new DC1DataContext(); string b = null; string c = obj.StoredProcedure1(1, ref b).ToString();

T.Balaji

Brady Kelly

DC1DataContext obj = new DC1DataContext();
string b = null;
string c = obj.StoredProcedure1(1, ref b).ToString();
Console.WriteLine(b);

I really can't do any more for you.

Unscrambling Eggs: Decompiling ASP.NET

balaji t

hi kelly Thanx for ur timely help.i understood now..il try and let u know

T.Balaji