Cannot set a value when initializing a variable

  • The strangest thing happened. I want to make a change in a stored procedure that I have used for ages. All of a sudden I get errors when trying to alter the sp.

    It turns out that the culprit is the following line: DECLARE @PermissiesViaGroep bit = 0

    When I change it to the code underneath, it's fine

    DECLARE @PermissiesViaGroep bit

    SET @PermissiesViaGroep = 0

    Does anyone knows what's happening here? I didn't change any settings, but I've updated SSMS.

  • what is the database compatibility level?

    Assigning a value to a variable as part of the declaration is only possible if the database is a certain level - don't remember which now.

    prior levels didn't allow direct assignment and you had to assign it after declaration.

  • 2008 introduced assignment of variable in declare statement.

  • I work with SQL Server 2019 and the compatability level is 150

  • Are you exec'ing the code from a different db with a lower compatibility?

    This really looks like a compatibility issue.  Check the compatibility level of all dbs on that instance.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I think it's a problem in SSMS. Because I just tried it in Azure Data Studio and it's no problem.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply