Nested Query how to

ALTER PROCEDURE dbo.updat开发者_运维问答eadmin
(
     @ReportRead char(10),
     @Date date,@EmpName varchar(50),
     @Subject nchar(50)
)
AS
    insert into Emp_ReportUpdate (ReportRead,[Date],EmpName,Subject)
    values(@ReportRead,@Date,@EmpName,@Subject)
    RETURN

This is my procedure and in @EmpName I need to use as a select statement so that to get data from another table and get it in @EmpN. Also if any one has a shorter method do guide me.

---

INSERT..SELECT.

You can work out the rest yourself of course because we don't have that information...

ALTER PROCEDURE dbo.updateadmin

    (
    @ReportRead char(10),@Date date,@EmpName varchar(50),@Subject nchar(50)
    )

AS
    insert into Emp_ReportUpdate (ReportRead,[Date],EmpName,Subject) 
    select @ReportRead, @Date, someOtherCol, @Subject
    from myothertable
    where somecol = @EmpName
RETURN

---

 ALTER PROCEDURE dbo.updateadmin
 (
     @ReportRead char(10), @Date date, @EmpName varchar(50), @Subject nchar(50)
 )  AS
 SELECT @EmpName = somecol FROM OTHERTABLE

 insert into Emp_ReportUpdate
 (ReportRead, [Date], EmpName, Subject)
 VALUES 
 (@ReportRead, @Date, @EmpName, @Subject)

 RETURN 

---

You can also use CTE. This can be helpful in future in case your stored procedure may turn more complex and this query may be used more then one time. So to get rid of duplications, We can use CTE.

ALTER PROCEDURE dbo.updateadmin

    (
        @ReportRead char(10),
        @Date date,
        @EmpName varchar(50),
        @Subject nchar(50)
    )

AS
    With CTE
    AS
    (
        Select YourColumnName from YourTableName Where YourColumnName = @EmpName
    )

    insert into Emp_ReportUpdate (ReportRead,[Date],EmpName,Subject) 
    Select @ReportRead, @Date, YourColumnName, @Subject
    From CTE

    RETURN