tazarvmmr
یک شنبه 27 آذر 1390, 22:12 عصر
سلام به همگی
یک sp من نوشتم که برای نرم افزار اتوماسیون اداری کار میکنه، وظیفش هم اینه که نامه های خاصی رو نشون میده، میخوام یک پارامتر ورودی بهش اضافه کنم که بنام File مقدار این پارامتر یا برابر یک رشته خالی خواهد بود یا برابر یک رشته متشکل از جند عدد که با یک کاراکتر خاص از هم جدا شدند، مثلا چیزی شبیه به این:
CC 12 BB 34 BB 125 BB 4 DD
داخل sp بعدا این رشته رو با دستوراتی مثل Replace تبدیلش میکنم به چیزی شبیه این:
(LetterId = 12) or (LetterId = 34) or (LetterId = 125) or (LetterId = 4)
بااین قسمت کاری ندارم، سوال اصلی اینجاست، اول این sp رو نگاه کنید:
ALTER procedure [dbo].[sp_ShowLetters]
@LetterId int = -1,
@LevelId int = -1,
@UserId int = -1,
@SectionId int = -1,
@CustomerId int = -1,
@OfficeId int = -1,
@ProjectId int = -1,
@TypeId int = -1,
@Direction int = -1,
@SubjectId int = -1,
@PriorityId int = -1,
@Deleted int = 0
as
SELECT
dbo.fParaphsQTY(Letters.Id, @LevelId) As ParaphsQTY,
Letters.IssueDate As LetterIssueDate,
Letters.IssueTime As LetterIssueTime,
Descriptions.DescriptionCaption AS LetterDescription,
LetterSubjects.LetterSubjects AS LetterSubject,
ISNULL(Sexes.ShortCaption + N' ' + Users.UserName, '') AS LetterUserName,
ISNULL(Sexes_1.ShortCaption + N' ' + Users_1.UserName, '') AS ArchivedUserName,
LetterTypes.TypeCaption AS LetterTypeCaption,
LetterTypes.TypeImageId AS LetterTypeImageId,
LetterLevels.LevelCaption AS LetterLevelCaption,
Letters.LevelCode as LetterLevelId,
Letters.TypeCode as LetterTypeId,
Letters.SubjectCode as LetterSubjectId,
Letters.PriorityCode as LetterPriorityId,
Letters.ID AS LetterId,
Letters.Deleted,
Letters.UserCode AS LetterUserId,
Letters.SectionCode AS LetterSectionID,
RequestedProjects.RequestProjectName AS LetterProjectName,
Letters.ProjectCode AS LetterProjectId,
Letters.OfficeCode AS LetterOfficeId,
CustomersPfix.CustomerPfixCaption + N' ' + Offices.OfficeName AS LetterOfficeName,
Letters.CustomerCode AS LetterCustomerId,
CustomersPfix_1.CustomerPfixCaption + N' ' + Customers.CustomerName AS LetterCustomerName,
Letters.Direction AS DirectionId,
Letters.Number AS LetterNumber,
Letters.ReferTo
FROM
ProjectNames LEFT OUTER JOIN
RequestedProjects ON ProjectNames.RequestId = RequestedProjects.Id RIGHT OUTER JOIN
Letters LEFT OUTER JOIN
Customers LEFT OUTER JOIN
CustomersPfix AS CustomersPfix_1 ON Customers.CustomerNameSuffixId = CustomersPfix_1.Id ON Letters.CustomerCode = Customers.Id LEFT OUTER JOIN
Offices LEFT OUTER JOIN
CustomersPfix ON Offices.OfficeSuffixId = CustomersPfix.Id ON Letters.OfficeCode = Offices.Id ON ProjectNames.Id = Letters.ProjectCode LEFT OUTER JOIN
LetterLevels ON Letters.LevelCode = LetterLevels.Id LEFT OUTER JOIN
LetterTypes ON Letters.TypeCode = LetterTypes.Id LEFT OUTER JOIN
Users AS Users_1 LEFT OUTER JOIN
Sexes AS Sexes_1 ON Users_1.UserSexId = Sexes_1.SexId ON Letters.ArchivedUserCode = Users_1.Id LEFT OUTER JOIN
Sexes RIGHT OUTER JOIN
Users ON Sexes.SexId = Users.UserSexId ON Letters.UserCode = Users.Id LEFT OUTER JOIN
LetterSubjects ON Letters.SubjectCode = LetterSubjects.ID LEFT OUTER JOIN
Descriptions ON Letters.DescriptionId = Descriptions.Id
WHERE
(Letters.ID = @LetterId) AND
(Letters.Deleted = Case When @Deleted = 1 then 1
When @Deleted = 0 then 0
When @Deleted = -1 then Letters.Deleted End) And
(Letters.OfficeCode = Case When @OfficeId = -1 then Letters.OfficeCode
When @OfficeId <> -1 then @OfficeId End) And
(Letters.ProjectCode = Case When @ProjectId = -1 then Letters.ProjectCode
When @ProjectId <> - 1 then @ProjectId End) And
(Letters.CustomerCode = Case When @CustomerId = -1 then Letters.CustomerCode
When @CustomerId <> -1 then @CustomerId End) And
(Letters.CustomerCode = Case When @SectionId = -1 then Letters.CustomerCode
When @SectionId <> -1 then @SectionId End) And
(Letters.UserCode = Case When @UserId = -1 then Letters.UserCode
When @UserId <> -1 then @UserId End) And
(Letters.ID = Case When @LetterId = -1 then Letters.ID
When @LetterId <> -1 then @LetterId End) And
(Letters.TypeCode = Case When @TypeId = -1 then Letters.TypeCode
When @TypeId <> -1 then @TypeId End) And
(Letters.Direction = Case When @Direction = -1 then Letters.Direction
When @Direction <> -1 then @Direction End) And
(Letters.SubjectCode = Case When @SubjectId = -1 then Letters.SubjectCode
When @SubjectId <> -1 then @SubjectId End) And
(Letters.LevelCode = Case When @LevelId = -1 then Letters.LevelCode
When @LevelId <> -1 then @LevelId End) And
(Letters.PriorityCode = Case When @PriorityId = -1 then Letters.PriorityCode
When @PriorityId <> -1 then @PriorityId End)
Return @@RowCount
اونچیزی که میخوام چیزی شبیه به اینه که بر اساس مقدار پارامتر File، این دستور به شرط Where اضافه بشه:
And
(Letters.Id in Case When @Folder <> '' then (Select LetterId From Files Where + @Files)
Else (Select Id from Letters) End)
خوب این دستور آخری خطا داره و کار نمیکنه! میخوام بدونم کسی میتونه راهنمایی کنه؟
ممنون
یک sp من نوشتم که برای نرم افزار اتوماسیون اداری کار میکنه، وظیفش هم اینه که نامه های خاصی رو نشون میده، میخوام یک پارامتر ورودی بهش اضافه کنم که بنام File مقدار این پارامتر یا برابر یک رشته خالی خواهد بود یا برابر یک رشته متشکل از جند عدد که با یک کاراکتر خاص از هم جدا شدند، مثلا چیزی شبیه به این:
CC 12 BB 34 BB 125 BB 4 DD
داخل sp بعدا این رشته رو با دستوراتی مثل Replace تبدیلش میکنم به چیزی شبیه این:
(LetterId = 12) or (LetterId = 34) or (LetterId = 125) or (LetterId = 4)
بااین قسمت کاری ندارم، سوال اصلی اینجاست، اول این sp رو نگاه کنید:
ALTER procedure [dbo].[sp_ShowLetters]
@LetterId int = -1,
@LevelId int = -1,
@UserId int = -1,
@SectionId int = -1,
@CustomerId int = -1,
@OfficeId int = -1,
@ProjectId int = -1,
@TypeId int = -1,
@Direction int = -1,
@SubjectId int = -1,
@PriorityId int = -1,
@Deleted int = 0
as
SELECT
dbo.fParaphsQTY(Letters.Id, @LevelId) As ParaphsQTY,
Letters.IssueDate As LetterIssueDate,
Letters.IssueTime As LetterIssueTime,
Descriptions.DescriptionCaption AS LetterDescription,
LetterSubjects.LetterSubjects AS LetterSubject,
ISNULL(Sexes.ShortCaption + N' ' + Users.UserName, '') AS LetterUserName,
ISNULL(Sexes_1.ShortCaption + N' ' + Users_1.UserName, '') AS ArchivedUserName,
LetterTypes.TypeCaption AS LetterTypeCaption,
LetterTypes.TypeImageId AS LetterTypeImageId,
LetterLevels.LevelCaption AS LetterLevelCaption,
Letters.LevelCode as LetterLevelId,
Letters.TypeCode as LetterTypeId,
Letters.SubjectCode as LetterSubjectId,
Letters.PriorityCode as LetterPriorityId,
Letters.ID AS LetterId,
Letters.Deleted,
Letters.UserCode AS LetterUserId,
Letters.SectionCode AS LetterSectionID,
RequestedProjects.RequestProjectName AS LetterProjectName,
Letters.ProjectCode AS LetterProjectId,
Letters.OfficeCode AS LetterOfficeId,
CustomersPfix.CustomerPfixCaption + N' ' + Offices.OfficeName AS LetterOfficeName,
Letters.CustomerCode AS LetterCustomerId,
CustomersPfix_1.CustomerPfixCaption + N' ' + Customers.CustomerName AS LetterCustomerName,
Letters.Direction AS DirectionId,
Letters.Number AS LetterNumber,
Letters.ReferTo
FROM
ProjectNames LEFT OUTER JOIN
RequestedProjects ON ProjectNames.RequestId = RequestedProjects.Id RIGHT OUTER JOIN
Letters LEFT OUTER JOIN
Customers LEFT OUTER JOIN
CustomersPfix AS CustomersPfix_1 ON Customers.CustomerNameSuffixId = CustomersPfix_1.Id ON Letters.CustomerCode = Customers.Id LEFT OUTER JOIN
Offices LEFT OUTER JOIN
CustomersPfix ON Offices.OfficeSuffixId = CustomersPfix.Id ON Letters.OfficeCode = Offices.Id ON ProjectNames.Id = Letters.ProjectCode LEFT OUTER JOIN
LetterLevels ON Letters.LevelCode = LetterLevels.Id LEFT OUTER JOIN
LetterTypes ON Letters.TypeCode = LetterTypes.Id LEFT OUTER JOIN
Users AS Users_1 LEFT OUTER JOIN
Sexes AS Sexes_1 ON Users_1.UserSexId = Sexes_1.SexId ON Letters.ArchivedUserCode = Users_1.Id LEFT OUTER JOIN
Sexes RIGHT OUTER JOIN
Users ON Sexes.SexId = Users.UserSexId ON Letters.UserCode = Users.Id LEFT OUTER JOIN
LetterSubjects ON Letters.SubjectCode = LetterSubjects.ID LEFT OUTER JOIN
Descriptions ON Letters.DescriptionId = Descriptions.Id
WHERE
(Letters.ID = @LetterId) AND
(Letters.Deleted = Case When @Deleted = 1 then 1
When @Deleted = 0 then 0
When @Deleted = -1 then Letters.Deleted End) And
(Letters.OfficeCode = Case When @OfficeId = -1 then Letters.OfficeCode
When @OfficeId <> -1 then @OfficeId End) And
(Letters.ProjectCode = Case When @ProjectId = -1 then Letters.ProjectCode
When @ProjectId <> - 1 then @ProjectId End) And
(Letters.CustomerCode = Case When @CustomerId = -1 then Letters.CustomerCode
When @CustomerId <> -1 then @CustomerId End) And
(Letters.CustomerCode = Case When @SectionId = -1 then Letters.CustomerCode
When @SectionId <> -1 then @SectionId End) And
(Letters.UserCode = Case When @UserId = -1 then Letters.UserCode
When @UserId <> -1 then @UserId End) And
(Letters.ID = Case When @LetterId = -1 then Letters.ID
When @LetterId <> -1 then @LetterId End) And
(Letters.TypeCode = Case When @TypeId = -1 then Letters.TypeCode
When @TypeId <> -1 then @TypeId End) And
(Letters.Direction = Case When @Direction = -1 then Letters.Direction
When @Direction <> -1 then @Direction End) And
(Letters.SubjectCode = Case When @SubjectId = -1 then Letters.SubjectCode
When @SubjectId <> -1 then @SubjectId End) And
(Letters.LevelCode = Case When @LevelId = -1 then Letters.LevelCode
When @LevelId <> -1 then @LevelId End) And
(Letters.PriorityCode = Case When @PriorityId = -1 then Letters.PriorityCode
When @PriorityId <> -1 then @PriorityId End)
Return @@RowCount
اونچیزی که میخوام چیزی شبیه به اینه که بر اساس مقدار پارامتر File، این دستور به شرط Where اضافه بشه:
And
(Letters.Id in Case When @Folder <> '' then (Select LetterId From Files Where + @Files)
Else (Select Id from Letters) End)
خوب این دستور آخری خطا داره و کار نمیکنه! میخوام بدونم کسی میتونه راهنمایی کنه؟
ممنون