Can i combine the script into a single query...

  • 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.

  • 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

  • --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