Using a select statement in an INSERT statement

  • CREATE Procedure InsertProc

    Variable declarations here

    then

    AS

    INSERT INTO tblProject(ProjectName,Description,RequestedBy,ApprovedBy,DateApproved,AssignedTo,DueDate,Scope,Priority,Status,Department,ClientArea,usrName,User_Key)

    VALUES

    (

    @ProjectName, @Description ,@RequestedBy ,@ApprovedBy ,@DateApproved,@AssignedTo ,@DueDate ,@Scope , @Priority, @status ,@Department,@ClientArea , @usrName,select user_key from tblUsers where userID = usrName

    )

    Msg 156, Level 15, State 1, Procedure InsertNewProject, Line 35

    Incorrect syntax near the keyword 'select'.

    Msg 102, Level 15, State 1, Procedure InsertNewProject, Line 37

    Incorrect syntax near ')'.

  • If you need to use that way I'd suggest you do it one of two ways...

    INSERT INTO tblProject

    SELECT

    @ProjectName, @Description , @RequestedBy , @ApprovedBy ,@DateApproved,@AssignedTo ,@DueDate , @Scope , @Priority, @status ,@Department,@ClientArea , @usrName,

    user_key

    from tblUsers

    where userID = usrName

    Or you could assign User_Key to another variable and use that in your values clause...

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Hi,

    Becuase I'm not passing the value of user_key to the stored procedure, I'm getting this error message:

    Insert Error: Column name or number of supplied values does not match table definition.

  • Franco_1 (3/13/2009)


    Hi,

    Becuase I'm not passing the value of user_key to the stored procedure, I'm getting this error message:

    Insert Error: Column name or number of supplied values does not match table definition.

    Yes, for the alternate method I'm not saying put it in a variable passed in as a parameter, I was suggesting that you could query for that value, assigning it to a newly declared variable inside your Procedure...

    OR

    The query I gave you should work based on the information supplied. If there are other columns in that table not included in the set you are updating, you need to use a the column list as you did in your original post.

    INSERT INTO

    tblProject(ProjectName,Description,RequestedBy,ApprovedBy,DateApproved,AssignedTo,DueDate,Scope,Priority,Status,Department,ClientArea,usrName,User_Key)

    SELECT

    @ProjectName,

    @Description,

    @RequestedBy,

    @ApprovedBy,

    @DateApproved,

    @AssignedTo,

    @DueDate,

    @Scope,

    @Priority,

    @status,

    @Department,

    @ClientArea,

    @usrName,

    user_key

    from tblUsers

    where tblUsers.userID = @usrName

    Have a go with that and let me know how you make out.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Thank you Luke.

    It worked just the way I want it.

    Thank U, Thank U.

    Have a wonderful weekend.

  • Glad it worked, have a good one.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply