How to set variable from a SQL query

Question

I m trying to set a variable from a SQL query   declare  ModelID uniqueidentifer  Select  ModelID   select modelid from models where areaid    South Coast    Obviously I m not doing this right as it doesn t work  Can somebody suggest a solution   Thanks

User · Answer

To ASSIGN variables using a SQL select the best practice is as shown below  - gt DECLARE co id INT   - gt DECLARE sname VARCHAR 10     - gt SELECT course id INTO co id FROM course details   - gt SELECT student name INTO sname FROM course details    IF you have to assign more than one variable in a single line you can use this same SELECT INTO  - gt DECLARE val1 int  - gt DECLARE val2 int   - gt SELECT student  id student name INTO val1 val2 FROM student details   --HAPPY CODING--

User · Answer

SELECT  ModelID   modelid FROM Models WHERE areaid    South Coast    If your select statement returns multiple values  your variable is assigned the last value that is returned     For reference on using SELECT with variables  http   msdn microsoft com en-us library aa259186 28SQL 80 29 aspx

User · Answer

Using SELECT SELECT  ModelID   m modelid    FROM MODELS m  WHERE m areaid    South Coast   Using SET SET  ModelID    SELECT m modelid                    FROM MODELS m                  WHERE m areaid    South Coast    See this question for the difference between using SELECT and SET in TSQL  Warning If this SELECT statement returns multiple values  bad to begin with    When using SELECT  the variable is assigned the last value that is returned  as womp said   without any error or warning  this may cause logic bugs  When using SET  an error will occur

User · Answer

declare  ModelID uniqueidentifer  --make sure to use brackets set  ModelID    select modelid from models where areaid    South Coast    select  ModelID

User · Answer

Use TOP 1 if the query returns multiple rows   SELECT TOP 1  ModelID   m modelid    FROM MODELS m  WHERE m areaid    South Coast

User · Answer

Select  ModelID  m modelid  From   MODELS m Where  m areaid    South Coast    In this case if you have two or more results returned then your result is the last record  So be aware of this if you might have two more records returned as you might not see the expected result

User · Answer

There are three approaches    DECLARE  SET -- Microsoft Recommended approach SELECT    Below query details the advantage and disadvantage of each   -- First way   DECLARE  test int    SELECT 1            test2 int    SELECT a from  values  1   2   t a   -- throws error  -- advantage  declare and set in the same place -- Disadvantage  can be used only during declaration  cannot be used later  -- Second way DECLARE  test int             test2 int   SET  test    select 1  SET  test2    SELECT a from  values  1   2   t a   -- throws error  -- Advantage  ANSI standard   -- Disadvantage  cannot set more than one variable at a time  -- Third way DECLARE  test int   test2 int  SELECT  test    select 1          test2    SELECT a from  values  1   2   t a   -- throws error  -- Advantage  Can set more than one variable at a time -- Disadvantage  Not ANSI standard

User · Answer

You can use this  but remember that your query gives 1 result  multiple results will throw the exception   declare  ModelID uniqueidentifer Set  ModelID    select Top 1  modelid from models where areaid    South Coast     Another way   Select Top 1  ModelID   modelid from models where areaid    South Coast

User · Answer

I prefer just setting it from the declare statement  DECLARE  ModelID uniqueidentifer    SELECT modelid                                      FROM models                                     WHERE areaid    South Coast

[sql] How to set variable from a SQL query?

To ASSIGN variables using a SQL select the best practice is as shown below

IF you have to assign more than one variable in a single line you can use this same SELECT INTO

Examples related to sql

Examples related to sql-server

Examples related to tsql