Set variable in SQL query [duplicate]

Question

I am trying to set variable but I get Incorrect syntax near '='..

    DECLARE @name NVARCHAR(100) = 'Some name'
    DECLARE @id INT
    EXECUTE ('SET ' + @id + ' = (SELECT mm.mmID FROM dbo.measurem AS mm
        WHERE mm.placeName = ''' + @name + ''')')
    PRINT @id

If I try like this:

SET @id= (SELECT mm.mmID FROM dbo.measurem AS mm
          WHERE mm.placeName = @name)

I get must declare variable @name although I have it as my input store procedure param.

You appear to be using Microsoft SQL Server syntax, but you have tagged your question mysql. Which database are you really using? What does SELECT @@version; return? — Bill Karwin
– Bill Karwin, Commented Dec 5, 2021 at 4:58
I am using MS SQL. It is top much 5 tags so I had to put something. — Hrvoje T
– Hrvoje T, Commented Dec 5, 2021 at 10:09

ishant kaushik · Accepted Answer · 2021-12-05 02:56:49Z

0

For declaring variable, you can go through Declaring Variable in mysql However you need to do something like this to make it run <>db-fiddle

SELECT @my_name := 'Some name';
SELECT @id;
SET @s = CONCAT (
        'Select mm.mmID into @id From measurem as mm where 
    mm.placeName='
        ,''''
        ,@my_name
        ,''''
        );
PREPARE stmt
FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @id;

edited Dec 5, 2021 at 2:56

answered Dec 5, 2021 at 2:48

ishant kaushik

9718 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set variable in SQL query [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related