SQL select multiple columns to multiple variables using where

Question

I have a simple table as follows

CREATE TABLE [accounting].[ExtractControl](
    [SourceSchema] [varchar](50) NOT NULL,
    [SourceTable] [varchar](150) NOT NULL,
    [SourceDatabase] [varchar](50) NOT NULL)

I would like to select SourceDatabase using SourceSchema and SourceTable values

Currently I am doing with multiple queries as follows

Select @s1 = SourceDatabase from accounting.ExtractControl where SourceSchema = 'xxx' and SourceTable = 'xxx'
Select @s2 = SourceDatabase from accounting.ExtractControl where SourceSchema = 'yyy' and SourceTable = 'yyy'
Select @s3 = SourceDatabase from accounting.ExtractControl where SourceSchema = 'xxx' and SourceTable = 'yyy'

I do believe that this can be done in more elegant ways! Thanks for the help!

@DanielHilgarth I am querying the database 3 times to return 3 variables, I am thinking that maybe its possible to return all 3 variables using 1 query against the Db. — dopplesoldner
– dopplesoldner, Commented Dec 13, 2012 at 10:46

Daniel Hilgarth · Accepted Answer · 2012-12-13 10:37:14Z

1

Are you asking for something like this?

select SourceDatabase from accounting.ExtractControl
where SourceSchema in ('xxx', 'yyy') and SourceTable in ('xxx', 'yyy');

This will return the SourceDatabase where the SourceSchema is either 'xxx' or 'yyy' and the SourceTable is either 'xxx' or 'yyy'.

answered Dec 13, 2012 at 10:37

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

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

3 Comments

dopplesoldner Over a year ago

Hi Daniel It would indeed return SourceDatabase but I want them to be stored in different variables depending on SourceSchema and SourceTable For eg. s1 should be when SourceSchema and SourceTable are both 'xxx' s2 -> when both are 'yyy'... etc etc

Daniel Hilgarth Over a year ago

If you actually need three different variables, go with your current solution.

Andriy M Over a year ago

@dopplesoldner: Agree with Daniel: if you want the three results in three distinct variables, stay with your current solution. However, if you elaborate more on how exactly you are going to use those variables, it may turn out there's a better way (or it may not).

Oleksandr Fedorenko · Accepted Answer · 2012-12-16 09:09:58Z

1

option with one query

SELECT @s1 = MAX(CASE WHEN SourceSchema = 'xxx' and SourceTable = 'xxx' THEN SourceDatabase END),
       @s2 = MAX(CASE WHEN SourceSchema = 'yyy' and SourceTable = 'yyy' THEN SourceDatabase END),
       @s3 = MAX(CASE WHEN SourceSchema = 'xxx' and SourceTable = 'yyy' THEN SourceDatabase END)
FROM accounting.ExtractControl

Demo on SQL Fiddle

edited Dec 16, 2012 at 9:09

answered Dec 14, 2012 at 10:20

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Comments

marc_s · Accepted Answer · 2012-12-13 10:49:45Z

0

select * from accounting.ExtractControl
where SourceSchema = 'xxx' or SourceSchema = 'yyy'

edited Dec 13, 2012 at 10:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 13, 2012 at 10:40

Mert

6,6127 gold badges34 silver badges74 bronze badges

2 Comments

dopplesoldner Over a year ago

Hi Mert I want them to be stored in different variables depending on SourceSchema and SourceTable For eg. s1 should be when SourceSchema and SourceTable are both 'xxx' s2 -> when both are 'yyy'... etc etc

Mert Over a year ago

I dont think there is better way then. you may select like this and linq in your code.

Pankaj Dubey · Accepted Answer · 2012-12-14 10:34:27Z

0

what about this Query...

select SourceDatabase
from accounting.ExtractControl
where SourceSchema in ('xxx', 'yyy')
      and SourceTable in ('xxx', 'yyy')
      and SourceTable = SourceSchema

i did not tried this but i think this is what you required

answered Dec 14, 2012 at 10:34

Pankaj Dubey

8163 gold badges10 silver badges33 bronze badges

1 Comment

Pankaj Dubey Over a year ago

if you want each cell value to different varriable then go with cursor now

Collectives™ on Stack Overflow

SQL select multiple columns to multiple variables using where

4 Answers 4

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related