Parameters (Visual Studio) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parameters (Visual Studio)

Author

Topic

Marteijn
Starting Member

28 Posts

Posted - 2009-11-30 : 05:48:19

Hi,

With some of your help, I created a sql.
In this sql there are 6 parameters. These parameters need the
following input to get results in a specific year.

PARA/YEAR 2007 2008 2009 2010

PEIL1 2005 2007 2008 2009
EDAT1 01-01-2008 01-01-2009 01-01-2010 01-01-2011
PEIL2 2005 2007 2008 2009
IDAT1 20070102 20080102 20090102 20100102
EDAT2 20070101 20080101 20090101 20100101
BDAT1 20070101 20080101 20090101 20100101 and on and on

In Visual Studio I have to input those 6 paramaters each.
My question: Is there a way in Visual Studio to input only the first
parameter (PEIL1) and the next 5 parameters will be auto-filled with the options above?

I encluded the complete SQL for more information...
;WITH CTE1
AS
(
SELECT GEOTAXNR
,CBBVGONUM
,CBBVGOWOZNUM
,VTGTAXDAT
,VTGTAXNAM
,VTGRDN
,VTGRLT
,VTGVSTWRD
,VTGBSK
,VTGOZBWRD
,VTGHMSOZB
,CBBVSLKDE
,ROW_NUMBER() OVER (PARTITION BY GEOTAXNR ORDER BY VTGTAXDAT DESC) AS RowNum2
,ROW_NUMBER() OVER (PARTITION BY GEOTAXNR, VTGBSK ORDER BY VTGTAXDAT DESC) AS RowNum3
,ROW_NUMBER() OVER (PARTITION BY GEOTAXNR, VTGBSK ORDER BY VTGTAXDAT) AS RowNum4
FROM fact_BelastingTaxGegBeschikk
WHERE (VTGWRDPJR = @PEIL1)
)
,CTE2
AS
(
SELECT GEOTAXNR
,MAX(CASE WHEN RowNum2 = 1 THEN CBBVGONUM END) AS LT_VGONUM
,MAX(CASE WHEN RowNum2 = 1 THEN CBBVGOWOZNUM END) AS LT_WOZNUM
,MAX(CASE WHEN RowNum2 = 1 THEN VTGTAXDAT END) AS LT_TAXDAT
,MAX(CASE WHEN RowNum2 = 1 THEN VTGTAXNAM END) AS LT_NAAMTAXA
,MAX(CASE WHEN RowNum2 = 1 THEN VTGRDN END) AS LT_TAX
,MAX(CASE WHEN RowNum2 = 1 THEN VTGRLT END) AS LT_RDN
,MAX(CASE WHEN RowNum2 = 1 THEN VTGVSTWRD END) AS LT_WAARDE
,MAX(CASE WHEN RowNum2 = 1 THEN VTGBSK END) AS LT_BESCH
,MAX(CASE WHEN RowNum2 = 1 THEN VTGOZBWRD END) AS LT_OZB
,MAX(CASE WHEN RowNum2 = 1 THEN VTGHMSOZB END) AS LT_OZBG
,MAX(CASE WHEN RowNum2 = 1 THEN CBBVSLKDE END) AS LT_VSL

,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN CBBVGONUM END) AS BT_VGONUM
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN CBBVGOWOZNUM END) AS BT_WOZNUM
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGTAXDAT END) AS BT_TAXDAT
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGTAXNAM END) AS BT_NAAMTAXA
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGRDN END) AS BT_TAX
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGRLT END) AS BT_RDN
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGVSTWRD END) AS BT_WAARDE
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGBSK END) AS BT_BESCH
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGOZBWRD END) AS BT_OZB
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN VTGHMSOZB END) AS BT_OZBG
,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN CBBVSLKDE END) AS BT_VSL

,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN CBBVGONUM END) AS IT_VGONUM0
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN CBBVGOWOZNUM END) AS IT_WOZNUM
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGTAXDAT END) AS IT_TAXDAT
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGTAXNAM END) AS IT_NAAMTAXA
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGRDN END) AS IT_TAX
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGRLT END) AS IT_RDN
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGVSTWRD END) AS IT_WAARDE
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGBSK END) AS IT_BESCH
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGOZBWRD END) AS IT_OZB
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN VTGHMSOZB END) AS IT_OZBG
,MAX(CASE WHEN RowNum4 = 1 AND VTGBSK = 'B' THEN CBBVSLKDE END) AS IT_VSL
FROM CTE1
GROUP BY GEOTAXNR)

,BRUN
AS
(SELECT CBBVGONUM,
CBBWTVBDT,
VBRVLN,
WTBVLN
,ROW_NUMBER() OVER (PARTITION BY CBBVGONUM ORDER BY CBBWTVBDT, VBRVLN DESC) AS RowNum5
,ROW_NUMBER() OVER (PARTITION BY CBBVGONUM ORDER BY CBBWTVBDT, VBRVLN) AS RowNum6
FROM fact_BelastingVastgoedBeschikking
WHERE (CBBWTVBDT = @BDAT1)
)

,BRUN2
AS
(SELECT CBBVGONUM
,MAX(CASE WHEN RowNum5 = 1 THEN CBBVGONUM END) AS BT_VGONUM
,MAX(CASE WHEN RowNum5 = 1 THEN CBBWTVBDT END) AS BT_BDAT
,MAX(CASE WHEN RowNum5 = 1 THEN VBRVLN END) AS BT_VOLGNR
,MAX(CASE WHEN RowNum5 = 1 THEN WTBVLN END) AS BT_BESCHNR

,MAX(CASE WHEN RowNum6 = 1 THEN CBBVGONUM END) AS IT_VGONUM
,MAX(CASE WHEN RowNum6 = 1 THEN CBBWTVBDT END) AS IT_BDAT
,MAX(CASE WHEN RowNum6 = 1 THEN VBRVLN END) AS IT_VOLGNR
,MAX(CASE WHEN RowNum6 = 1 THEN WTBVLN END) AS IT_BESCHNR
FROM BRUN
GROUP BY CBBVGONUM)

,GEO
AS
(
SELECT s.WOZOBJEKTNR,
s.CODE_WGEB AS G_WGEB,
s.SOORT_OBJ_GEM AS G_OBJSRT,
s.SOORT_OBJ_GT AS G_CODEGT,
s.TAXATIEDATUM AS G_TAXDAT,
s.TAXATEUR AS G_NAAMTAXA,
s.VWAARDE_WOZ AS G_WAARDE,
s.VWAARDE_OZB AS G_OZB,
s.VWAARDE_OZBG AS G_OZBG,
s.RDN_VSL_WOZ_OZB AS G_VSL,
s.HOOFDCODE AS G_HFDC,
s.CODE_BLOKKEREN AS G_BLOK,
s.WRDINGVOORSCHR AS G_WRDVRS,
s.GROEPAANDUIDING AS G_GROEP,
s.INPANDIGE_OPNAME AS G_INPOPN,
s.BOUWSTROOMNR AS G_BOUWSTR,
s.IND_PEILPUNT AS G_PP,
s.COMPLEXNR AS G_COMPLEX,
t.STAT_INVT,
t.STAT_BEZW,
t.STAT_BWVG,
t.STAT_WRDB,
t.STAT_FOTO AS CORSAC
FROM fact_GEO_GT_VOVERZICHT AS s LEFT JOIN fact_GEO_GT_STATUS AS t ON s.WOZOBJEKTNR = t.WOZOBJEKTNR
WHERE (s.DTE = @EDAT1) AND (t.PEILJAAR = @PEIL2)

)

SELECT g.GEOTAXNR AS WOZNR
,g.VGONUM AS VGONR
,g.VGOWOZNUM AS WOZLANG
,g.CBRWYKKDE AS WIJK
,g.CBRBRTKDE AS BUURT
,g.ADRSTTNAM AS STRAAT
,g.ADRHUINUM AS NR
,g.ADRHUILET AS LT
,g.ADRHUITVG AS TV
,g.ADRHUIAND AS AD
,g.VGSOBJSRT AS OBJSRT
,g.VGSOMS AS OMSCHR
,g.VGSGBRKDE AS CODEGT
,g.VGOIDT AS INGDAT
,g.VGOADTWOZ AS EINDAT
,g.BLKKDE AS BLOK_H
,g.VGOINGJAR AS JAAR_H
,g.VGOBLWKDE AS BLOK_W
,g.VGOINGJARWOZ AS JAAR_W
,C.*
,N.*
,Z.*
FROM fact_BelastingObjecten AS g
LEFT JOIN CTE2 C
ON g.GEOTAXNR = C.GEOTAXNR
LEFT JOIN GEO N
ON g.GEOTAXNR = N.WOZOBJEKTNR
LEFT JOIN BRUN2 Z
ON g.VGONUM = Z.CBBVGONUM
WHERE g.VDTINDRCN = 'X'
AND g.ADRINDRCN = 'X'
AND (g.VGOIDT < @IDAT1)
AND g.VGSGBRKDE <> 31
AND (g.VGSOBJSRT <> 9100 AND g.VGSOBJSRT <> 9995)
AND (g.VGOADTWOZ = '00000000' OR (g.VGOADTWOZ > @EDAT2))
AND C.BT_BESCH is null
AND (C.LT_WAARDE > 2267 OR C.LT.WAARDE is null)
ORDER BY g.ADRSTTNAM, g.ADRHUINUM, g.ADRHUILET, g.ADRHUITVG, g.ADRHUIAND

I thank you already for thinking about this!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 09:47:15

how are the 5 parameters related to 1st? can you suggest that?

Marteijn
Starting Member

28 Posts

Posted - 2009-12-01 : 02:26:56

Hi,

PEIL1 is the year - 1 (except for year 2007 (year - 2)).
The other parameters relate to the first. If PEIL1 is 2008 (year = 2009),
than automically EDAT1 = 01-01-2010, PEIL2 = 2008, IDAT2 = 20090102,
EDAT2 = 20090101 and BDAT1 = 20090101. These are all parameters to determine
the year.
If PEIL1 is 2007 (year = 2008),
than automically EDAT1 = 01-01-2009, PEIL2 = 2007, IDAT2 = 20080102,
EDAT2 = 20080101 and BDAT1 = 20080101.

=

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources