Problem for Calling A Stored Procedure, Please help.

xxd


I am writing a Stored Procedure for other server (using C++ to receive the output values) as below, where @Total £¬ @balance£¬ @A are output values


create proc [MaxTime]
@number varchar(30),
@numbera varchar(30),
@numberb varchar(30)

as
begin
declare @balancefloat
declare @table varchar(20)
declare @freetotal varchar(20)
declare @SQL nvarchar(4000)
declare @A float
declare @Total float
select @balance = balance, @table = table, @freetotal = freetotal
from info where number = @number


SELECT @SQL = 'select @A = A FROM' + @table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
exec sp_executesql @sql,N'@Afloat output',@Aoutput

set @Total = @balance / @A + @freetotal

end
return

but the server got nothing, then i wrote another Stored Procedure below:

create proc [MaxTime]
(@number varchar(30),
@numbera varchar(30),
@numberb varchar(30),
@Total float output,
@balance float output,
@A float output)
--here is the only modified i made

as
begin
declare @table varchar(20)
declare @freetotal varchar(20)
declare @SQL nvarchar(4000)
select @balance = balance, @table = table, @freetotal = freetotal
from info where number = @number


SELECT @SQL = \'select @A = A FROM\' + @table
+ \' WHERE LEFT(code, 1) = \' + LEFT(@incomingcode, 1)
+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @incomingcode+ \') = 1\' +
\' ORDER BY LEN(code) DESC\'
exec sp_executesql

--@sql,N\'@Afloat output\',@Aoutput --im not sure about this line

set @Total = @balance / @A + @freetotal

end
return

system error message£º
Msg 170, Level 15, State 1, Procedure MaxTime, Line 11
Line 11: Incorrect syntax near \'@Total \'.
Msg 137, Level 15, State 1, Procedure MaxTime,, Line 21
Must declare the variable \'@Total \'.

Please help, appreciated

HuntTsai

Hi,xxd

If you wanna get data from database without using dataset.
maybe you can try function.

By the below case, the SQL substring can't add the local varity into the sentance.
It was because the "exec sp_executesql " will be the other Transcation.

xxd

Hi, HutTsai:

thanks for ur reply, as u mentioned about dataset, did you mean in SQL or on the other server side

and for the 'exec sp_executesql', it was only for excuting the dynamic@sql to get those values that i need to calculate in set @Total = @balance / @A + @freetotal.

Thanks

HuntTsai

Hello xxd

as the requirement,I think you need to clac the value @total return for Client that call sp [MAXTIME]

this is the sample code I wrote, try it. modi by your sample code.
if you need the detail for this. Let me know. :)

why "cursor"
my target was get the return value From dynamic-SQLstring,
using the cursor delcare in global.
then fetch its content for out return value.

it's a better method.

Reference: Stored Procedure,Cursor;

Cheers,
Hunt

 

/* Sample Code by Hunt Begin*/

create proc [MaxTime]
(@number varchar(30),
@numbera varchar(30),
@numberb varchar(30),
@Total float output)
--here is the only modified i made

as
begin
declare @table varchar(20)
declare @freetotal varchar(20)
declare @SQL nvarchar(4000)
select @balance = balance, @table = table, @freetotal = freetotal
from info where number = @number

set @sql =
' Declare tmpcur cursor for '
' select @A = A FROM' + @table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'

exec (@sql)
open tmpcur;
Fetch Next From tmpcur into @A;
close tmpcur;
Deallocate tmpcur;

select @total = @balance / @A + @freetotal

/*Sample Code by Hunt End*/

xxd

Thanks Hunt:

1st, there are something wrong for this part below:
' Declare tmpcur cursor for '
' select @a = a FROM'
please advise me that how to modify it, coz this is my 1st time to see write cursor this way :)
and for my case, i don't really need to use cursor, coz the ' select @A = A FROM' + @table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
will only back me one set of data. anyway.

based on your code, i modified mine as below:
alter proc [MaxTime]
(@number varchar(30),
@numbera varchar(30),
@numberb varchar(30),
@Total float output,
@balance float output)

as
begin
declare @table varchar(20)
declare @freetotal varchar(20)
declare @SQL nvarchar(4000)
select @balance = balance, @table = table, @freetotal = freetotal
from info where number = @number


SELECT @SQL = \'select @A = A FROM\' + @table
+ \' WHERE LEFT(code, 1) = \' + LEFT(@incomingcode, 1)
+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @incomingcode+ \') = 1\' +
\' ORDER BY LEN(code) DESC\'
exec sp_executesql

--don't know where did i get those \ from

set @Total = @balance / @A + @freetotal

end
return @Total
return @balance

however, got error message like:
Msg 201, Level 16, State 4, Procedure MaxTime, Line 0
Procedure 'MaxTime' expects parameter '@Total', which was not supplied.

but one step closed i think

Cheers.

HuntTsai

ok,the important checkpoint on sys.procedure -> "exec sp_executesql"
check with my sample.
you'll get what you want.

Hint: as you set output varity with value,you shouldn't return any value. it's useless.
Reference with Books Online "sp_executesql"

Cheers,
Hunt

alter proc [sp_test1]
(@number varchar(30),
@numbera varchar(30),
@numberb varchar(30),
@Total float output,
@balance float output)
as
declare @table varchar(20)
declare @freetotal varchar(20)
declare @A float
declare @SQL nvarchar(4000);
declare @SQLparm nvarchar(500);

set @table ='car'
set @balance = 3.0
set @freetotal = 2.0

--the section below will be the most important.
set @SQLparm = N'@A float output'
select @sql = ' select @A = 2.0 FROM ' + @table
exec sp_executesql @sql,@SQLparm ,@A output
set @Total = @balance / @A + @freetotal
go

declare @tot float
declare @free float
exec [sp_test1] '1','2','3',@tot output,@free output
print '---'
print @tot
print @free
go

xxd

thank you for writing all of those code.

however, why i need those below
declare @tot float
declare @free float
exec [sp_test1] '1','2','3',@tot output,@free output

and i think it should be like exec [sp_test1] '1','2','3', @Total output, @balance output

otherwise, in fact, there are two clients are calling this procedure 1 of them was alright for getting the outputs, the other one doesn't, it is an application, in this application all i can do is to identify three input parameters,
which are @number varchar(30),
@numbera varchar(30),
@numberb varchar(30),
and there are no more space for @Total output and @balance output.

so that when this application pass the exec command to sql server, it would be like exec [sp_test1] '1','2','3' rather than exec [sp_test1] '1','2','3', @Total output, @balance output

hope i explained clearly.

Cheers

HuntTsai

HuntTsai

this thread got a little problem,i can't post any word on it.

try put default value behind the varity.
@total float =0 out,@balance float =0 out

in this sample,you can call sp with no output param.
check it.

Cheer.

xxd

hi thanks,
i'll try it tomorrow, let you know then

xxd

did you mean that
exec [sp_test1] '1','2','3', @Total float = 0 output, @balance float = 0 output


or exec [sp_test1] '1','2','3','0','0'

it works fine when i am using exec [sp_test1] '1','2','3','0','0' for outputing values for the server (C++) or i do not even need to use the output value, it also works. However i still can not set the @total float =0 out,@balance float =0 out for the application case, and that application tells me that it did not get anything.

so is there any better way to solve it out

many thx

HuntTsai

You have to put the default value when you create stored procedure.
as below

Create Proc [MAXTime] (@numbera varchar(20),@numberb varchar(20),@numberc varchar(20),
@total float =0 out,@balance float =0 out);

after you alter the sp,you can call the proc by
exec [MAXTime] '1','2','3'
or
exec [MAXTime] '1','2','3',@total out,@balance out

try it.

xxd

hi HuntTsai:

thanks for that, by now i do not thin k the output will solve my problem, after all i realised that there are 3 types of output function of sproc:

1select @something
2@something output
3return@something and return(0)

all of above are the same(of course not) or for some special using
thanks

HuntTsai

I think I don't know what's your original requirement(or question).
maybe it could be describe more detail. On basiclly, the output Question seems like be sloved.

anyway,when we use stored procedure, it was defined for regular process.
and the return types that you said in last post were the normal method.
(I add the 4th as fire_trigger).

1. select @something
2. @something output
3. return@something and return(0)
4. sometimes we also set it up as another type of trigger.

Best Regrads. :)

xxd

Hi HuntTsai:

Thanks a lot.