2 Replies - 113 Views - Last Post: 29 September 2019 - 10:32 AM

#1 pawsdog18   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-September 19

How can I allow only 1 employee to be inserted into a specified job

Posted 29 September 2019 - 04:37 AM

The error in both of my examples from a stored procedure below. This happens even with no records of an employee emp_id with that jobid. I've tried so many variations from the code I have commented out and I'm not too sure how to approach this. My employee table emp_id is of type char so I've also had some conversion troubles as well




begin
     if  exists (SELECT COUNT(*)
FROM
(
SELECT emp_id, COUNT(*) AS CNT
FROM employee
GROUP BY emp_id
) AS T
WHERE CNT > 1 and @jobId = 2)
     throw 500001, 'Only 1 employee can be assigned to this role',1

	 --begin
  --   if  exists (select * from employee where @jobId = 2 group by emp_id having count(emp_id) = 1)
  --   throw 500001, 'Only 1 employee can be assigned to this role',1



Is This A Good Question/Topic? 0
  • +

Replies To: How can I allow only 1 employee to be inserted into a specified job

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: How can I allow only 1 employee to be inserted into a specified job

Posted 29 September 2019 - 09:25 AM

Provide the error details.

You probably need to provide more code and context details as well.
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,581
  • Joined: 12-June 08

Re: How can I allow only 1 employee to be inserted into a specified job

Posted 29 September 2019 - 10:32 AM

Seems odd to do a count in a count.

why not just a SELECT 1 and a 'exists' ?


cr eate tabl e #foo(a int)

ins ert into #foo(a)values(1)

select 'exists!'
where exists (
	select 1
	from #foo
	where a = 2
)

d rop tabl e #foo

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1