Passing values in a variable in SQL Server

Question

I need an approach to pass values from a SELECT-Statement in a variable in SQL server because I have a lot of checks before the procedure returns a result.

Firstly I have a SELECT-Statement that checks whether the IDs aren't in a group 'US'.

// 1. check:
SELECT
    g.artID
FROM
    dbo.G_FID g
INNER JOIN
    dbo.MasterData s
ON
    g.WKN = s.WKN
WHERE
    s.ID <> "US"

All artIDs they are not in the US- the group will check for the next SELECT-Statement:

// 2. check:
SELECT
    g.artID
FROM
    dbo.G_FID g
WHERE
    (g.FID='68A')
OR
    (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))

the result of this check continues to the next check and so on. Could anyone give me an example how to handle this issue?

can't the filter conditions be added with 'and' clause(In a single select statement)? — Coder1991
– Coder1991, Commented May 11, 2017 at 7:59
Does that mean 2nd condition will be applied on the result set which resulted from the first condition? — Coder1991
– Coder1991, Commented May 11, 2017 at 8:11

P.Salmon · Accepted Answer · 2017-05-11 09:16:56Z

You could create a temporary table containing the item and a status column. At each stage in your procedure you could amend the status so that after all your checks you have know which items you are really interested in. Your first query would be a list of everything <> US with a null status (perhaps) your second query might then set the status to 1 (true/required) etc..

/*
CREATE TABLE G_FID
(ARTID INT, WKN INT, FID VARCHAR(3), VALUE VARCHAR(2))

CREATE TABLE MASTERDATA
(ID VARCHAR(2), WKN INT)
*/
truncate table g_fid
INSERT INTO G_FID VALUES
(1,1,'1','04'),(2,1,'68A','04'),(3,1,'73','04'),(4,2,'68A','04'),(5,2,'73','04')

TRUNCATE TABLE MASTERDATA
INSERT INTO MASTERDATA VALUES
('UK',1),('US',2) 

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

SELECT
    g.artID,g.WKN,g.FID,g.VALUE,s.ID,s.wkn as s_wkn,NULL AS STATUS
INTO #TEMP
FROM
    dbo.G_FID g
INNER JOIN
    dbo.MasterData s
ON
    g.WKN = s.WKN
WHERE
    s.ID <> 'US'

SELECT * FROM #TEMP

UPDATE #TEMP
SET STATUS = 1 WHERE #TEMP.ARTID IN(
SELECT
    g.artID
FROM
    dbo.G_FID g
WHERE
    (g.FID='68A')
OR
    (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))
)

SELECT * FROM #TEMP

----------- ----------- ---- ----- ---- ----------- -----------
1           1           1    04    UK   1           NULL
2           1           68A  04    UK   1           NULL
3           1           73   04    UK   1           NULL

(3 row(s) affected)

(2 row(s) affected)

artID       WKN         FID  VALUE ID   s_wkn       STATUS
----------- ----------- ---- ----- ---- ----------- -----------
1           1           1    04    UK   1           NULL
2           1           68A  04    UK   1           1
3           1           73   04    UK   1           1

(3 row(s) affected)

Thanks for your answer. That's a good idea with temporary tables. Could you give me an example? That would be nice :)

Vascudo · Accepted Answer · 2017-05-11 09:18:13Z

1

You can use Common table expressions if you dont like subqueries

with G_FID_CTE
as 
(
   SELECT *
   FROM dbo.G_FID g
   INNER JOIN dbo.MasterData s
           ON g.WKN = s.WKN
   WHERE s.ID <> "US"
)

SELECT g.artID
FROM G_FID_CTE g
WHERE (g.FID='68A')
OR    (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))

To use multiple:

--first
with G_FID_CTE
as 
(
   SELECT *
   FROM dbo.G_FID g
   INNER JOIN dbo.MasterData s
           ON g.WKN = s.WKN
   WHERE s.ID <> "US"
),

--second
with G_FID_CTE2
as 
(
   SELECT *
   FROM G_FID_CTE g
   WHERE <some_expression>
)

SELECT g.artID
FROM G_FID_CTE2 g
WHERE (g.FID='68A')
OR    (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))

edited May 11, 2017 at 9:18

answered May 11, 2017 at 8:06

Vascudo

1337 bronze badges

2 Comments

Vascudo Over a year ago

Forgot. You can use multiple CTE's like this. I'll update my post

yuro Over a year ago

Very nice. I will try it and give you feedback as soon as possible. :)

greenhatman · Accepted Answer · 2017-05-11 07:52:13Z

0

You can wrap the queries as a subquery. So means your SQL statement starts with the final results that you want, and you do that based on the result of the subquery.

SELECT
    g.artID
FROM

    (SELECT 
       g.artID 
    FROM
       dbo.G_FID g
    INNER JOIN
       dbo.MasterData s
    ON
       g.WKN = s.WKN
    WHERE
       s.ID <> "US")

WHERE
    (g.FID='68A')
OR
    (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))

edited May 11, 2017 at 7:52

answered May 11, 2017 at 7:50

greenhatman

761 silver badge7 bronze badges

1 Comment

yuro Over a year ago

I know the approach with subqueries, but I have a lot of queries. That would be to much for my intention. However thanks for your support :)

Matej Hlavaj · Accepted Answer · 2017-05-11 10:05:53Z

0

you can use condition in join clausule... For example:

SELECT
    g.artID,

FROM dbo.G_FID g
JOIN dbo.MasterData s ON g.WKN = s.WKN AND 
                         s.ID <> 'US'
WHERE (g.FID='68A') OR
      (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))

In other way you can also use temporary table like :

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SELECT
    g.artID,
    g.FID,
    g.VALUE
INTO #tmp
FROM dbo.G_FID g
JOIN dbo.MasterData s ON g.WKN = s.WKN
WHERE s.ID <> 'US'


SELECT
    g.artID
FROM
    #tmp g
WHERE
    (g.FID='68A')
OR
    (g.FID='73' AND g.VALUE IN ('04', '07', '09', '11', '13', '14', '15'))

edited May 11, 2017 at 10:05

answered May 11, 2017 at 8:27

Matej Hlavaj

1,0589 silver badges11 bronze badges

Collectives™ on Stack Overflow

Passing values in a variable in SQL Server

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related