Returning a string value from SP

balaji t

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

Mycroft Holmes

Error 1 DO NOT PREFIX YOUR PROCEDURES WITH SP

Never underestimate the power of human stupidity RAH

Mycroft Holmes

Error 1 DO NOT PREFIX YOUR PROCEDURES WITH SP it forces the compiler to go through ALL the system procedures (sp) Having said that try changing out to output, the only thing I can see that may be a problem.

Never underestimate the power of human stupidity RAH

Vimalsoft Pty Ltd

hi Try this

create procedure SP_abc(
@a INT,
@b VARCHAR(10) OUTPUT
)
as
begin
set @b = 'ABCD'
end

Hope 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

Colin Angus Mackay · 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

Colin Angus Mackay · 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

Vimalsoft Pty Ltd

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

Vimalsoft Pty Ltd

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

Colin Angus Mackay

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

Vimalsoft Pty Ltd

:)

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

balaji t

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

Colin Angus Mackay

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

Vimalsoft Pty Ltd

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

Mycroft Holmes

You were supplied a keyboard with your computer, USE THE BLOODY THING. Credibility -5

Never underestimate the power of human stupidity RAH

balaji t

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

balaji t

hi, Holmes stay cool.dont get tensed..Ive sent my clarification clearly.

T.Balaji

balaji t

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

SomeGuyThatIsMe

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.

Vimalsoft Pty Ltd

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 Procedure

RString = Convert.ToString(cmdselect.Parameters["@OutRess"].Value);
//accept a Geocode from the output variable

        con.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

Pete OHanlon

Please - just pick one and stick with it. Stop cross posting.

Deja View - the feeling that you've seen this post before.

My blog | My articles