New qry problem - SQL Server Forums

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

SQL Server Development (2000)

New qry problem

Author

Topic

Rita Bhatnagar
Posting Yak Master

172 Posts

Posted - 2001-09-10 : 13:07:34

I have this query in access.
I need to change this to stored procedure for sql
server.
SELECT SM_MsgTriggerID, TriggerName,
SM_MsgTriggerCodeID, TriggerTotal, CompanyFilter,
eCompanyIDs, CRFilter, eCareRecpIDs, Active
FROM SM_MsgTrigger
WHERE
(((SM_MsgTriggerCodeID)=[prmSM_MsgTriggerCodeID]) AND
((Active)=True) AND
(IIf([SM_MsgTrigger].[CompanyFilter] = 2, InStr(1,
[SM_MsgTrigger].[eCompanyIDs], "," & [prmeCompanyID] &
","), True)) AND
(IIf([SM_MsgTrigger].[CRFilter] = 2, InStr(1,
[SM_MsgTrigger].[eCareRecpIDs], "," & [prmeCRID] &
","), True)));

I am confused about the iif part.
I am trying to do like this but this is not right

Declare
@prmSM_MsgTriggerCodeID int,
@prmeCompanyID varchar,
@prmeCRID varchar
SELECT SM_MsgTriggerID, TriggerName,
SM_MsgTriggerCodeID, TriggerTotal, CompanyFilter,
eCompanyIDs, CRFilter,
eCareRecpIDs, Active
FROM SM_MsgTrigger
WHERE SM_MsgTriggerCodeID=@prmSM_MsgTriggerCodeID AND
Active=1
AND Case when CompanyFilter = 2 then
charindex(@prmeCompanyID,eCompanyIDs)>0 end
AND Case When CRFilter = 2 then
charindex(@prmeCRID,eCareRecpIDs)>0 End

What i am doing wrong?
Any help is appreciated.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources