June 15, 2004 at 3:51 pm
DECLARE @emp_group_name varchar(100)
SELECT @emp_group_name=b.emp_group_name
FROM mmmm_emp a INNER JOIN mmmm_emp_group b
ON a.emp_group_id=b.emp_group_id AND a.emp_id=18887
SELECT @emp_group_name
SELECT * FROM mmmm_emp
WHERE first_name = SUBSTRING(@emp_group_name,1,CHARINDEX(' ',@emp_group_name)-1)
AND last_name=SUBSTRING(@emp_group_name,CHARINDEX(' ',@emp_group_name)+1,50)
Thanks.
June 15, 2004 at 7:34 pm
Yes, put all the code in a stored procedure and just execute the SP and supply it with the variables.
EG.
create procedure sp_emp
as
DECLARE @emp_group_name varchar(100)
SELECT @emp_group_name=b.emp_group_name
FROM mmmm_emp a INNER JOIN mmmm_emp_group b
ON a.emp_group_id=b.emp_group_id AND a.emp_id=18887
SELECT @emp_group_name
SELECT * FROM mmmm_emp
WHERE first_name = SUBSTRING(@emp_group_name,1,CHARINDEX(' ',@emp_group_name)-1)
AND last_name=SUBSTRING(@emp_group_name,CHARINDEX(' ',@emp_group_name)+1,50)
go
-----
then to run it call it like this -> exec sp_emp
June 16, 2004 at 2:27 am
--Is it something like this You are trying to accomplish ?:
SELECT grp.emp_group_name, mmmm_emp.*
FROM mmmm_emp
JOIN(
SELECT emp_group_name
FROM mmmm_emp a INNER JOIN mmmm_emp_group b
ON a.emp_group_id=b.emp_group_id AND a.emp_id=18887 ) grp
ON mmmm_emp.first_name = SUBSTRING(grp.emp_group_name,1,CHARINDEX(' ',grp.emp_group_name)-1)
AND mmmm_emp.last_name=SUBSTRING(grp.emp_group_name,CHARINDEX(' ',grp.emp_group_name)+1,50)
--And instead of CHARINDEX.. can You do like this ?:
SELECT grp.emp_group_name, mmmm_emp.*
FROM mmmm_emp
JOIN(
SELECT emp_group_name
FROM mmmm_emp a INNER JOIN mmmm_emp_group b
ON a.emp_group_id=b.emp_group_id AND a.emp_id=18887 ) grp
ON mmmm_emp.first_name + ' ' + mmmm_emp.last_name = grp.emp_group_name
--/rockmoose
You must unlearn what You have learnt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply