How to set variable in to Ms Sql select

How can I set in MS Sql select variables, idea looks like this:

Declare @var int

SET @var = 0;

Select (if(idid = @var) then sum+1 else sum-1) AS Sum,
   set @var = id 
FROM table

Edit based on comments.

DECLARE @T TABLE
(
ID  INT PRIMARY KEY,
IDID INT,
SUMM INT 
)

INSERT INTO @T
SELECT 1,1,4 UNION ALL
SELECT 2,1,5 UNION ALL 
SELECT 3,2,6 UNION ALL 
SELECT 4,2,7 UNION ALL 
SELECT 5,3,8

In select result I need:

ID  IDID  SUMM 
--  ----  ----
1   1     4 
2   1     0 
3   2     6 
4   2     0 
5   3     8

Is this what you are trying to do?

Declare @var int, @id int
SET @var = 0

SELECT @id = id FROM Table t

if(@id = @var)
BEGIN
    SET @var = @var + 1
END
ELSE
BEGIN
    SET @var = @var - 1
END

print @var

This article explores the SQL variables using SET and Select SQL , Using SELECT: SELECT @ModelID = m.modelid FROM MODELS m WHERE m. areaid = 'South Coast'. Using SET: SET @ModelID = (SELECT� Example: Assigning a value to multiple variable using SELECT. Syntax: DECLARE @Local_Variable _1 <Data_Type>, @Local_Variable _2 <Data_Type>,SELECT @Local_Variable _1 = <Value_1>, @Local_Variable _2 = <Value_2> Rules: Unlike SET, SELECT can be used to assign a value to multiple variables separated by the comma.

Just use SELECT.

e.g.

SELECT @myValue = MyField FROM MyTable WHERE ....

How to set variable from a SQL query?, SET vs. SELECT When Assigning Variables. There are 2 ways of assigning a value to a local variable previously created with the DECLARE @LocalVariable� There are 2 ways of assigning a value to a local variable previously created with the DECLARE @LocalVariable statement, namely using the SET and the SELECT statements. To illustrate: DECLARE @SETVariable INT, @SELECTVariable INT SET @SETVariable = 1 SELECT @SELECTVariable = 2.

Although, like others, I don't really get the idea, the query below produces the output as specified in the original post (which, in turn, has been derived from your comment). This requires SQL Server 2005 or higher.

WITH cte AS (
  SELECT
    ID,
    IDID,
    SUMM,
    RowNum = ROW_NUMBER() OVER (PARTITION BY IDID ORDER BY ID)
  FROM @T
)
SELECT
  ID,
  IDID,
  SUMM = CASE RowNum WHEN 1 THEN SUMM ELSE 0 END
FROM cte

Basically, the SUMM output column contains SUMM from the original table if this is the first occurrence of IDID as ordered by ID, otherwise the column contains 0.

Not sure how far it is from what you are after, but maybe it'll help you to explain your problem more accurately.

SET vs. SELECT When Assigning Variables, What is Variable? In MS SQL, variables are the object which acts as a placeholder to a memory location. Variable hold single data value. When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement. To assign a variable a value by using the SET statement, include the variable name and the value to assign to the variable.

I was in the bad habit of violating referential integrity while using my test data. But this was useful for restoring the data with referential integrity after I was finished testing my scripts:

DECLARE @foreign_id INTEGER;
   SET @foreign_id = (SELECT TOP 1 (FK_column) FROM (FK_table));
-- To confirm the value is what you want it to be
SELECT @foreign_id;

Now I could multi-insert the data back once I was finished, using this value to ensure that:

-- Referential integrity check:
SELECT (FK_column) FROM (FK_table)
   WHERE (FK_column) NOT IN
   (SELECT (PK_column) FROM (PK_table));

did not return any values.

SQL Server Variable: Declare, Set, Select, Global,Local [TSQL , Whenever you are assigning a query returned value to a variable, SET will accept and assign a scalar (single) value from a query. While SELECT� Assigning scalar values. -- Query 1: Assigning scalar values Declare @set INT, @select INT SET @set=1 SELECT @select=1 SELECT @set AS 'Set', @select AS 'Select' GO. In the above query both SET and SELECT are used to assign a value to a variable. Assigning values to multiple parameters.

When to use SET vs SELECT when assigning values , Second, use the SET statement to assign the query's result set to the variable: SET @product_count = ( SELECT COUNT(*) FROM production.products );. Third � If you wanted to simply assign some variables for later use, you can do them in one shot with something along these lines: declare @var1 int,@var2 int,@var3 int; select @var1 = field1, @var2 = field2, @var3 = field3 from table where condition If that's the type of thing you're after

Variables in SQL Server Stored Procedures, Note: Don't use the select statement if it returns multiple values. When you use SELECT, the last value gets assigned to the variable and that� I want to take the value of ExtractedDate from this query and use it as @LastExtractDate in the next query. How do I do that? SELECT TOP 1 [ExtractedDate] FROM [OnsiteV4].[dbo].[SqlPendin

How to set variable from a SQL query?, Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables. USE AdventureWorks2012; GO DECLARE @state char (25); SET @state = N'Oregon'; SELECT RTRIM (FirstName) + ' ' + RTRIM (LastName) AS Name, City FROM HumanResources.vEmployee WHERE StateProvinceName = @state; C. Using a compound assignment for a local variable. The following two examples produce the same result.

Comments
  • What you posted doesn't make any sense. What's the schema and purpose?
  • I've edited your question based on your comment and still have no idea what you are asking. Can you please edit the question yourself and explain the rules used to get the expected result. Do you want only the row with the minimum id value for each IDID to contain its stored value and all other rows for that IDID to show a summ of zero?
  • Table structur: ID IDID SUMM 1 1 4 2 1 5 3 2 6 4 2 7 5 3 8 In select result I need: ID IDID SUMM 1 1 4 2 1 0 3 2 6 4 2 0 5 3 8
  • It looks from his sample code like he is trying to do a loop of some sort, though.
  • @valisimo - Please provide your table structure and example data to explain your requirement rather than snippets of invalid SQL.