Error message on 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

SQL Server Development (2000)

Error message on Stored procedure

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-03-25 : 16:55:58

I am try to run the below procedure. but, I am keep getting error message says:

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

Could anyone advise me what I am doing wrong?.

CREATE PROCEDURE proc_resv_change1(@begindate DATETIME, @enddate DATETIME ) AS

Begin
Declare @totnetchange decimal(10,2)
select
@totnetchange = sum(loss_amt),
@begindate as begindate,
@enddate as enddate
from
claim, location_xref, loss, clmxref, location
WHERE claim.ref_location=location_xref.location_key AND
claim.claim_key=clmxref.xref_key AND
location_xref.ref_loc2=location.location_key AND
location_xref.ref_loc1=32138 AND
claim.claim_key=loss.loss_claim AND
loss.loss_type='R' AND
loss.loss_enterdate BETWEEN @begindate AND @enddate

END

Begin
SELECT
@begindate as begindate,
@enddate as enddate,
clm_incdate,
clm_nbr,
clm_clmt,
MAX(location.name) AS Dealership,
MAX(clmxref.xref_totres + clmxref.xref_tottobe ) as TotRes,
MAX(clmxref.xref_totpmt - clmxref.xref_totreim) AS TotPaid,
MAX(clmxref.xref_totinc) AS TotInc,
SUM(loss_amt) AS ResvChange,
MAX(loss_enterdate) AS LastChngDate
FROM claims, location_xref, loss, clmxref, location
WHERE claim.ref_location=location_xref.location_key AND
claim.claim_key=clmxref.xref_key AND
location_xref.ref_loc8=location.location_key AND
location_xref.ref_loc1=87655 AND
claim.claim_key=loss.loss_claim AND
loss.loss_type='R' AND
loss.loss_enterdate BETWEEN @begindate AND @enddate
GROUP BY clm_nbr, clm_clmt, clm_incdate
HAVING ABS(SUM(loss_amt))>=4567 ORDER BY clm_clmt, clm_incdate

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 17:03:20

Well you are assinging values to variables while also returning a record set, which is not allowed. So get your values in one statement, then do the record set in another select.

Example:
SELECT @var1 = column1, @var2 = column2 FROM Table1 WHERE column3 = @var5

SELECT column4, column5 FROM Table1 WHERE column6 IS NOT NULL

Tara

Edited by - tduggan on 03/25/2003 17:09:14

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-03-25 : 17:04:52

I think that you can't do this:
@totnetchange = sum(loss_amt)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 17:06:55

You definitely can do that (@totnetchange = sum(loss_amt)). I just created a stored procedure yesterday that does that.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-25 : 17:08:08

@begindate as begindate

isn't assigning a value to the variable @begindate.

Maybe:

@beginDate = MyColumn

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 17:09:48

Good catch SamC, didn't even see that!

But that isn't the only problem:

declare @int int

select @int = column1, column2
from Table
where column3 = 'some value'
print @int

You can not do this. You have to assign the variable in one statement and then do another select to get the record set.

Tara

Edited by - tduggan on 03/25/2003 17:12:11

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-25 : 17:24:46

I wasn't sure (and didn't spend enough time) to guess whether the point of the procedure was returning a recordset.

It looks like the first query is supposed to assign values to the passed parameters (which are input only) and use these values in the 2nd query to return a recordset.. - Maybe -

Hope jun1975 fills us in on the final solution.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources