CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
user614946
user614946

Reputation: 619

Nested Query how to 

ALTER PROCEDURE dbo.updateadmin
(
     @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.

Upvotes: 0

Views: 59

Answers (3)

Pankaj
Pankaj

Reputation: 10095

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

Upvotes: 0

VMAtm
VMAtm

Reputation: 28355

 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

Upvotes: 0

gbn
gbn

Reputation: 432180

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

Upvotes: 1

Related Questions