How to set variable in to Ms Sql select

Question

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

What you posted doesn't make any sense. What's the schema and purpose? — JNK
– JNK, Commented May 27, 2011 at 12:08
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? — Martin Smith
– Martin Smith, Commented May 27, 2011 at 12:54

KenL · Accepted Answer · 2011-05-27 12:23:01Z

25

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

answered May 27, 2011 at 12:23

KenL

8756 silver badges14 bronze badges

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

1 Comment

valisimo Over a year ago

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

Paul Alan Taylor · Accepted Answer · 2011-05-27 12:15:03Z

7

Just use SELECT.

e.g.

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

answered May 27, 2011 at 12:15

Paul Alan Taylor

10.7k1 gold badge30 silver badges44 bronze badges

2 Comments

JNK Over a year ago

It looks from his sample code like he is trying to do a loop of some sort, though.

Martin Smith Over a year ago

@valisimo - Please provide your table structure and example data to explain your requirement rather than snippets of invalid SQL.

Andriy M · Accepted Answer · 2011-05-27 15:42:12Z

5

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.

answered May 27, 2011 at 15:42

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

Christian Meyer · Accepted Answer · 2019-04-28 21:46:02Z

0

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.

answered Apr 28, 2019 at 21:46

Christian Meyer

6358 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to set variable in to Ms Sql select

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related