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