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

KODE EKSEMPLER

ASP.net (199)

ASP 3.0 (111)

PHP (30)

HTML (66)

SQL (89)

CSS (46)

XML (7)

JavaScript (78)

Diverse kode (13)

ARTIKLER / RESSURSER

Programmering (22)

System og drift (15)

Trafikk og inntekt (11)

Guider og tips (22)

Nyttig lesestoff (23)

Web forum (604)

Link galleri (565)

KURS / TUTORIALS

ASP.NET kurs(5)

CSS kurs (2)

JQuery kurs (2)

FILM / MEDIA

Øk trafikken (8)

Facebook App. (4)

Microsoft CEO (6)

GRATIS SOFTWARE

Server & web

Internett & epost

Systemverktøy

Sikkerhet

Fildeling

Lyd & media

Diverse software

TJENESTER

Domenesalg

Metagenerator

Websikre farger

WebMail

ANNET

Bli medlem!

Siste innlegg

Gjestebok

Tips en venn

Kontakt oss

Forsiden

INTERNETT HASTIGHET

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 er utviklet og drives av SoftMaker
Sett som startside: [ ]. Bokmerk denne siden: [ klikk ]. Sitemap. http://twitter.com/webressurs_no/. Antall brukersesjoner: 14722503.
Copyright WebRessurs.no © 2003 - 2018