Sql/sqlserver/sp/stored procedure: Ignore optional parameters in update + CRUD functionality in SP
You have a Stored Procedure which can do updates.
One or more of the parameters are optional.
How do you express this?
The first solution that springs to mind is IFs.
If param1 is null
Update without param1
else
Update with param1
Another more elegant solution could be this:
Update SomeDB
SET
SomeColumn=@SomeParam,
Antall = ISNULL(@Param1,Antall) --If @Param1 is null, use the exisiting column value, i.e no update
Of course if you want to set the column to NULL this solution can't be used.
Below full stored procedure with full CRUD functionality and optional parameters:
USE [SOMEDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HendelserVarslet_siud]
@FraDato DateTime = null,
@Tildato DateTime = null,
@HendelseTypeID smallint=NULL,
@HendelseInformasjonID int=NULL,
@RaadgiverNr nvarchar(50)=NULL,
@Antall int=NULL,
@Funksjon Char(1)='L'
AS
BEGIN
SET NOCOUNT ON;
IF(@Funksjon='L' OR @Funksjon='S')
-- List records
BEGIN
-- set default dates
IF @FraDato IS NULL
SET @FraDato = dbo.StartOfday(GETDATE())
IF @Tildato IS NULL
SET @Tildato= dbo.EndOfday(@FraDato)
-- Get records
SELECT
V.HendelseInformasjonID, I.HendelseTypeID,
T.HendelseType, V.RaadgiverNr, V.Antall,
V.LoggetDato, I.Beskrivelse, I.RegistrertDato
FROM HendelseVarslet AS V
INNER JOIN
HendelseInformasjon AS I
ON V.HendelseInformasjonID=I.HendelseInformasjonID
INNER JOIN
HendelseType T
ON T.HendelseTypeId = I.HendelseTypeId
WHERE
V.LoggetDato>=@FraDato
AND
V.LoggetDato<=@TilDato
AND
(I.HendelseTypeID=@HendelseTypeID OR @HendelseTypeID IS NULL)
AND
(V.HendelseInformasjonID=@HendelseInformasjonID OR @HendelseInformasjonID IS NULL)
AND
(V.RaadgiverNr=@RaadgiverNr OR @RaadgiverNr IS NULL)
RETURN @@ERROR
END
ELSE
IF(@Funksjon='I')
-- Insert record
BEGIN
IF (@RaadgiverNr IS NOT NULL AND @HendelseInformasjonID IS NOT NULL AND @Antall IS NOT NULL)
AND
NOT EXISTS ( SELECT HendelseinformasjonId, RaadgiverNr FROM [PFA].[dbo].HendelseVarslet
WHERE
RaadgiverNr=@RaadgiverNr AND HendelseInformasjonID=@HendelseInformasjonID)
AND
EXISTS ( SELECT HendelseinformasjonId, RaadgiverTil FROM [PFA].[dbo].Hendelse
Where (RaadgiverTil=@RaadgiverNr OR Eier=@RaadgiverNr)
AND HendelseInformasjonID=@HendelseInformasjonID )
BEGIN
INSERT INTO [PFA].[dbo].[HendelseVarslet]
(
HendelseInformasjonID,
RaadgiverNr,
Antall,
LoggetDato
)
VALUES
(
@HendelseInformasjonID,
@RaadgiverNr,
@Antall,
getdate()
)
RETURN @@ERROR
END
ELSE
BEGIN
RAISERROR(' Invalid parameters or these values already exists in db',11,1)
RETURN @@ERROR
END
END
ELSE
IF(@Funksjon='U')
-- update record
BEGIN
IF (@RaadgiverNr IS NOT NULL AND @HendelseInformasjonID IS NOT NULL)
AND
(EXISTS ( SELECT HendelseinformasjonId, RaadgiverNr FROM [PFA].[dbo].HendelseVarslet
WHERE
RaadgiverNr=@RaadgiverNr AND HendelseInformasjonID=@HendelseInformasjonID)
)
BEGIN
UPDATE [PFA].[dbo].[HendelseVarslet]
SET
HendelseInformasjonID=@HendelseInformasjonID,
RaadgiverNr=@RaadgiverNr,
Antall = ISNULL(@Antall,Antall) --If @Antall IS NULL, don't update Antall column
RETURN @@ERROR
END
ELSE
BEGIN
RAISERROR(Invalid parameters or this combination of HendelseInformasjonId and rådgiver is non-existant ',11,1)
RETURN @@ERROR
END
END
ELSE
IF(@Funksjon='D')
-- Delete record
BEGIN
IF EXISTS ( SELECT HendelseinformasjonId, RaadgiverNr FROM [PFA].[dbo].HendelseVarslet
WHERE
(RaadgiverNr=@RaadgiverNr AND HendelseInformasjonID=@HendelseInformasjonID)
)
AND @RaadgiverNr IS NOT NULL AND @HendelseInformasjonID IS NOT NULL
BEGIN
DELETE FROM [PFA].[dbo].[HendelseVarslet]
WHERE
HendelseInformasjonID=@HendelseInformasjonID
AND
RaadgiverNr=@RaadgiverNr
RETURN @@ERROR
END
ELSE
BEGIN
RAISERROR('Invalid parameters or this combination of HendelseInformasjonId and rådgiver is non-existant,11,1)
RETURN @@ERROR
END
END
ELSE
-- Unknown function
BEGIN
RAISERROR('Function unknown,11,1)
RETURN @@ERROR
END
END
GO
Posted via email from Henris blogg
SQL/Sqlserver/SP/Stored procedure: Funksjon som støtter Select, Insert, Update og Delete
Etter inspirasjon fra grensesnittet på en SP noen databasekolleger har laget
kommer her en Stored Procedure som har støtte for CRUD-operasjoner.
Eller SIUD på databasisk(...nytt ord:)
Ps Jeg har ingen databasekompetanse, jeg bare lager det jeg trenger akkurat der og da…
De som kan det kan helt sikkert forbedre dette :)
Enjoy:
USE [DINDATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HentConfig]
@Valg Varchar(100) = NULL,
@Verdi Varchar(MAX) = NULL,
@Funksjon Char(1)='L'
AS
BEGIN
SET NOCOUNT ON;
IF(@Funksjon='L')
-- List records
BEGIN
SELECT
Valg, Verdi
FROM
HendelseVarselKonfig
WHERE
(Valg=@Valg OR @Valg IS NULL)
RETURN @@ERROR
END
ELSE
IF(@Funksjon='I')
-- Insert record
BEGIN
IF NOT EXISTS (SELECT Verdi FROM HendelseVarselKonfig
WHERE Valg=@Valg )
BEGIN
INSERT INTO HendelseVarselKonfig
(Valg, Verdi)
VALUES
(@Valg, @Verdi)
RETURN @@ERROR
END
ELSE
BEGIN
RAISERROR('Denne verdien finnes allerede',11,1)
RETURN @@ERROR
END
END
ELSE
IF(@Funksjon='U')
-- update record
BEGIN
IF EXISTS (SELECT Verdi FROM HendelseVarselKonfig
WHERE Valg=@Valg )
BEGIN
UPDATE HendelseVarselKonfig
SET Verdi=@Verdi
WHERE
Valg=@Valg
RETURN @@ERROR
END
ELSE
BEGIN
RAISERROR('Denne verdien finnes ikke',11,1)
RETURN @@ERROR
END
END
ELSE
IF(@Funksjon='D')
-- Slett record
BEGIN
IF EXISTS (SELECT Verdi FROM HendelseVarselKonfig
WHERE Valg=@Valg )
BEGIN
DELETE FROM HendelseVarselKonfig
WHERE
Valg=@Valg
RETURN @@ERROR
END
ELSE
BEGIN
RAISERROR('Denne verdien finnes ikke',11,1)
RETURN @@ERROR
END
END
ELSE
-- UKJENT FUNKSJON
BEGIN
RAISERROR('Denne funksjonen finnes ikke',11,1)
RETURN @@ERROR
END
END
GO
Posted via email from Henris blogg
Sql/sqlserver/sp/stored procedure: Finne alle records laget en dag
Å finne start /slutt på en dag er et forholdsvis vanlig behov.
Her er to sqlscript for å lage to funksjoner som returner start og slutt på inndato.
(2 ms oppløsning på sluttdato…)
Lenger ned kan du se de i bruk:
CREATE function dbo.StartOfDay
(
@Date DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(d,DATEDIFF(d,0,@Date),0)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE function dbo.EndOfDay
(
@Date DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(ms,-2,DATEADD(d,1,dbo.StartOfDay(@Date)))
END
Denne prosedyren finner default alle poster laget i dag.
Bruker funksjonene over for å finne dagen i dag sin start og slutt:
USE [ENDATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HentHendelserVarslet]
-- Add the parameters for the stored procedure here
@FraDato DateTime = null,
@Tildato DateTime = null,
@HendelseTypeID smallint = null
AS
BEGIN
IF @FraDato IS NULL
SET @FraDato = dbo.StartOfday(GETDATE())
IF @Tildato IS NULL
SET @Tildato= dbo.EndOfday(@FraDato)
SET NOCOUNT ON;
SELECT
V.HendelseInformasjonID, I.HendelseTypeID,
T.HendelseType, V.RaadgiverNr, V.Antall,
V.LoggetDato, I.Beskrivelse
FROM HendelseVarslet AS V
INNER JOIN
HendelseInformasjon AS I
ON V.HendelseInformasjonID=I.HendelseInformasjonID
INNER JOIN
HendelseType T
ON T.HendelseTypeId = I.HendelseTypeId
WHERE
V.LoggetDato>=@FraDato
AND
V.LoggetDato<=@TilDato
AND
(I.HendelseTypeID=@HendelseTypeID OR @HendelseTypeID IS NULL)
END
GO
Posted via email from Henris blogg
Visual Studio/c#: Åpne asmx.cs fil i code editor
På min Visual Studio 2008 åpner web-service codebehind-filer (asmx.cs)
seg i Design Mode når jeg dobbeltklikker de.
Det er garantert IKKE det jeg ønsker.
For å få de til å åpne seg i Code View:
i Visual Studio høyreklikk filen og velg Open With
velg CSharp Editor og klikk [Set as Default] og velg OK.
Nå kan du dobbeltklikke i vei… :)
Posted via email from Henris blogg
c#: mappe/caste automatisk fra en klasse til en annen
Ved å lage en ny metode kan vi automatisk konvertere et objekt til et annet.
Uten å måtte caste eller lage egne mapping-funksjoner.
Ønsker du kun eksplisitt konvertering kan du bruke nøkkelordet explicit i stedet for i metodedefinisjonen.
(Denne koden utvider samtidig en klasse som er returnert/generert automatisk fra en Webtjeneste,
og legger til en ny metode på denne vha partial class.
Dersom du skal gjøre det må du passe på at namespacene er like for
klassen i Reference.cs og din utvidede klasse)
Eksempel:
namespace BOHjelpeklasser
{
namespace ProsesserAgent
{
/// <summary>
/// Utvider classen returnert fra ProsesserAgent med hjelpemetoder
/// </summary>
public partial class BrukerEntitetExt :Entitet
{
static public implicit operator BrukerEntitet(BrukerEntitetExt bruker)
{
var retur = new BrukerEntitet()
{
arbeidsgiver = bruker.arbeidsgiver,
brukerID = bruker.brukerID,
epostadresse = bruker.epostadresse,
etternavn = bruker.epostadresse,
fornavn = bruker.fornavn,
host = bruker.host,
ID = bruker.ID,
IsUptodate = bruker.IsUptodate,
poststed = bruker.poststed,
rådgivernummer = bruker.rådgivernummer,
Tag = bruker.Tag,
telefonnummer = bruker.telefonnummer
};
return retur;
}
}
}
}
Posted via email from Henris blogg
Oppdatert CookieUtil
Oppdatert cookieutil som tar med brukernavn i cookie, og sletter cookie dersom dette har endret seg, dvs ny bruker har logget på samme maskin.
1: /// <summary>
2: /// util for å serialize /deserialize objekter til cookies
3: /// </summary>
4: public static class CookieUtils
5: { 6: 7: /// <summary>
8: /// Henter satt cookie som object, returnerer null hvis cookie ikke finnes
9: /// </summary>
10: /// <param name="navn"></param>
11: /// <returns></returns>
12: public static T GetCookie<T>(string navn) where T : class, new()
13: { 14: 15: try
16: { 17: 18: HttpCookie c = HttpContext.Current.Request.Cookies[navn];19: if (c == null || string.IsNullOrEmpty(c.Value))
20: return default(T);
21: 22: 23: string[] verdier = c.Value.Split('|');
24: 25: 26: 27: //fjern cookie hvis den er for en annen bruker
28: string brukerident = verdier[0]; //første==brukerident
29: if (brukerident != Kontroller.HentCurrentUser())
30: { 31: SlettCookie(navn);32: return default(T);
33: }34: int n = 1; //start på 1, 0==brukerident...
35: T objekt = new T();
36: 37: Type type = objekt.GetType();38: //Type type = objekt.GetType();
39: FieldInfo[] felter = type.GetFields();40: foreach (FieldInfo feltinf in felter)
41: { 42: feltinf.SetValue(objekt, verdier[n++]); 43: } 44: 45: PropertyInfo[] props = type.GetProperties();46: foreach (PropertyInfo propinf in props)
47: { 48: SetProperty(propinf, verdier[n++], objekt); 49: } 50: 51: 52: 53: return objekt;
54: }55: catch (Exception ex)
56: {57: throw new BasisException(ex);
58: } 59: 60: 61: }62: /// <summary>
63: /// Lager cookie av gjeldende objekt som utløper om 10 timer
64: /// </summary>
65: /// <param name="navn"></param>
66: /// <param name="objekt"></param>
67: public static void SetCookie(string navn, object objekt)
68: { 69: SetCookie(navn, objekt, DateTime.Now.AddHours(10)); 70: } 71: 72: /// <summary>
73: /// Lager cookie av gjeldende objekt
74: /// </summary>
75: /// <param name="navn"></param>
76: /// <param name="objekt"></param>
77: /// <param name="utløper"></param>
78: public static void SetCookie(string navn, object objekt, DateTime utløper)
79: { 80: 81: 82: try
83: { 84: 85: StringBuilder streng = new StringBuilder();
86: streng.Append(Kontroller.HentCurrentUser() + "|");
87: Type type = objekt.GetType(); 88: FieldInfo[] felter = type.GetFields();89: foreach (FieldInfo feltinf in felter)
90: {91: if (feltinf != null)
92: {93: object verdi = feltinf.GetValue(objekt);
94: streng.Append(verdi.ToString() + "|");
95: 96: } 97: } 98: 99: PropertyInfo[] props = type.GetProperties();100: foreach (PropertyInfo propinf in props)
101: {102: if (propinf != null)
103: {104: object verdi = propinf.GetValue(objekt, null);
105: streng.Append(verdi.ToString() + "|");
106: } 107: } 108: 109: HttpCookie c = new HttpCookie(navn, streng.ToString());
110: 111: c.Expires = utløper; 112: 113: if (HttpContext.Current.Request.Cookies[navn] != null)
114: //finnes fra før
115: HttpContext.Current.Response.Cookies.Set(c);116: else
117: //finnes ikke, lag ny
118: HttpContext.Current.Response.Cookies.Add(c); 119: 120: }121: catch (Exception ex)
122: {123: throw new BasisException(ex);
124: } 125: } 126: 127: 128: /// <summary>
129: /// sletter en satt cookie
130: /// (resetter verdi og setter utløpsdato tilbake i tid)
131: /// </summary>
132: /// <param name="navn"></param>
133: public static void SlettCookie(string navn)
134: {135: //tømmer verdi og setter utløpt for ett år siden
136: HttpCookie c = new HttpCookie(navn, "");
137: c.Expires = DateTime.Now.AddYears(-1); 138: 139: if (HttpContext.Current.Request.Cookies[navn] != null)
140: //finnes fra før
141: HttpContext.Current.Response.Cookies.Set(c); 142: 143: } 144: 145: 146: 147: 148: 149: /// <summary>
150: /// Hjelpemetode for å sette en verdi via reflection etc
151: /// </summary>
152: /// <param name="pInfo"></param>
153: /// <param name="propertyValue"></param>
154: /// <param name="objectToSetValue"></param>
155: private static void SetProperty(PropertyInfo pInfo, string propertyValue, object objectToSetValue)
156: { 157: Type pType = pInfo.PropertyType; 158: 159: if (pType == typeof(string))
160: pInfo.SetValue(objectToSetValue, propertyValue, null);
161: else if (pType == typeof(bool))
162: pInfo.SetValue(objectToSetValue, Convert.ToBoolean(propertyValue), null);
163: else if (pType == typeof(int))
164: pInfo.SetValue(objectToSetValue, Convert.ToInt32(propertyValue), null);
165: else
166: throw new NotImplementedException("Objektet har felttype som ikke er implementert enda..");
167: } 168: }
Posts
No comments :
Post a Comment