Returning a string value from SP
-
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
hi Try this
create procedure SP_abc(
@a INT,
@b VARCHAR(10) OUTPUT
)
as
begin
set @b = 'ABCD'
endHope it 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
modified on Monday, August 4, 2008 3:29 AM
-
hi Try this
create procedure SP_abc(
@a INT,
@b VARCHAR(10) OUTPUT
)
as
begin
set @b = 'ABCD'
endHope it 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
modified on Monday, August 4, 2008 3:29 AM
That is unlikely to even compile. There is a missing comma between the second and third line.
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
-
hi Try this
create procedure SP_abc(
@a INT,
@b VARCHAR(10) OUTPUT
)
as
begin
set @b = 'ABCD'
endHope it 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
modified on Monday, August 4, 2008 3:29 AM
How is this any different from the OP's SQL Code?
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
-
That is unlikely to even compile. There is a missing comma between the second and third line.
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
Thanks i Fixed it
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
-
How is this any different from the OP's SQL Code?
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
I dont understand your Question, i just took the Output keywork to the End of the declaration line.
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
-
I dont understand your Question, i just took the Output keywork to the End of the declaration line.
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
Ah, so you did! :)
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
-
Ah, so you did! :)
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
:)
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
-
:)
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
-
i cudnt get wht u ppl say..is it working 5n?can u tell me whether ure able to access the value at the front end?
T.Balaji
balaji.t wrote:
cudnt
balaji.t wrote:
wht
balaji.t wrote:
ppl
balaji.t wrote:
5n
balaji.t wrote:
u
balaji.t wrote:
ure
None of these words are in my dictionary. I don't understand what you are saying.
Recent blog posts: *SQL Server / Visual Studio install order *Installing SQL Server 2005 on Vista *Tip of the Day - SysInternals * Meme My Blog
-
i cudnt get wht u ppl say..is it working 5n?can u tell me whether ure able to access the value at the front end?
T.Balaji
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
-
i cudnt get wht u ppl say..is it working 5n?can u tell me whether ure able to access the value at the front end?
T.Balaji
You were supplied a keyboard with your computer, USE THE BLOODY THING. Credibility -5
Never underestimate the power of human stupidity RAH
-
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