Question on error in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Question on error in stored procedure

Author

Topic

sandcl
Starting Member

4 Posts

Posted - 2006-04-06 : 02:24:06

Dear Experts,

I am a newbie in stored procedure and would like to know why the
stored procedure below:

create procedure sansp_cust_crd_info
@credit_bal decimal(10,2),@custnbr varchar(12), @locnname varchar(3)
as
SELECT glf_chart_acct.credit_limit as credit_limit, @credit_bal = credit_limit - (sum(amt1) from glf_ldg_acc_trans where accnbri = (SELECT accnbri from inf_cust_acc where cust_nbr=@custnbr and inv_name='ABC' and locn_name=@locnname)
FROM GLF_CHART_ACCT a, GLF_LDG_ACC_TRANS b, INF_CUST_ACCT c
WHERE b.ACCNBRI = a.ACCNBRI AND cACCNBRI = a.ACCNBRI AND c.ACCNBRI = b.ACCNBRI

return credit_limit, @credit_bal
go

is giving error:
Server: Msg 156, Level 15, State 1, Procedure sansp_cust_crd_info, Line 4
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Procedure sansp_cust_crd_info, Line 5
Incorrect syntax near the keyword 'FROM'.

Thanks

Regards,
Sandra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-06 : 02:31:35

use the OUTPUT parameter to return value and not via the RETURN.

create procedure sansp_cust_crd_info
	@credit_bal 	decimal(10,2) OUTPUT,
	@custnbr 	varchar(12), 
	@locnname 	varchar(3)
as
begin
	SELECT 	glf_chart_acct.credit_limit as credit_limit, 
		@credit_bal = glf_chart_acct.credit_limit - 	(select sum(amt1) from glf_ldg_acc_trans 
							where 	accnbri = (SELECT accnbri from inf_cust_acc 
										where 	cust_nbr 	= @custnbr 
										and 	inv_name	= 'ABC' 
										and 	locn_name	= @locnname)
						)
	FROM 	GLF_CHART_ACCT a INNER JOIN GLF_LDG_ACC_TRANS b
	ON 	b.ACCNBRI 	= a.ACCNBRI 
        INNER JOIN INF_CUST_ACCT c
	ON 	c.ACCNBRI 	= a.ACCNBRI

        return credit_limit, @credit_bal
end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-07 : 04:14:18

for creating stored procedures i usually do this:

1. create the tsql such as select statement
2. if #2 returns the recordset i need then i encapsulate it in a
"create procedure spname
as
tsql here

"
3. if i need to pass a parameter, i add one parameter at a time and replacing the variables for the parameter name

if you want to return a value instead of a recordset, my advice is to use a function if you want this value retrieved to participate in an inline call from your apps

it's not the "actual" solution but hope it helps you formulate future objects....

--------------------
keeping it simple...

sandcl
Starting Member

4 Posts

Posted - 2006-04-13 : 03:10:06

Thanks for the assistance. I've changed the stored procedure as suggested by KH but I am getting another error:

Server: Msg 141, Level 15, State 1, Procedure sansp_cust_crd_info, Line 10
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Just for reference, the stored procedure has been changed like below:
create procedure sansp_cust_crd_info
@credit_bal decimal(10,2) OUTPUT,
@custnbr varchar(12),
@locnname varchar(3)
as
begin
SELECT glf_chart_acct.credit_limit as credit_limit, @credit_bal = glf_chart_acct.credit_limit - (select sum(amt1) from glf_ldg_acc_trans where accnbri = (SELECT accnbri from inf_cust_acc where cust_nbr = @custnbr and inv_name = 'ABC' and locn_name = @locnname))
FROM GLF_CHART_ACCT a INNER JOIN GLF_LDG_ACC_TRANS b ON b.ACCNBRI = a.ACCNBRI INNER JOIN INF_CUST_ACCT c ON c.ACCNBRI = a.ACCNBRI
return credit_limit, @credit_bal
end

I have tried the select statements individually and they return the value needed but once I put all of them together, I seem to have a problem :(

Regards,
Sandra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 03:12:40

remove this line. Should use OUTPUT paremeter to return value to calling procedure

return credit_limit, @credit_bal

sandcl
Starting Member

4 Posts

Posted - 2006-04-13 : 04:08:13

Hi KH,

How do I output a variable (@credit_bal) and a field (credit_limit) in a single output statement?

Regards,
Sandra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 04:21:43

create procedure sansp_cust_crd_info
@credit_bal decimal(10,2) OUTPUT,
@credit_limit decimal(10,2) OUTPUT,
@custnbr varchar(12),
@locnname varchar(3)
as
begin
    SELECT @credit_limit = glf_chart_acct.credit_limit, 
           @credit_bal   = glf_chart_acct.credit_limit -  .....
end

sandcl
Starting Member

4 Posts

Posted - 2006-04-17 : 04:25:03

Hi KH,

Thanks for all the assistance. The stored procedure is done :)

Regards,
Sandra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources