More efficent way to achive the following..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

More efficent way to achive the following..

Author

Topic

airzoom90
Starting Member

1 Post

Posted - 2004-03-02 : 05:40:56

,

I'm looking for a way to do the following in a more efficent way within a stored procedure. Basically the SP will return certain rows depending on the filters, it was all done originally as dynamic SQL but had to be reviewed due to performance. I've managed to rewrite most of the SP using the COALESCE function on the other filters but I think the COALESCE function is not going to help with the following -

IF @Time <> ''
IF Left(@Time,1) <> '='
SET @SQL = @SQL + ' AND (SUBSTRING(pd.vchTime,28,2) IN (''' + @Time + ''') OR SUBSTRING(pd.vchTime,40,2) IN (''' + @Time + ''') OR SUBSTRING(pd.vchTime,46,2) IN (''' + @Time + '''))'
ELSE
IF @Time = '=Morning'
SET @SQL = @SQL + 'AND (SUBSTRING(pd.vchTime,28,5) BETWEEN ''00:01'' AND ''10:59'')'
IF @Time = '=Lunchtime'
SET @SQL = @SQL + 'AND (SUBSTRING(pd.vchTime,40,5) BETWEEN ''11:00'' AND ''14:59'')'
IF @Time = '=Afternoon'
SET @SQL = @SQL + 'AND (SUBSTRING(pd.vchTime,46,5) BETWEEN ''15:00'' AND ''23:59'')'

IF @Sector <> ''
SET @SQL = @SQL + ' AND CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector END IN (''' + @Sector + ''')'
--ELSE
-- SET @SQL = @SQL + ' AND CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector END NOT LIKE ''VACHER DOD%'''

Can any one suggest a method to achive this

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 05:59:33

Well one bit is

and (SUBSTRING(pd.vchTime,28,5) BETWEEN '00:01' AND '10:59' or @Time <> '=Morning')
and (SUBSTRING(pd.vchTime,40,5) BETWEEN '11:00' AND '14:59' or @Time <> '=Lunchtime')
and (SUBSTRING(pd.vchTime,46,5) BETWEEN '15:00' AND '23:59' or @Time <> '=Afternoon')

But this won't be very efficient. The problem is your table structure which you should think about redesigning or building redundent structures to spport the query if it is important.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources