|
This is exactly the type of question that should not be answered here. You have not taken the time to do any research on the topic and expect the nice folks here to do your job for you. Forget about it.
Don't waste our time.
|
|
|
|
|
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
|
|
|
|
|
Error 1 DO NOT PREFIX YOUR PROCEDURES WITH SP
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
That is unlikely to even compile. There is a missing comma between the second and third line.
|
|
|
|
|
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?
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
cmdselect.Parameters.Add("@OutString", SqlDbType.VarChar, 10);
cmdselect.Parameters["@OutRess"].Direction = ParameterDirection.Output;
con.Open();
cmdselect.ExecuteNonQuery();
RString = Convert.ToString(cmdselect.Parameters["@OutRess"].Value);
con.Close();
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 budduy...its working well with normal sql conenction i want the same in LINQ
T.Balaji
|
|
|
|
|
You were supplied a keyboard with your computer, USE THE BLOODY THING.
Credibility -5
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
Holmes stay cool.dont get tensed..Ive sent my clarification clearly.
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,
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.
<edit>
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.
|
|
|
|
|
Please - just pick one and stick with it. Stop cross posting.
|
|
|
|
|
hi ,
sorry dude ..i wont repeat
T.Balaji
|
|
|
|