filmov
tv
SQL Server @@Identity vs Scope_Identity vs Ident_Current

Показать описание
CREATE Procedure [dbo].[GetContacts]
@pageIndex INT,
@pageSize INT,
@searchText NVARCHAR(500),
@orderField NVARCHAR(100),
@UserId UniqueIdentifier,
@AccountId NVARCHAR(500)
AS
DECLARE @sqlScript NVARCHAR(1000), @sqlScriptMax NVARCHAR(1000)
DECLARE @Start INT, @End INT, @MaxRecord INT
BEGIN
SET @orderField=NULL
END
DECLARE @permission SMALLINT
DECLARE @UserTypeId int
if @AccountId $$ ''
begin
IF @UserTypeId = 2
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
END
BEGIN
IF @permission=3 OR @permission=4 OR @permission=5
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
END
ELSE
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 and AccountId='+ @AccountId +' AND AccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
END
END
end
else
begin
IF @UserTypeId = 2
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 AND ServiceAccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
END
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 AND AccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
END
END
end
-- Set the max record.
BEGIN
END
-- Set the paging.
--SELECT @sqlScript
--SELECT @sqlScriptMax
@pageIndex INT,
@pageSize INT,
@searchText NVARCHAR(500),
@orderField NVARCHAR(100),
@UserId UniqueIdentifier,
@AccountId NVARCHAR(500)
AS
DECLARE @sqlScript NVARCHAR(1000), @sqlScriptMax NVARCHAR(1000)
DECLARE @Start INT, @End INT, @MaxRecord INT
BEGIN
SET @orderField=NULL
END
DECLARE @permission SMALLINT
DECLARE @UserTypeId int
if @AccountId $$ ''
begin
IF @UserTypeId = 2
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
END
BEGIN
IF @permission=3 OR @permission=4 OR @permission=5
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
END
ELSE
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 and AccountId='+ @AccountId +' AND AccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
END
END
end
else
begin
IF @UserTypeId = 2
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 AND ServiceAccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
END
BEGIN
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 AND AccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
END
END
end
-- Set the max record.
BEGIN
END
-- Set the paging.
--SELECT @sqlScript
--SELECT @sqlScriptMax