November 18, 2011 at 9:52 am
Is there a way to write this without an intermediate variable?
CREATE FUNCTION dbo.CreditOverlap
(
@ProgramID1INT,
@ProgramID2INT
)
RETURNS BIT
AS
BEGIN
DECLARE
@CountINT
SET @Count =
(
SELECT COUNT( Credit_ID )
FROM
(
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID1
INTERSECT
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID2
) AS ResultSet
)
-- Return the result of the function. We need 2 or better to return true
RETURN ( CASE WHEN @Count > 1 THEN 1 ELSE 0END )
END
I had tried below, but get this error - Msg 444, Level 16, State 2, Procedure CreditOverlap1, Line 9
Select statements included within a function cannot return data to a client.
CREATE FUNCTION dbo.CreditOverlap
(
@ProgramID1INT,
@ProgramID2INT
)
RETURNS BIT
AS
BEGIN
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID1
INTERSECT
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID2
-- Return the result of the function. We need 2 or better to return true
RETURN ( CASE WHEN @@ROWCOUNT > 1 THEN 1 ELSE 0END )
END
November 18, 2011 at 10:10 am
The function doesn't know the selects are only used to get a rowcount, so tries to return the results -> error.
Something like
CREATE FUNCTION dbo.CreditOverlap
(
@ProgramID1INT,
@ProgramID2INT
)
RETURNS BIT
AS
BEGIN
DECLARE @Count INT
SET @Count =
SELECT COUNT(Credit_ID)
FROM
(
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID1
INTERSECT
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID2
) z
-- Return the result of the function. We need 2 or better to return true
RETURN ( CASE WHEN @@ROWCOUNT > 1 THEN 1 ELSE 0END )
END
There's probably a better way to write this though 🙂
November 18, 2011 at 10:20 am
Ha sorry I've just done your original function again. Home time I think!
Maybe this:
RETURN ( CASE WHEN
(SELECT COUNT(Credit_ID)
FROM
(
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID1
INTERSECT
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID2
) z
) > 1 THEN 1 ELSE 0 END )
END
November 18, 2011 at 10:24 am
CREATE FUNCTION dbo.CreditOverlap
(@ProgramID1 INT
,@ProgramID2 INT)
RETURNS BIT
AS
BEGIN
RETURN (SELECT CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END
FROM
(SELECT Credit_ID FROM DIP.dbo.ProgramCredit WHERE Program_ID = @ProgramID1
INTERSECT
SELECT Credit_ID FROM DIP.dbo.ProgramCredit WHERE Program_ID = @ProgramID2) AS x)
END
GO
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 18, 2011 at 10:25 am
Gazareth (11/18/2011)
Ha sorry I've just done your original function again. Home time I think!Maybe this:
RETURN ( CASE WHEN
(SELECT COUNT(Credit_ID)
FROM
(
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID1
INTERSECT
SELECT Credit_ID
FROM DIP.dbo.ProgramCredit
WHERE Program_ID = @ProgramID2
) z
) > 1 THEN 1 ELSE 0 END )
END
Ha, I should know by now to refresh my screen before posting... 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 18, 2011 at 10:28 am
I saved time by not using the code tags 😀
Bad form by me there!
And of course if Credit_ID is nullable then they're not the same code!
November 18, 2011 at 11:24 am
Jason,
That worked perfectly - it seems more syntactically complete than my solution, even though they perf out 50/50 in plan comparison.
Doug
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply