none
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    Question

  • what about this SQL Query is making it return the error?

    declare @city varchar(2)
    SELECT TOP 1 @city=city, region  FROM dbo.ip
    inner join dbo.locations on dbo.ip.locationID = dbo.locations.locationID
     WHERE dbo.ip.startIP >= '2922867218' ORDER BY dbo.ip.startIP  


    - 10spotdomain for all your domain and domain related needs
    Monday, August 01, 2011 1:43 PM

Answers

  • Remove region column from the list or assign a variable to region

    declare @city varchar(2),@region varchar(10)
    SELECT TOP 1 @city=city 
    @region=region FROM dbo.ip

    inner join dbo.locations on dbo.ip.locationID = dbo.locations.locationID
     WHERE dbo.ip.startIP >= '2922867218' ORDER BY dbo.ip.startIP  


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Vishal Gajjar Monday, August 01, 2011 2:09 PM
    • Marked as answer by Peja Tao Wednesday, August 03, 2011 2:29 AM
    Monday, August 01, 2011 1:45 PM

All replies

  • Remove region column from the list or assign a variable to region

    declare @city varchar(2),@region varchar(10)
    SELECT TOP 1 @city=city 
    @region=region FROM dbo.ip

    inner join dbo.locations on dbo.ip.locationID = dbo.locations.locationID
     WHERE dbo.ip.startIP >= '2922867218' ORDER BY dbo.ip.startIP  


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Vishal Gajjar Monday, August 01, 2011 2:09 PM
    • Marked as answer by Peja Tao Wednesday, August 03, 2011 2:29 AM
    Monday, August 01, 2011 1:45 PM
  • So in the select statement you can't mix a field to return with the assignment of a variable.

    - 10spotdomain for all your domain and domain related needs
    Wednesday, August 03, 2011 2:48 AM
  • So in the select statement you can't mix a field to return with the assignment of a variable.

    - 10spotdomain for all your domain and domain related needs

    Exactly !


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, August 03, 2011 3:17 AM