Turvenn.no
Webutvikler kurs ASP.NET, C#, CSS mm.

WebRessurs.no - Webprogrammering og ressurs innen ASP, ASP.NET, PHP, SQL, HTML, CSS, Javascript, XML, C#, VB, Flash  mm.

Brukernavn: 
Passord:

Registrèr deg Glemt passord?
Logg deg inn




  ASP.net (199)
  ASP 3.0 (111)
  PHP (30)
  HTML (66)
  SQL (89)
  CSS (46)
  XML (7)
  JavaScript (78)
  Diverse kode (13)


  Programmering (22)
  System og drift (15)
  Trafikk og inntekt (11)
  Guider og tips (22)
  Nyttig lesestoff (23)
  Web forum (604)
  Link galleri (566)


  ASP.NET kurs(5)
  CSS kurs (2)
  JQuery kurs (2)


  Øk trafikken (8)
  Facebook App. (4)
  Microsoft CEO (6)


  Server & web
  Internett & epost
  Systemverktøy
  Sikkerhet
  Fildeling
  Lyd & media
  Diverse software


  Domenesalg
  Metagenerator
  Websikre farger
  Gratis epost
  WebMail


  Bli medlem!
  Siste innlegg
  Gjestebok
  Tips en venn
  Kontakt oss
  Forsiden




Kode Artikler
Linker


Mål internetthastigheten din.


Stored Procedure med valgfrie parametre

av Øyvind A. Isaksen
 
Her vises 2 eksempler på hvordan i en Stored Procedure kan oppdatere data i en tabell. Det spesielle er at ikke alle parametre MÅ tilordnes en verdi, kun de du ønsker å oppdatere. I eksempel 1 benytter man ISNULL funksjonen, i eksempel 2 eksekverer man en SQL vhja den innebygde prosedyren "sp_executesql" .

-------------------------------------
Eksempel 1
-------------------------------------

ALTER PROCEDURE [dbo].[spSetArticle] (
@ArticleId int,
@ParentId int = null,
@TemplateId smallint = null,
@SiteId smallint = null,
@Name varchar(256) = null,
@Url varchar(256) = null,
@Created smalldatetime = null,
@Guid uniqueidentifier = null,
@Deleted bit = null,
@ShowInMenu bit = null,
@PublishStart smalldatetime = null,
@PublishEnd smalldatetime = null,
@Published bit = null,
@SortOrder smallint = null
)
AS
BEGIN
SET NOCOUNT ON

Update tblArticle
set ParentId = ISNULL(@ParentId , ParentId),
TemplateId = ISNULL(@TemplateId , TemplateId),
SiteId = ISNULL(@SiteId , SiteId),
[Name] = ISNULL(@Name , [Name]),
Url = ISNULL(@Url , Url),
Created = ISNULL(@Created , Created),
Guid = ISNULL(@Guid , Guid),
Deleted = ISNULL(@Deleted , Deleted),
ShowInMenu = ISNULL(@ShowInMenu , ShowInMenu),
PublishStart = ISNULL(@PublishStart , PublishStart),
PublishEnd = ISNULL(@PublishEnd , PublishEnd),
Published = ISNULL(@Published , Published),
Sortorder = ISNULL(@SortOrder , Sortorder)
where [Id] = @ArticleId
END



-------------------------------------
Eksempel 2
-------------------------------------

Opps: Får du "Permission" (rettigheter) feil må du gi brukeren "public" retigheter til å lese/skrive på tabellen du skal oppdatere.
I dettetilfellet på tabellen "tblArticle".


ALTER PROCEDURE [dbo].[spSetArticle] (
@ArticleId int,
@ParentId int = null,
@TemplateId smallint = null,
@SiteId smallint = null,
@Name varchar(256) = null,
@Url varchar(256) = null,
@Created smalldatetime = null,
@Guid uniqueidentifier = null,
@Deleted bit = null,
@ShowInMenu bit = null,
@PublishStart smalldatetime = null,
@PublishEnd smalldatetime = null,
@Published bit = null,
@SortOrder smallint = null
)
AS
BEGIN
SET NOCOUNT ON

declare @strSQL nvarchar(4000),
@ctr int
set @ctr = 0
set @strSQL = 'Update tblArticle Set '

if @ParentId is not null
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'ParentId = ' + cast(@ParentId as varchar(10)) + ', '
end
if @TemplateId is not null
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'TemplateId = ' + cast(@TemplateId as varchar(10)) + ', '
end

if @SiteId is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'SiteId = ' + cast(@SiteId as varchar(10)) + ', '
end
if @Name is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + '[Name] = ''' + @Name + '", '
end
if @Url is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'Url = ''' + @Url + ''', '
end
if @Created is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'Created = ''' + cast(@Created as varchar(50)) + ''', '
end
if @Guid is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'Guid = ' + cast(@Guid as varchar(100)) + ', '
end
if @Deleted is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'Deleted = ' + cast(@Deleted as varchar(2)) + ', '
end
if @ShowInMenu is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'ShowInMenu = ' + cast(@ShowInMenu as varchar(2)) + ', '
end
if @PublishStart is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'PublishStart = ''' + cast(@PublishStart as varchar(50)) + ''', '
end

if @PublishEnd is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'PublishEnd = ''' + cast(@PublishEnd as varchar(50)) + ''', '
end
if @Published is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'Published = ' + cast(@Published as varchar(2)) + ', '
end
if @SortOrder is not NULL
begin
set @ctr = @ctr + 1
set @strSQL = @strSQL + 'SortOrder = ' + cast(@SortOrder as varchar(10)) + ', '
end

if @ctr > 0
begin
set @strSQL = left(@strSQL, len(@strSQL)-1)
set @strSQL = @strSQL + ' Where [Id] = ' + cast(@ArticleId as varchar(10))
end

if @ctr > 0 and @ArticleId is not null
begin
Exec sp_executesql @strSQL
end
END

WebRessurs.no anbefaler:    StackOverflow.com | Experts-Exchange.com | W3schools | ASP.NET | Codeproject | 4Guys
WebRessurs.no er utviklet og drives av SoftMaker LTD
Sett som startside: [ ]. Bokmerk denne siden: [ klikk ]. Sitemap. http://twitter.com/webressurs_no/. Antall brukersesjoner: 9760706.
Copyright WebRessurs.no © 2003 - 2015