Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDateI don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =
dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =
utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/