March 13, 2009 at 7:21 am
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 ')'.
March 13, 2009 at 7:35 am
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.
March 13, 2009 at 9:50 am
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.
March 13, 2009 at 10:51 am
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.
March 13, 2009 at 11:08 am
Thank you Luke.
It worked just the way I want it.
Thank U, Thank U.
Have a wonderful weekend.
March 13, 2009 at 11:13 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply