------------------------------
Utgangspunkt
------------------------------
ALTER PROCEDURE [dbo].[spShowNotes]
(
@private bit,
@employeeID smallint,
@custID smallint
)
AS
BEGIN
SET NOCOUNT ON;
select title,body,regdate,status from tblNote
where private=@private and active=1 and employeeID=@employeeID and custID=@custID
order by noteID desc
END
------------------------------
Metode 1
------------------------------
ALTER PROCEDURE [dbo].[spShowNotes]
(
@private bit,
@employeeID smallint,
@custID smallint
)
AS
BEGIN
SET NOCOUNT ON;
select title,body,regdate,status from tblNote
where private=@private
and active=1
and ( @private = 'false' OR employeeID=@employeeID )
and custID=@custID
order by noteID desc
END
------------------------------
Metode 2
------------------------------
ALTER PROCEDURE [dbo].[spShowNotes]
(
@private bit,
@employeeID smallint,
@custID smallint
)
AS
BEGIN
SET NOCOUNT ON;
Declare @SQL as Varchar(200)
if @private = false
Set @SQL = 'select title,body,regdate,status from tblNote where private= false and active=1 and employeeID=' + Cast(@employeeID as Varchar(10)) + ' and custID= ' + Cast(@custID as VArchar(10)) + ' order by noteID desc'
Else
Set @SQL = 'select title,body,regdate,status from tblNote where private= true and active=1 and custID= ' + Cast(@custID as VArchar(10)) + ' order by noteID desc'
Exec(@sql)
END
|
