Need help with a query

Hi Experts,

I have a simple query in a stored procedure. Which works fine.
select CUSTEVENT.ID,CUSTEVENT.EventID,CUSTEVENT.NewOrFollowup,CUSTEVENT.CustID,
CUSTEVENT.JobNo,CUSTEVENT.CustContactID,
CUSTEVENT.CustEvID,CUSTEVENT.CustEvComment, CUSTEVENT.CustEvDt,
CUSTEVENT.CustFlUpDt,CUSTEVENT.CustFlUpEvntID,
CUSTEVENT.CustEvntOpenOrClose,EventCreatedBy,
CUSTEVENT.EventAssignedTo,CUSTEVENT.PrivateOrPublic
from CUSTEVENT
where  CUSTEVENT.CustEvntOpenOrClose='O' and
(EventCreatedBy= @EmpID or EventAssignedTo= @EmpID )


But when I write it like this then it does not work.

SET @SQL='select CUSTEVENT.ID,CUSTEVENT.EventID,CUSTEVENT.NewOrFollowup,CUSTEVENT.CustID,
                    CUSTEVENT.JobNo,CUSTEVENT.CustContactID,
               CUSTEVENT.CustEvID,CUSTEVENT.CustEvComment, CUSTEVENT.CustEvDt,
                     CUSTEVENT.CustFlUpDt,CUSTEVENT.CustFlUpEvntID,
               CUSTEVENT.CustEvntOpenOrClose,EventCreatedBy,
                     CUSTEVENT.EventAssignedTo,CUSTEVENT.PrivateOrPublic
               from CUSTEVENT
               where  CUSTEVENT.CustEvntOpenOrClose=''O'' and (EventCreatedBy=' + @EmpID   + ' or EventAssignedTo=' + @EmpID  +  ')';

     EXEC(@SQL);

I am getting the following error.

Conversion failed when converting the varchar value 'select CUSTEVENT.ID,CUSTEVENT.EventID,CUSTEVENT.NewOrFollowup,CUSTEVENT.CustID,
                    CUSTEVENT.JobNo,CUSTEVENT.CustContactID,
               CUSTEVENT.CustEvID,CUSTEVENT.CustEvComment, CUSTEVENT.CustEvDt,
                     CUSTEVENT.CustFlUpDt,CUSTEVENT.CustFlUpEvntID,
               CUSTEVENT.CustEvntOpenOrClose,EventCreatedBy,
                     CUSTEVENT.EventAssignedTo,CUSTEVENT.PrivateOrPublic
               from CUSTEVENT
               where  CUSTEVENT.CustEvntOpenOrClose='O' and (EventCreatedBy=' to data type int.

I also tried to do like this.
(EventCreatedBy=' + CAST(@EmpID AS NVARCHAR) +' or EventAssignedTo=' + CAST(@EmpID AS NVARCHAR);

But for some reason it is not taking the parameter.

Where an I going wrong?

Thanks in advance.
RadhaKrishnaKiJayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
See what the @tsql is before you execute it (via the select).  

try the following:
declare @empid int
set @empid = 5

declare @tsql varchar(max) = 
'select ID,EventID,NewOrFollowup,CustID,
JobNo,CustContactID, 
CustEvID,CustEvComment, CustEvDt,
CustFlUpDt,CustFlUpEvntID,
CustEvntOpenOrClose,EventCreatedBy,
EventAssignedTo,PrivateOrPublic
from CUSTEVENT 
where  CustEvntOpenOrClose=''O'' and 
(EventCreatedBy=' + cast(@empid as varchar(50)) + ' or  EventAssignedTo=' + cast(@empID as varchar(50)) + ')'

select @tsql
exec (@tsql)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jose TorresCertified Database AdministratorCommented:
Change the NVARCHAR to NVARCHAR(##) in the cast statement

SET @SQL='select CUSTEVENT.ID,CUSTEVENT.EventID,CUSTEVENT.NewOrFollowup,CUSTEVENT.CustID,
                    CUSTEVENT.JobNo,CUSTEVENT.CustContactID,
               CUSTEVENT.CustEvID,CUSTEVENT.CustEvComment, CUSTEVENT.CustEvDt,
                     CUSTEVENT.CustFlUpDt,CUSTEVENT.CustFlUpEvntID,
               CUSTEVENT.CustEvntOpenOrClose,EventCreatedBy,
                     CUSTEVENT.EventAssignedTo,CUSTEVENT.PrivateOrPublic
               from CUSTEVENT
               where  CUSTEVENT.CustEvntOpenOrClose=''O'' and (EventCreatedBy=' + cast(@EmpID as nvarchar(10))   + ' or EventAssignedTo=' + cast(@EmpID as nvarchar(10))  +  '
RadhaKrishnaKiJayaAuthor Commented:
Thank you for your reply. But still I am not getting the output. I am trying to execute the SP like this.

Exec spCustEventsSummary 4

But getting this error.

Msg 4145, Level 15, State 1, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'Ev'.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jose TorresCertified Database AdministratorCommented:
Dumb question here.  Is there a real need to modify the existing stored procedure to use dynamic sql.
RadhaKrishnaKiJayaAuthor Commented:
Yes. Because I have to pass 4/5 parameters dynamically and I think I need to use dynamic SQL.  Here I have posted only part of my query so that it will be easy for the experts.

Thanks.
Kyle AbrahamsSenior .Net DeveloperCommented:
ev isn't anything that you've shown . . . so it'll be helpful at this point if you posted the whole query.
RadhaKrishnaKiJayaAuthor Commented:
Actually it is working now. I had to change the parameters and the change u suggested. Thank you very much for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.