November 22, 2010 at 7:38 am
Hi, I am passing variables from a web form and returning a dataset based on the form input parameters. I'm just wondering if there is a better way to do this without using a lot of lines of code...
if AuthorID = '0'...it means return from ALL Author and not from specific Author
and same applied for StatusID
@month1 int,
@year1 int,
@month2 int,
@year2 int,
@authorID varchar(50),
@statusID int
AS
BEGIN
SET NOCOUNT ON;
-- both are ALL
if (@authorID = '0' AND @statusID = 0)
begin
SELECT aal.id,aal.docID,aal.RequestorID,aal.AuthorID,bb.CN as 'Author',b.CN as 'Requestor',aal.details,aal.docUpdated,aal.StatusID,s.english as 'Status',aal.RequestDate,aal.Comments,aal.ModifiedDate,aal.PlannedCloseDate
FROM tblAuthorAssignLog aal
INNER JOIN tbl_bc b on aal.RequestorId = b.bellpein
INNER JOIN tbl_bc bb on aal.AuthorId = bb.bellpein
INNER JOIN tblStatus s on s.id = aal.StatusID
WHERE
MONTH(aal.RequestDate) between @month1 and @month2 AND
YEAR(aal.RequestDate) between @year1 and @year2 AND
aal.AuthorID in (select AuthorUserID from tblAuthors where Active = 0) AND
aal.StatusID in (select id from tblStatus)
end
-- author = ALL, status NOT ALL
if (@authorID = '0' AND @statusID > 0)
begin
SELECT aal.id,aal.docID,aal.RequestorID,aal.AuthorID,bb.CN as 'Author',b.CN as 'Requestor',aal.details,aal.docUpdated,aal.StatusID,s.english as 'Status',aal.RequestDate,aal.Comments,aal.ModifiedDate,aal.PlannedCloseDate
FROM tblAuthorAssignLog aal
INNER JOIN tbl_bc b on aal.RequestorId = b.bellpein
INNER JOIN tbl_bc bb on aal.AuthorId = bb.bellpein
INNER JOIN tblStatus s on s.id = aal.StatusID
WHERE
MONTH(aal.RequestDate) between @month1 and @month2 AND
YEAR(aal.RequestDate) between @year1 and @year2 AND
aal.AuthorID in (select AuthorUserID from tblAuthors where Active = 0) AND
aal.StatusID = @statusID
end
-- author NOT ALL , status = ALL
if (@authorID <> '0' AND @statusID = 0)
begin
SELECT aal.id,aal.docID,aal.RequestorID,aal.AuthorID,bb.CN as 'Author',b.CN as 'Requestor',aal.details,aal.docUpdated,aal.StatusID,s.english as 'Status',aal.RequestDate,aal.Comments,aal.ModifiedDate,aal.PlannedCloseDate
FROM tblAuthorAssignLog aal
INNER JOIN tbl_bc b on aal.RequestorId = b.bellpein
INNER JOIN tbl_bc bb on aal.AuthorId = bb.bellpein
INNER JOIN tblStatus s on s.id = aal.StatusID
WHERE
MONTH(aal.RequestDate) between @month1 and @month2 AND
YEAR(aal.RequestDate) between @year1 and @year2 AND
aal.AuthorID = @authorID AND
aal.StatusID in (select id from tblStatus)
end
-- author NOT ALL, status NOT ALL
if (@authorID <> '0' AND @statusID > 0)
begin
SELECT aal.id,aal.docID,aal.RequestorID,aal.AuthorID,bb.CN as 'Author',b.CN as 'Requestor',aal.details,aal.docUpdated,aal.StatusID,s.english as 'Status',aal.RequestDate,aal.Comments,aal.ModifiedDate,aal.PlannedCloseDate
FROM tblAuthorAssignLog aal
INNER JOIN tbl_bc b on aal.RequestorId = b.bellpein
INNER JOIN tbl_bc bb on aal.AuthorId = bb.bellpein
INNER JOIN tblStatus s on s.id = aal.StatusID
WHERE
MONTH(aal.RequestDate) between @month1 and @month2 AND
YEAR(aal.RequestDate) between @year1 and @year2 AND
aal.AuthorID = @authorID AND
aal.StatusID = @statusID
end
November 22, 2010 at 9:29 am
It might be worth using temp tables.
CREATE TABLE #tmpAuthors (AuthorUserID int PRIMARY KEY CLUSTERED);
IF @author_id = 0
INSERT INTO #tmpAuthors
SELECT AuthorUserID
FROM tblAuthors
WHERE Active = 0
ELSE
INSERT INTO #tmpAuthors
VALUES (@author_id);
CREATE TABLE #tmpStatus(StatusID int PRIMARY KEY CLUSTERED);
IF @status_id = 0
INSERT INTO #tmpStatus
SELECT id
FROM tblStatus
ELSE
INSERT INTO #tmpStatus
VALUES (@status_id);
-- let's fix those date searches first:
DECLARE @StartDate datetime, @EndDate datetime;
SET @StartDate = convert(char(4), @year1) + right('00', convert(varchar(2), @month1)) + '01';
SET @EndDate = convert(char(4), @year2) + right('00', convert(varchar(2), @month2)) + '01';
SET @EndDate = DateAdd(month, 1, @EndDate); -- get the first of the next month
-- now join to the new temp tables:
SELECT aal.id,
aal.docID,
aal.RequestorID,
aal.AuthorID,
bb.CN as 'Author',
b.CN as 'Requestor',
aal.details,
aal.docUpdated,
aal.StatusID,
s.english as 'Status',
aal.RequestDate,
aal.Comments,
aal.ModifiedDate,
aal.PlannedCloseDate
FROM tblAuthorAssignLog aal
INNER JOIN tbl_bc b on aal.RequestorId = b.bellpein
INNER JOIN tbl_bc bb on aal.AuthorId = bb.bellpein
INNER JOIN tblStatus s on s.id = aal.StatusID
INNER JOIN #tmpAuthors ta ON ta.AuthorUserID = aal.AuthorID
INNER JOIN #tmpStatus ts ON ts.StatusID = aal.StatusID
WHERE aal.RequestDate >= @StartDate
AND aal.RequestDate < @EndDate -- less than the start of the next month...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 22, 2010 at 10:30 am
Looks like a catch-all query issue.
November 22, 2010 at 10:39 am
Thanks Wayne, that's an interesting solution. I am testing and will get back with results.
Lutz, I had looked at building a dynamic query string, but was thinking that it would still use multiple condition clauses and thought that would create redundancy...probably better than the original try.
November 22, 2010 at 10:50 am
Lutz - wouldn't the temp tables that I used handle that? Otherwise, you end up with 4 procs to handle this...
As long as there are just small amounts of data in those temp tables, it should be fine.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 22, 2010 at 11:21 am
WayneS (11/22/2010)
Lutz - wouldn't the temp tables that I used handle that? Otherwise, you end up with 4 procs to handle this...
As long as there are just small amounts of data in those temp tables, it should be fine.
I was referring to the dynamic sql solution. Something like following code.
That would just need one proc and separates the "static" from the dynamic part of the query.
What I'd most probably do: test both solutions and decide based on the performance results.
I like your approach since it's still "real SQL", that's most probably easier to maintain than the dynamic approach. But that'll depend on the coding standards used at Marcus' company, too.
Edit: One more thing: Marcus, if you decide to use the dynamic approach, make sure to incorporate Waynes [RequestDate] solution.
DECLARE @month1 INT,
@year1 INT,
@month2 INT,
@year2 INT,
@authorID VARCHAR(50),
@statusID INT
SELECT @authorID='0',@statusID=1
DECLARE @str_fix VARCHAR(4000)=
'SELECT
aal.id,
aal.docID,
aal.RequestorID,
aal.AuthorID,
bb.CN as ''Author'',
b.CN as ''Requestor'',
aal.details,
aal.docUpdated,
aal.StatusID,
s.english as ''Status'',
aal.RequestDate,
aal.Comments,
aal.ModifiedDate,
aal.PlannedCloseDate
FROM tblAuthorAssignLog aal
INNER JOIN tbl_bc b on aal.RequestorId = b.bellpein
INNER JOIN tbl_bc bb on aal.AuthorId = bb.bellpein
INNER JOIN tblStatus s on s.id = aal.StatusID
WHERE
MONTH(aal.RequestDate) between @month1 and @month2 AND
YEAR(aal.RequestDate) between @year1 and @year2 '
DECLARE @str_case VARCHAR(500)
SET @str_case =
CASE
WHEN @authorID = '0' AND @statusID = 0
THEN 'AND aal.AuthorID in (select AuthorUserID from tblAuthors where Active = 0) AND
aal.StatusID in (select id from tblStatus)'
WHEN @authorID = '0' AND @statusID > 0
THEN 'AND aal.AuthorID in (select AuthorUserID from tblAuthors where Active = 0) AND
aal.StatusID = @_statusID'
WHEN @authorID <> '0' AND @statusID = 0
THEN 'AND aal.AuthorID = @_authorID AND aal.StatusID in (select id from tblStatus)'
WHEN @authorID <> '0' AND @statusID > 0
THEN 'AND aal.AuthorID = @_authorID AND aal.StatusID = @_statusID'
END
SELECT @str_fix = @str_fix + @str_case
EXEC sp_executesql @str_fix,
N'@_authorID VARCHAR(50), @_statusID int, ...',
@_authorID = @authorID, @_statusID = @statusID, ...
November 22, 2010 at 11:24 am
@Lutz: "coding standards"
LOL...sad to say...it's pretty much whatever gets the job done in the fastest time possible.
November 22, 2010 at 12:05 pm
Marcus Farrugia (11/22/2010)
@Lutz: "coding standards"
LOL...sad to say...it's pretty much whatever gets the job done in the fastest time possible.
That's not sad, it rather gives you total flexibility 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply