SQL Variable - Result as SomeName

Posted on 2007-07-24
Last Modified: 2010-03-19
I have this query:

Create Mytable
(somefield int)

DECLARE @Num numeric (10,10)

SELECT top 1 @Num = somefield

I always get (No Column Name) when I get my result.

SELECT top 1 @Num = somefield as SomeCoolName   // This doesn't work.

Any ideas?
Question by:Endelm
    LVL 15

    Accepted Solution

    When you set a variable in a select statement it won't show up in the resultset.

    You can do this:

    SELECT top 1 @Num = somefield from... where...

    followed by

    select @num SomeCoolName

    and that should work fine
    LVL 10

    Expert Comment

    When you specify a local variable(@Num) the value is set for the variable.  You can alias a field with similar syntax:

    SELECT top 1 SomeCoolName  = YourField
    from YourTable

    OR another way to alias a field:

    SELECT top 1 Field AS SomeCoolName  
    from YourTable

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now