Returning a string value from SP
-
I really dont Understand what you are saying. Do you speak English?
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
hi folks, Ill put my doubt clearly.Wht i sent earlier is a chat slang :)...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
-
You were supplied a keyboard with your computer, USE THE BLOODY THING. Credibility -5
Never underestimate the power of human stupidity RAH
-
You were supplied a keyboard with your computer, USE THE BLOODY THING. Credibility -5
Never underestimate the power of human stupidity RAH
I CAN DO NOTHING IF U DONT UNDERSTAND THIS :( hi folks, Ill put my doubt clearly.Wht i sent earlier is a chat slang ...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
-
I CAN DO NOTHING IF U DONT UNDERSTAND THIS :( hi folks, Ill put my doubt clearly.Wht i sent earlier is a chat slang ...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
in sql server any version up to 2000(havnt played with anything past that..yet) stored procedures, typically prefixed with usp_(user stored procedure), can only return INT values so that you can check if the work was completed or had problems. however if you need something else you can get it the same way you SELECT what you want from any other place in the database. it works very well in sql server, however it wont work in oracle to my knowledge.. if you dont like my first idea you can always try an output parameter.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
-
hi folks, Ill put my doubt clearly.Wht i sent earlier is a chat slang :)...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
hi man **balaji.t Wrote:**Can u tell me whether it is possible to return a string value as output in the front end using a Stored Procedure? Yes its Possible. In your Situation just look at some of my artcles i have done that. if you are using C#, just do the Following
/*Declare an output Parameter, The Dimenstion should be the same as the
one you have in your table.*/
cmdselect.Parameters.Add("@OutString", SqlDbType.VarChar, 10);cmdselect.Parameters["@OutRess"].Direction = ParameterDirection.Output;
//The Following line you are going to retrieve what has been returned.
con.Open(); //open connection cmdselect.ExecuteNonQuery(); //Execution of a Stored ProcedureRString = Convert.ToString(cmdselect.Parameters["@OutRess"].Value);
//accept a Geocode from the output variablecon.Close();//Closing the Connection/* Remember the Output Variable name in your Stored Procedure ,should be the same as the one you declare in C# code. */
Hope this Helps
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
-
hi folks, A SP in sql server is not returning a string variable as output variable ..can any1 let me know how to return a string variable from SP in sql server?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
Please - just pick one and stick with it. Stop cross posting.
Deja View - the feeling that you've seen this post before.
-
Please - just pick one and stick with it. Stop cross posting.
Deja View - the feeling that you've seen this post before.
-
in sql server any version up to 2000(havnt played with anything past that..yet) stored procedures, typically prefixed with usp_(user stored procedure), can only return INT values so that you can check if the work was completed or had problems. however if you need something else you can get it the same way you SELECT what you want from any other place in the database. it works very well in sql server, however it wont work in oracle to my knowledge.. if you dont like my first idea you can always try an output parameter.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
hi, thanx dude..the issue is even i use output parameter the sql server is not returning its returning 1ly int values.i think its an issue with sql server .. i want u ppl 2 try this and let me knw..if u also face the same issue we will inform microsoft about this and ask them the solution.
T.Balaji
-
hi man **balaji.t Wrote:**Can u tell me whether it is possible to return a string value as output in the front end using a Stored Procedure? Yes its Possible. In your Situation just look at some of my artcles i have done that. if you are using C#, just do the Following
/*Declare an output Parameter, The Dimenstion should be the same as the
one you have in your table.*/
cmdselect.Parameters.Add("@OutString", SqlDbType.VarChar, 10);cmdselect.Parameters["@OutRess"].Direction = ParameterDirection.Output;
//The Following line you are going to retrieve what has been returned.
con.Open(); //open connection cmdselect.ExecuteNonQuery(); //Execution of a Stored ProcedureRString = Convert.ToString(cmdselect.Parameters["@OutRess"].Value);
//accept a Geocode from the output variablecon.Close();//Closing the Connection/* Remember the Output Variable name in your Stored Procedure ,should be the same as the one you declare in C# code. */
Hope this Helps
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
-
hi, thanx dude..the issue is even i use output parameter the sql server is not returning its returning 1ly int values.i think its an issue with sql server .. i want u ppl 2 try this and let me knw..if u also face the same issue we will inform microsoft about this and ask them the solution.
T.Balaji
well what the output parameter is defined as determines what kind of values it gets out...or if you do a simple SELECT @varName at the end of your sp you will be able to get any value from an sp, just remmeber that each SELECT generates a different result set so if you use a reader in c# to read them you'll have to read a value then move to the next result set. I know its not a problem with Sql server i've done this several dozens of times for my applications including a few yesterday(testing things, usually not production code) Sql Server has several problems, rollup doesnt work correctly sometimes, count(*) returns one when it should be 0, a few others, but returning values from a sp is not one of them. i was re reading your posts a second ago, and i've never used this method of retrieving values from anything..
balaji.t wrote:
DC1DataContext obj = new DC1DataContext(); string b = null; string c = obj.StoredProcedure1(1, ref b).ToString();
i always used SqlCommands and SqlReaders, and opened and closed my own SqlConnections. To be honest i'm not sure how your method actually works..i can guess at the obvious parts, but its entirely possible that your problem is with how you're running the sp and getting your data back, and not with SqlServer. also if you initialize strings with null, you can get null reference exceptions, however if you initialize them with "" you wont get the exceptions, and you'll just have to check b == "" or string.Empty. which works unless you're treating "" as a valid value for something.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.