CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
yuro
yuro

Reputation: 2229

Passing values in a variable in SQL Server

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?

Upvotes: 0

Views: 86

Answers (4)

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

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'))

Upvotes: 0

Vascudo
Vascudo

Reputation: 133

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'))

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17665

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)

Upvotes: 2

greenhatman
greenhatman

Reputation: 76

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'))

Upvotes: 0

Related Questions