Have this Select query SP want to add condition

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 2005 Forums

Transact-SQL (2005)

Have this Select query SP want to add condition

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-11 : 14:43:46

I want to apply a where condition with the following, is it possible or it has to be conmpletely modified to a dynamic SQL?:

If Modulename='SU' then 'join Tab_Submittals su on Modulerecordid = su.suid and su.deleted = '0'

If Modulename='RF' then 'join Tab_RFI RF on Modulerecordid = rf.rfid and RF.deleted = '0'

If Modulename='RM' then 'join Tab_RM RM on Modulerecordid = RM.RMID and RM.deleted = '0'

-- This STP returns the mytasks actionitems and reviewers list

ALTER PROCEDURE [dbo].[USP_GetMyAlerts]
(
		@userid 		int
)

AS

DECLARE @ErrorCode				int

SET NOCOUNT ON
SELECT @ErrorCode = @@Error
IF @ErrorCode = 0
BEGIN
	Select actionid, 'ACT' as Type, actiondescription, rtrim(isnull(ModuleName,'')) as ModuleName,ModuleRecordID,
[days] = CASE WHEN duedate < GetDate()
THEN -1 * DATEDIFF(Day, duedate, GetDate())
ELSE CONVERT(varchar(20), DATEDIFF(Day, GetDate(), duedate))
END
FROM Tab_CCSNETACTIONS where AssignedTo = @userid and CompleteDate IS NULL

ORDER BY days ASC


SELECT @ErrorCode = @@Error
END
SET NOCOUNT OFF
RETURN @ErrorCode

Thank you very much for the helpful information.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 16:38:12

Another option is if the number of records isn't alot in those table and you really want to avoid dynamic SQL...

Import the information in temp table and join that temp table in your select clause.

If Modulename='SU' then SELECT USID as ID, * INTO #Tmp FROM Tab_Submittals su WHERE su.deleted = '0'
If Modulename='RF' then SELECT RFID as ID, * INTO #Tmp FROM Tab_RFI RF WHERE RF.deleted = '0'
If Modulename='RM' then SELECT RMID as ID, * INTO #Tmp FROM Tab_RM RM WHERE RM.deleted = '0'

And later .. join that tmp table in you statement....

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources