One more question about building stored procedure

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hello,

one other question:
I use this statement to fill a list used für a DataRepeater-Control:

CREATE PROCEDURE dbo.stProc_Header_VerfSG_Lst AS

SELECT 'K ' + CAST(dbo.tbl_VerfSG.Verf_Nr AS nvarchar) + '/' + CAST
(RIGHT(dbo.tbl_VerfSG.Verf_Jahr, 2) AS nvarchar) AS lst_VerfNr,
CASE WHEN LEN(dbo.tbl_Kd.KdVorname) > 0 THEN
dbo.tbl_Kd.KdName + ', ' + dbo.tbl_Kd.KdVorname ELSE dbo.tbl_Kd.KdName
END AS lstKdName,
CASE WHEN dbo.tbl_VerfSG.ID_TEAM > 0 THEN
dbo.tbl_Team.Team + '-' ELSE '' END + CASE WHEN LEN
(dbo.tbl_Kd.KdzPDVNr)
> 0 THEN dbo.tbl_Kd.KdzPDVNr ELSE
dbo.tbl_Kd.KdSonstAz END AS lstVerfAz, dbo.tbl_VerfSG.EinDat AS
lstEinDat,
'Sachgebiet: ' + CASE WHEN
dbo.tbl_VerfSG.ID_Sachgebiet > 0 THEN SUBSTRING
(dbo._sys_tbl_K_Sgb.Sachgebiet, 4,
LEN(dbo._sys_tbl_K_Sgb.Sachgebiet) - 3)
ELSE '---' END + CASE WHEN
dbo.tbl_VerfSG.ID_Sperrzeit > 0 THEN ', Sperrzeit: ' + SUBSTRING
(dbo._sys_tbl_Sperrzeiten.Sperrzeit, 4,
LEN(dbo._sys_tbl_Sperrzeiten.Sperrzeit) - 3)
ELSE '' END + CASE WHEN dbo.tbl_VerfSG.ID_ErlArt > 0 THEN ', erledigt
am: ' + CONVERT(varchar(10),
dbo.tbl_VerfSG.ErlDat, 104) + ', ' + SUBSTRING
(dbo._sys_tbl_KErlArt.ErlArt, 4, LEN(dbo._sys_tbl_KErlArt.ErlArt) -
3)
ELSE ', unerledigt' END AS lstSachgebiet,
CASE WHEN dbo.tbl_VerfSG.ID_SG > 0 THEN 'SG ' +
dbo.Sozialgerichte.SG ELSE '---' END + CASE WHEN LEN
(dbo.tbl_VerfSG.AzSG)
> 0 THEN ', ' + dbo.tbl_VerfSG.AzSG ELSE '' END
AS lstSG, CASE WHEN LEN(dbo.tbl_VerfSG.Vermerk)
> 0 THEN dbo.tbl_VerfSG.Vermerk ELSE 'keine
Eintragungen' END AS lstVermerk
FROM dbo.tbl_VerfSG LEFT OUTER JOIN
dbo.Sozialgerichte ON dbo.tbl_VerfSG.ID_SG =
dbo.Sozialgerichte.SGNr LEFT OUTER JOIN
dbo._sys_tbl_KErlArt ON dbo.tbl_VerfSG.ID_ErlArt
= dbo._sys_tbl_KErlArt.ID_ErlArt LEFT OUTER JOIN
dbo._sys_tbl_K_Sgb ON
dbo.tbl_VerfSG.ID_Sachgebiet = dbo._sys_tbl_K_Sgb.ID_Sachgebiet LEFT
OUTER JOIN
dbo._sys_tbl_Sperrzeiten ON
dbo.tbl_VerfSG.ID_Sperrzeit = dbo._sys_tbl_Sperrzeiten.ID_Sperrzeit
LEFT OUTER JOIN
dbo.tbl_Team ON dbo.tbl_VerfSG.ID_Team =
dbo.tbl_Team.ID_Team LEFT OUTER JOIN
dbo.tbl_Kd ON dbo.tbl_VerfSG.ID_Kd =
dbo.tbl_Kd.ID_Kd

(I know, it´s a lot of code to read and to write). This is the "head"
of some different procedures, which allways have the same or quite
similar basic-queries but different WHERE-Clauses.

However I don´t want to write this head in every procedure, which
needs it, because it would be a lot of work to maintain the Code if
anything changes there.

First idea was to write this "head" in a view and create stored
procedures using this view. However, I´m not sure, if this runs well.
Next idea was to get the WHERE-Clause from the application - but I
wanted to store all my SQL-Code in SQL-Server and not in the
application.

Does anyone have a good idea to store the 'head' anywhere in SQL-
Server in one place and create stored procedures using this head.

btw: I use SQl Server 2000

Thank you in advance

Karl
.

Quantcast