May 17, 2005 at 12:27 am
Guys, I know one of you guys has the answer to this. Its so simple. I've got an SP here that takes a parameter, that I am using for an insert statement. As you can see, I also have a small select statement commented out.
I want to insert the sShortDescription value that is passed in to the SP, but I also wanted to insert the result of the select statement. What is the trick? I can't seem to embed the SELECT statement within the VALUES argument.
Any help would be AWESOME.
-Tom
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[pr_tbl_CAP_Insert]
@sShortDescription varchar(50),
@iScoreID int
AS
/*
SELECT
tbl_UserAssignment.SubjectID AS 'SubjectID'
WHERE tbl_Score.ID = @iScoreID
*/
-- INSERT a new row in the table.
INSERT [dbo].[tbl_CAP]
(
[ShortDescription]
[SubjectID]
)VALUES(
@sShortDescription
)
-- Get the IDENTITY value for the row just inserted.
SELECT @iID=SCOPE_IDENTITY()
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
May 17, 2005 at 11:47 am
Is SubjectID an identity field? If so, don't put it in the insert statement.
May 17, 2005 at 1:28 pm
Are you looking for something like this?
ALTER PROCEDURE [dbo].[pr_tbl_CAP_Insert]
@sShortDescription varchar(50),
@iScoreID int
AS
INSERT [dbo].[tbl_CAP] ([ShortDescription], [SubjectID])
SELECT @sShortDescription, SubjectID
FROM tbl_UserAssignment
WHERE tbl_Score.ID = @iScoreID
-- Get the IDENTITY value for the row just inserted.
SELECT @iID=SCOPE_IDENTITY()
GO
Note that this code does not do anything with the @iID parameter since I just copied that part from your code. I assume you want to return that value or something.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply