This post is part of the QUERY series.

Common table expressions or CTE for short are like temporal tables that are kept in RAM in order to access them very fast. But to not saturate the RAM they are so short lived that they can be only used in the next sentence that generated the CTE.

The sintax of a CTE is simple, they are declared without data types because they get the type from the table that is used in the definition. In the next example we can see a CTE in combination with ROW_NUMBER, both used to get a page of records from a table ordered and filtered.

ALTER PROCEDURE [dbo].[DI_EXP_ORG_QUERY]
@pPK_Org int, @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
SET NOCOUNT ON;
--CTE declaration
WITH PagingCTE
(--Columns are only defined by name
Row_ID,
PK_Org,
Nom,
Descripcio)
AS
(--The query is enclosed in parenthesis
SELECT
ROW_NUMBER()--Here is used the Row_Number function, and the ORDER BY
OVER(ORDER BY
CASE WHEN @pSortColumn = 'PK_OrgASC' THEN DI_EXP_ORG.PK_Org END ASC,
CASE WHEN @pSortColumn = 'PK_OrgDESC' THEN DI_EXP_ORG.PK_Org END DESC,
CASE WHEN @pSortColumn = 'NomASC' THEN DI_EXP_ORG.Nom END ASC,
CASE WHEN @pSortColumn = 'NomDESC' THEN DI_EXP_ORG.Nom END DESC,
CASE WHEN @pSortColumn = 'DescripcioASC' THEN DI_EXP_ORG.Descripcio END ASC,
CASE WHEN @pSortColumn = 'DescripcioDESC' THEN DI_EXP_ORG.Descripcio END DESC
)
AS [Row_ID],
DI_EXP_ORG.PK_Org,
DI_EXP_ORG.Nom,
DI_EXP_ORG.Descripcio
 FROM DI_EXP_ORG WHERE ((@pPK_Org IS NULL) OR (DI_EXP_ORG.PK_Org=@pPK_Org))
)--Here ends the CTE, ready to use in the following QUERY
SELECT
(SELECT COUNT(*) FROM PagingCTE) AS NumRegs,
PK_Org,
Nom,
Descripcio FROM PagingCTE
--In the CTE WHERE part records are filtered.
--In this WHERE a page is selected, calculated from parameters
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
 ORDER BY Row_ID
END

As you can see the QUERY is pretty straight forward, there are no JOIN, the number of records is low and the filter parameters are just a few. So we can use CTE - ROW_NUMBER to get an excelent performance.

This two SQLServer functions are used to number datatable rows in order to obtain a page of these rows. Both functions are very simmilar but they have a key difference that obligues us to use one or the other.

Row_number returns the sequential number of a row within a partition of a result set, starting at 1 for the first row.

On the other hand Dense_rank returns the number of different rows in a table. Or in other words, returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Dense_rank function must be used when there's a JOIN and a GROUP BY / DISTINCT in a query. A JOIN can cause the apperance of duplicate rows in a datatable, and then when a GROUP BY / DISTINCT is performed over that datable if Row_number is used the duplicates will not be removed because all rows are different (have a different row_number).

Dense_rank has it's own drawbacks. It's more resource intensive than row_number and when a certain number of rows is reached (> 500.000) it stops working properly (at least in SQLServer 2008 R2). So with this in mind we will need to adapt to different scenarios: absence/presence of JOINs and number of rows, in order to choose one technique or other one.

This post is part of a series dedicated to database access. Until now all stored procedures (SP) have been pretty easy to understand and to build. This SP is the complex one of the lot. Its tasks are to obtain a page of records (rows) from a datatable ordered by a column (ascending or descending), and filter them by some criteria. All of these tasks have to be done maintaining good or decent performance.

As you can see in the SP QUERY there are different operations hence these operations can be done in different ways. Choosing one way or another one depends on the query type (with or without JOINS), the number of rows in the datatable (hundreds, thousands, millions...) and the number of parameters that can filter the datable (you can filter by one, two (not many) or you can filter a lot (by nineteen columns for example). Depending on these criteria we have to use one kind of SP QUERY or another.

Maybe this is a little confusing, but in future posts I will deal with these different QUERY methods, their meanings and their motivations: CTE RowNumber, CTE DenseRank, Temporal Table with dynamic WHERE.

This post is part of a series dedicated to database access. This is the stored procedure (SP) that deletes rows in the database.

DEL SP has the same parameters as the GET SP, with the restriction that the parameters have to belong to the affected datatable. With these parameters the DEL operation is more flexible because it can affect not only the row affected by the PK but other ones.

As usual, a code example:

DELETE FROM DI_EXP_DESTRUCCIO
 WHERE
 ((@pPK_Destruccio IS NULL) OR (PK_Destruccio=@pPK_Destruccio)) AND
 ((@pFK_TAAD IS NULL) OR (DI_EXP_DESTRUCCIO.FK_TAAD = @pFK_TAAD))
 AND ((@pFK_Estat IS NULL) OR (DI_EXP_DESTRUCCIO.FK_Estat = @pFK_Estat))
 AND ((@pDataInici IS NULL) OR (DI_EXP_DESTRUCCIO.Data_Destruccio BETWEEN @pDataInici AND @pDataFi))
 AND ((@pFK_Arxiu IS NULL) OR (DI_EXP_DESTRUCCIO.FK_Arxiu = @pFK_Arxiu))

This post is part of a series dedicated to database access. This is the stored procedure (SP) that updates a row in the database.

It's fairly straightforward to do this SP. It receives the value of all columns of the datatable that it has to update. The program knows which ones are pure data and which column is the primary key (PK). The PK has to go in the WHERE part of the SQL sentence in order to affect only one row of the datatable. In this SP the PK is always an input parameter, in the INS SP it was an output parameter.

Code looks like this:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_UPD]
 @pPK_Destruccio int,
 @pFK_TAAD int,
 @pFK_Arxiu int,
 @pAny_obertura int,
 @pAny_tancament int,
 @pMetres decimal(5, 1),
 @pData_Destruccio datetime,
 @pObservacions varchar(2000),
 @pbCataleg nchar(20),
 @pSerie_documental varchar(500),
 @pMK_UsuariAlta varchar(50),
 @pMK_DataAlta datetime,
 @pMK_UsuariModif varchar(50),
 @pMK_DataModif datetime, @pFK_Estat int,
 @pNumExpedients int,
 @pFK_Classificacio int,
 @pFK_Especifica int
 AS BEGIN
 UPDATE DI_EXP_DESTRUCCIO SET
--All datatable columns except the PK
 FK_TAAD=@pFK_TAAD, FK_Arxiu=@pFK_Arxiu,
 Any_obertura=@pAny_obertura, Any_tancament=@pAny_tancament,
 Metres=@pMetres, Data_Destruccio=@pData_Destruccio,
 Observacions=@pObservacions, bCataleg=@pbCataleg,
 Serie_documental=@pSerie_documental,
 MK_UsuariAlta=@pMK_UsuariAlta, MK_DataAlta=@pMK_DataAlta,
 MK_UsuariModif=@pMK_UsuariModif, MK_DataModif=@pMK_DataModif,
 FK_Estat=@pFK_Estat, NumExpedients=@pNumExpedients,
 FK_Classificacio=@pFK_Classificacio, FK_Especifica=@pFK_Especifica
--Here is the PK
 WHERE (PK_Destruccio=@pPK_Destruccio)
END

This post is part of a series dedicated to database access. This is the stored procedure that creates new rows in the database.

It is very simple; it has all the columns of the table to which it belongs as input parameters. If the primary key is an identity it is converted to an output parameter in order to send the new created key to the calling layer for future use.

In order to retrieve the value of the primary key I use the IDENT_CURRENT function.

Here is an example of how it works:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_INS]
--PK as OUTPUT
@pPK_Destruccio int OUTPUT,
@pFK_TAAD int, 
@pFK_Arxiu int, 
@pAny_obertura int, 
@pAny_tancament int, 
@pMetres decimal(5, 1), @pData_Destruccio datetime, 
@pObservacions varchar(2000), 
@pbCataleg nchar(20), 
@pSerie_documental varchar(500), 
@pMK_UsuariAlta varchar(50), 
@pMK_DataAlta datetime, @pMK_UsuariModif varchar(50), 
@pMK_DataModif datetime, @pFK_Estat int, 
@pNumExpedients int, 
@pFK_Classificacio int, 
@pFK_Especifica int AS
BEGIN
 INSERT INTO DI_EXP_DESTRUCCIO (
 FK_TAAD, 
 FK_Arxiu, 
 Any_obertura, 
 Any_tancament, 
 Metres, 
 Data_Destruccio, 
 Observacions, 
 bCataleg, 
 Serie_documental, 
 MK_UsuariAlta, 
 MK_DataAlta, 
 MK_UsuariModif, 
 MK_DataModif, 
 FK_Estat, 
 NumExpedients, 
 FK_Classificacio, 
 FK_Especifica
 ) VALUES (
--PK is not included
 @pFK_TAAD, 
 @pFK_Arxiu, 
 @pAny_obertura, 
 @pAny_tancament, 
 @pMetres, 
 @pData_Destruccio, 
 @pObservacions, 
 @pbCataleg, 
 @pSerie_documental, 
 @pMK_UsuariAlta, 
 @pMK_DataAlta, 
 @pMK_UsuariModif, 
 @pMK_DataModif, 
 @pFK_Estat, 
 @pNumExpedients, 
 @pFK_Classificacio, 
 @pFK_Especifica
 );
--PK retrieval
SET @pPK_Destruccio = IDENT_CURRENT('DI_EXP_DESTRUCCIO')
END

This post is part of a series dedicated to database access. Every table has different stored procedures (SP) to access or modify it. One of these SP is the GET SP, so we will have the SP called TABLENAME_GET. This SP performs SELECT operations against the datable, so it GETS records.

The GET SP usually has the primary key (PK) as an input parameter. Optionally it has more parameters depending of the bussiness logic. For each extra parameter an operator is selected, it can be '=', '>=', etc... and in nvarchar/nchar columns you can choose LIKE operator. The interesting part of building a SP with parameters is that you can choose to inform them or not, in other words, all parameters are optional. If you don't inform any parameter you'll get all records in the datatable. Doing a ton of parameter checking has its impact on performance, so when you reach 8-10 optional parameter treshold then it is advisable to switch to dynamic SQL. The dynamic SQL building uses the 'sp_executesql' instead of EXEC. It's better the sp_executesql because it will cache the execution plan and will check the parameters type.

Here is an example of a simple SQL GET SP.

ALTER PROCEDURE [dbo].[T_MUNICIPI_GET] @pIDMUNICIPI bigint , @pIDCOMARCA int, @pNOMMUNICIPI nvarchar(50) AS BEGIN
 SELECT T_MUNICIPI.IDMUNICIPI,
T_MUNICIPI.IDCOMARCA,
T_MUNICIPI.IDDIPUTACIO,
T_MUNICIPI.NOMMUNICIPI,
T_MUNICIPI.CODMUNICIPI,
T_MUNICIPI.CODPOSTAL,
T_MUNICIPI.NOMCURTMUN,
T_MUNICIPI.NOMALT1MUN,
T_MUNICIPI.NOMALT2MUN,
T_MUNICIPI.NOMALT3MUN,
T_MUNICIPI.CAPITALCOM,
T_MUNICIPI.CAPITALDIP,
T_MUNICIPI.CODIMUN6,
T_COMARCA.NOMCOMARCA
 FROM T_MUNICIPI  LEFT OUTER JOIN T_COMARCA ON T_MUNICIPI.IDCOMARCA=T_COMARCA.IDCOMARCA
 WHERE ((@pIDMUNICIPI IS NULL) OR (IDMUNICIPI=@pIDMUNICIPI))
 AND ((@pIDCOMARCA IS NULL) OR (T_COMARCA.IDCOMARCA=@pIDCOMARCA))
 AND ((@pNOMMUNICIPI IS NULL) OR (T_MUNICIPI.NOMMUNICIPI LIKE @pNOMMUNICIPI + '%'))
 
ORDER BY T_MUNICIPI.NOMMUNICIPI
 
END

And here an example of a complex one.

ALTER PROCEDURE [dbo].[DI_EXP_EXPEDIENTARXIU_GET] 
--This SELECT has a ton of parameters to use as a filter, so its
build dynamically
@pPK_ExpedientArxiu int, @pTitol varchar(255),
 @pExped_I varchar(50),
 @pAny_Obertura int,
 @pExped_F varchar(50),
 @pFK_LLocDiposit int,
 @pFK_Classificacio int,
 @pFK_Destruccio int,
 @pFK_Procedencia int,
 @pFK_Especifica int,
 @pAny_Tancament int,
 @pFK_Transferencia int,
 @pFK_Estat int,
 @pFK_Arxiu int,
 @pFK_TAAD int,
 @pFK_Uniforme int,
 @pFK_Capsa int,
 @pAny_OberturaMesGran int,
 @pAny_TancamentMenor int,
 @pCapsa varchar(30) AS
 BEGIN
SET NOCOUNT ON
DECLARE @strSQL nvarchar(MAX)
DECLARE @strPARAMS nvarchar(MAX)

SET @strSQL = 'SELECT DISTINCT dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Transferencia,
dbo.DI_EXP_EXPEDIENTARXIU.FK_LLocDiposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Municipi,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat,
dbo.DI_EXP_EXPEDIENTARXIU.FK_TransferenciaAH,
dbo.DI_EXP_EXPEDIENTARXIU.Titol,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_I,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_F,
dbo.DI_EXP_EXPEDIENTARXIU.Dia_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Mes_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Dia_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.Mes_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_I,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_F,
dbo.DI_EXP_EXPEDIENTARXIU.Observacions_Unitat,
dbo.DI_EXP_EXPEDIENTARXIU.Volum,
dbo.DI_EXP_EXPEDIENTARXIU.Descripcio_AC,
dbo.DI_EXP_EXPEDIENTARXIU.Observacions_AC,
dbo.DI_EXP_EXPEDIENTARXIU.CodiTransferencia,
dbo.DI_EXP_EXPEDIENTARXIU.Adreca,
dbo.DI_EXP_EXPEDIENTARXIU.CP,
dbo.DI_EXP_EXPEDIENTARXIU.C,
dbo.DI_EXP_EXPEDIENTARXIU.MK_UsuariAlta,
dbo.DI_EXP_EXPEDIENTARXIU.MK_DataAlta,
dbo.DI_EXP_EXPEDIENTARXIU.MK_UsuariModif,
dbo.DI_EXP_EXPEDIENTARXIU.MK_DataModif,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_I,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_F,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio,
dbo.DI_EXP_CLASSIFICACIO.Codi_TC,
dbo.DI_EXP_UNIFORME.Uniforme_TC,
dbo.DI_EXP_ESPECIFICA.Especifica_TC,
dbo.DI_EXP_PROCEDENCIA.Codi_TC AS Codi_Procedencia,
dbo.DI_EXP_PROCEDENCIA.N_NivellP,
dbo.DI_EXP_LLOCDIPOSIT.Nom AS Diposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Arxiu,
dbo.DI_EXP_CAPSES.Capsa,
dbo.DI_EXP_LLOCDIPOSIT.Sigla,
dbo.DI_EXP_EXPEDIENTARXIU.CapsaHist,
dbo.DI_EXP_ESTAT.Nom AS Estat
 FROM DI_EXP_EXPEDIENTARXIU INNER JOIN DI_EXP_CLASSIFICACIO ON 
DI_EXP_EXPEDIENTARXIU.FK_Classificacio=DI_EXP_CLASSIFICACIO.PK_Classificacio
  LEFT OUTER JOIN DI_EXP_ESPECIFICA ON DI_EXP_EXPEDIENTARXIU.FK_Especifica=DI_EXP_ESPECIFICA.PK_Especifica 
 LEFT OUTER JOIN DI_EXP_UNIFORME ON DI_EXP_EXPEDIENTARXIU.FK_Uniforme=DI_EXP_UNIFORME.PK_Uniforme 
 INNER JOIN DI_EXP_PROCEDENCIA ON DI_EXP_EXPEDIENTARXIU.FK_Procedencia=DI_EXP_PROCEDENCIA.PK_Procedencia 
 LEFT OUTER JOIN DI_EXP_TAAD_REL ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_TAAD_REL.FK_Classificacio 
 LEFT OUTER JOIN DI_EXP_CAPSA_EXPEDIENT ON
DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=DI_EXP_CAPSA_EXPEDIENT.FK_ExpedientArxiu 
 LEFT OUTER JOIN DI_EXP_CAPSES ON DI_EXP_CAPSA_EXPEDIENT.FK_Capsa=DI_EXP_CAPSES.PK_Capsa 
 LEFT OUTER JOIN DI_EXP_SIGTOP ON DI_EXP_CAPSES.PK_Capsa=DI_EXP_SIGTOP.FK_Capsa 
 LEFT OUTER JOIN DI_EXP_LLOCDIPOSIT ON DI_EXP_SIGTOP.FK_LlocDiposit=DI_EXP_LLOCDIPOSIT.PK_LLocDiposit 
 INNER JOIN DI_EXP_ESTAT ON DI_EXP_EXPEDIENTARXIU.FK_Estat=DI_EXP_ESTAT.PK_Estat 
 WHERE 1=1 '
--Depending if the parameter is informed it is attached to the WHERE statement or not
IF @pPK_ExpedientArxiu IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=@lPK_ExpedientArxiu'
END

IF @pTitol IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Titol LIKE @lTitol + ''%'''
END

IF @pExped_I IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_I LIKE @lExped_I + ''%'''
END

IF @pAny_Obertura IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura = @lAny_Obertura'
END

IF @pExped_F IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_F LIKE @lExped_F + ''%'''
END

IF @pFK_LLocDiposit IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_SIGTOP.FK_LLocDiposit = @lFK_LLocDiposit'
END

IF @pFK_Classificacio IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio = @lFK_Classificacio'
END

IF @pFK_Destruccio IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio = @lFK_Destruccio'
END

IF @pFK_Procedencia IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia = @lFK_Procedencia'
END

IF @pFK_Especifica IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica = @lFK_Especifica'
END

IF @pAny_Tancament IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament = @lAny_Tancament'
END

IF @pFK_Transferencia IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Transferencia = @lFK_Transferencia'
END

IF @pFK_Estat IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat = @lFK_Estat'
END

IF @pFK_Arxiu IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Arxiu = @lFK_Arxiu'
END

IF @pFK_TAAD IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_TAAD_REL.FK_TAAD = @lFK_TAAD'
END

IF @pFK_Uniforme IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme = @lFK_Uniforme'
END

IF @pFK_Capsa IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSA_EXPEDIENT.FK_Capsa = @lFK_Capsa'
END

IF @pAny_OberturaMesGran IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura >= @lAny_OberturaMesGran'
END

IF @pAny_TancamentMenor IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament <= @lAny_TancamentMenor'
END

IF @pCapsa IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSES.Capsa LIKE @lCapsa + ''%'''
END

SET @strSQL = @strSQL + ' ORDER BY DI_EXP_CLASSIFICACIO.Codi_TC, Exped_I, Any_Obertura'


--PARAMS--
SET @strPARAMS = '@lPK_ExpedientArxiu int, 
@lTitol varchar(255), 
@lExped_I varchar(50), 
@lAny_Obertura int, 
@lExped_F varchar(50), 
@lFK_LLocDiposit int, 
@lFK_Classificacio int, 
@lFK_Destruccio int, 
@lFK_Procedencia int, 
@lFK_Especifica int, 
@lAny_Tancament int, 
@lFK_Transferencia int, 
@lFK_Estat int, 
@lFK_Arxiu int, 
@lFK_TAAD int, 
@lFK_Uniforme int, 
@lFK_Capsa int, 
@lAny_OberturaMesGran int, 
@lAny_TancamentMenor int, 
@lCapsa varchar(30)'



--EXECUTE--
EXECUTE sp_executesql @strSQL, @strPARAMS,
 @pPK_ExpedientArxiu,
 @pTitol, @pExped_I,
 @pAny_Obertura,
 @pExped_F,
 @pFK_LLocDiposit,
 @pFK_Classificacio,
 @pFK_Destruccio,
 @pFK_Procedencia,
 @pFK_Especifica,
 @pAny_Tancament,
 @pFK_Transferencia,
 @pFK_Estat,
 @pFK_Arxiu,
 @pFK_TAAD,
 @pFK_Uniforme,
 @pFK_Capsa,
 @pAny_OberturaMesGran,
 @pAny_TancamentMenor,
 @pCapsa;
END

When I'm developing an application I need a database to store all user data, and to retrieve information fast. It doesn't matter if it's a web application or a desktop one, a database is always needed. So I built a layer that manages the access to the information. The closest layer to the database tables is the stored procedures layer. I use SQL Server as a database server, so the client side is base in SQLClient for .NET. Later on I'll explain how these stored procedures will work and how they are used from the client side.

There's an important thing to explain about these stored procedures, they are software generated with a CASE tool that I've developed. From an XML file I create the storeds, this XML file is also created from the table definition, I only add extra business logic but I don't start from 0, the tool generates a basic scaffolding that helps me a lot.

From a table I create the next storeds:

• GET: to retrieve one (based on the primary key) or more rows (based in some criteria).
• GETTOP: the same as GET but with a row limit.
• DEL: to delete a row.
• INS: to insert a new row.
• UPD: to update a row.
• QUERY: to get a determined number of rows, filtered by some criteria, ordered by a column and paginated.

This is created for each table, so I get a ton of stored procedures that are like "tablename_GET", "tablename_GETTOP", "tablename_DEL", "tablename_INS", "tablename_UPD" and "tablename_QUERY".

In future posts I will detail how those stored procedures are built. As a recap here is this image:

I work developing applications (usually web applications) and with Microsoft technology. Before that I was doing Winforms applications (also known as desktop apps) and that evolved into web applications. Those web applications have suffered great changes since 2000. Back then they were done in Classic ASP, but now HTML5 is in full force (and I'm happy about that because it seems that finally someone listened to the needs of a web application developer, with HTML5 there are tags for things that with HTML4 you have to do with Javascript).

The most important thing in a web application is finding a way to get information from the client (a browser) to the server. And as a developer I have to do this task (moving information around) in an efficient way and without dying of boredom doing it.

So I've found a way, my way. Perhaps there are better ones, but this is mine. I use JSON for data communication, and HTML/CSS for presentation (nothing new). But I do that separately. JSON is usually generated at the server and transported with AJAX, and joins the HTML in the browser. HTML is only loaded once, and then only JSON is moved between client and server. This way it is easy to maintain the form state, because it's static (remember, HTML only loads once).

To avoid dying of boredom in the task of retrieving the value of 40 fields of a form (for example), load the 40 values in 40 parameters of a stored procedure. I do that with serialization/deserialization. I have a software tool that automatically creates a mapping between controls and values, so I don't mind having 5 or 50 fields in a form because the tool will deal with that. As a developer I only have to do fine tuning, putting a calendar, a combo, cascade combos, some business logic, etc. The heavy lifting is done by a tool.

In order to access a database (DB) I use stored procedures (SP) and in order to create those SPs I also have a tool that creates them, and a class in C# that invokes those SPs (clsDades)

To connect the client (browser) with the class that invokes the SPs I use ASHX, with very good results in a lot of projects (some big ones and a lot of little ones).

Printing is usually the weak point of web applications, and I've found a solution using PDFs. The problem here is how to create this PDFs, I use HTML and later I convert it to PDF using a library from Winnovative. If there are a ton of PDFs to do, and the result the PDF is too big to move around in a web environment, I resort to a desktop application that does only that.

So the architecture of a web application is something like the next diagram.

In future posts I will deal with all of these parts, and how are they built.