Variable assignment is not allowed in a statement

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 2012 Forums

Transact-SQL (2012)

Variable assignment is not allowed in a statement

Author

Topic

SacSmi
Starting Member

3 Posts

Posted - 2015-02-05 : 10:51:33

I am not able to find what is wrong with my query. I am getting this error using UNION in my function.

Please help

ALTER FUNCTION [dbo].[GetPermitFee] ( @PermitRequestID INT, @PermitFeeType NVARCHAR(25))
RETURNS MONEY
AS
BEGIN
DECLARE @PermitFeeAmt MONEY
--DECLARE @PermitFeeAmt1 Money = 0

SELECT TOP 1 @PermitFeeAmt = Amount
FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
PermitRequest.RequestType = 0 AND
LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType) AND
PermitRequest.ApplicationRecievedDate BETWEEN PermitFeePlan.EffectiveDate AND PermitFeePlan.ExpireDate

UNION

SELECT TOP 1 @PermitFeeAmt = Amount
FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
PermitRequest.RequestType = 1 AND
LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType)

RETURN @PermitFeeAmt

END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 12:05:11

This error? (Be sure to post your error messages!)

quote:

Msg 10734, Level 15, State 1, Procedure GetPermitFee, Line 18
Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.

There are several things wrong here:

1. remove WITH (NOLOCK). You can get dirty reads. It appears that you are reading financial amounts. Dirty reads should not be permitted.
2. It appears you are trying to assign the varialb @PermitFeeAmt twice (and that's what the error is all about). What do you really want to do?
3. Why are you using the LOWER function? Unless you are running a case-sensitive collation (are you?) it is meaningless and means your query is not SARG-able.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-05 : 13:08:15

[code]
ALTER FUNCTION [dbo].[GetPermitFee] ( @PermitRequestID INT, @PermitFeeType NVARCHAR(25) )
RETURNS MONEY
AS
BEGIN
RETURN (
SELECT TOP (1) Amount
FROM (
SELECT TOP (1) Amount, 1 AS sequence
FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
PermitRequest.RequestType = 0 AND
LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType) AND
PermitRequest.ApplicationRecievedDate BETWEEN PermitFeePlan.EffectiveDate AND PermitFeePlan.ExpireDate
UNION ALL
SELECT TOP (1) Amount, 2 AS sequence
FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
PermitRequest.RequestType = 1 AND
LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType)
) AS derived
ORDER BY sequence
)
END --FUNCTION

GO [/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-05 : 15:12:41

[code]ALTER FUNCTION dbo.GetPermitFee
(
@PermitRequestID INT,
@PermitFeeType NVARCHAR(25)
)
RETURNS MONEY
AS
BEGIN
RETURN (
SELECT TOP(1) @PermitFeeAmt = pfp.Amount
FROM dbo.PermitFeePlan AS pfp WITH (NOLOCK)
INNER JOIN dbo.PermitRequest AS pr WITH (NOLOCK) ON pr.PermitTypeID = prp.PermitTypeID
AND pr.PermitRequestID = @PermitRequestID
AND pr.RequestType IN (0, 1)
WHERE LOWER(pfp.PermitFeeType) = LOWER(@PermitFeeType)
ORDER BY CASE
WHEN pr.RequestType = 0 AND pr.ApplicationRecievedDate BETWEEN pfp.EffectiveDate AND pfp.[ExpireDate] THEN 0
WHEN pr.RequestType = 1 THEN 1
ELSE 2
END
)
END[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SacSmi
Starting Member

3 Posts

Posted - 2015-02-05 : 15:49:04

Thank You so much!!! It just worked as i expected.

quote:

Originally posted by ScottPletcher


ALTER FUNCTION [dbo].[GetPermitFee] ( @PermitRequestID INT, @PermitFeeType NVARCHAR(25) )
RETURNS MONEY
AS
BEGIN
RETURN (
    SELECT TOP (1) Amount
    FROM (
        SELECT TOP (1) Amount, 1 AS sequence
        FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
             PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
        WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
            PermitRequest.RequestType = 0 AND
            LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType) AND
            PermitRequest.ApplicationRecievedDate BETWEEN PermitFeePlan.EffectiveDate AND PermitFeePlan.ExpireDate 
        UNION ALL
        SELECT TOP (1) Amount, 2 AS sequence 
        FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
             PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
        WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
	         PermitRequest.RequestType = 1 AND
	         LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType)
	) AS derived
	ORDER BY sequence
)
END --FUNCTION

GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 16:38:45

you still need to get rid of NOLOCK!

SacSmi
Starting Member

3 Posts

Posted - 2015-02-10 : 13:10:05

Thank You so much!!! It just worked as i expected.

quote:

Originally posted by ScottPletcher


ALTER FUNCTION [dbo].[GetPermitFee] ( @PermitRequestID INT, @PermitFeeType NVARCHAR(25) )
RETURNS MONEY
AS
BEGIN
RETURN (
    SELECT TOP (1) Amount
    FROM (
        SELECT TOP (1) Amount, 1 AS sequence
        FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
             PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
        WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
            PermitRequest.RequestType = 0 AND
            LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType) AND
            PermitRequest.ApplicationRecievedDate BETWEEN PermitFeePlan.EffectiveDate AND PermitFeePlan.ExpireDate 
        UNION ALL
        SELECT TOP (1) Amount, 2 AS sequence 
        FROM PermitFeePlan WITH (NOLOCK) INNER JOIN
             PermitRequest WITH (NOLOCK) ON PermitFeePlan.PermitTypeID = PermitRequest.PermitTypeID
        WHERE PermitRequest.PermitRequestID = @PermitRequestID AND
	         PermitRequest.RequestType = 1 AND
	         LOWER(PermitFeePlan.PermitFeeType) = LOWER(@PermitFeeType)
	) AS derived
	ORDER BY sequence
)
END --FUNCTION

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources