Why event though declaration giving error must declare the scalar variable in SQL function?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I am altering `function` like:

SQL

ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY)
RETURNS TypePreviousListingResult
AS
BEGIN
    DECLARE @tbl_ListingDateDetails TypePreviousListingResult
    RETURN @tbl_ListingDateDetails
END
GO

But throwing error as:

SQL

Msg 137, Level 16, State 1, Procedure fn_CalculateListing, Line 6
   Must declare the scalar variable "@tbl_ListingDateDetails".

Why this giving error and asking for declaration eventhough I have declared that before using that variable?

Posted 24-Jan-16 19:07pm

Member 11774876

Updated 24-Jan-16 19:09pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-01-24T21:44:00

That isn't your whole function - if nothing else it doesn't give a value to the variable so the RETURN is a little pointless! :laugh:
Without the whole function it's difficult to be sure (and since we don't have your types, we can't test your code exactly) but the most common reason for this is that your function contains one or more GO commands. These "break" the code and anything declared above the GO is not available below:

SQL

DECLARE @X INT
SET @X = 3
SELECT @X
GO
SELECT @X

Will give the error message you describe.