Inserting into two tables

[JJ297]

how do I set up a stored procedure to insert into two tables. This is
what I have thus far...

I want the quesid from the QuesNAns table to go into the Requestors
table Getting an error message that I must declare the QuesID but
when I do it still does not work. Any suggestions?


CREATE procedure AddQuestion

@quesdate datetime,
@topicid int,
@questions varchar(1000)


AS
Set NOCOUNT ON

INSERT INTO QuesNAns
(quesdate,topicid, questions)

values
(@quesdate,
@topicid,
@questions)

SET NOCOUNT OFF

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]



INSERT INTO Requestors
(quesid)

values
(@quesid)

GO

 

[bruce barker]

you seem a bit confused.

set nocount, controls whether transaction result counts are sent to the
client. not sure why you turn it off and on. usually you turn it off to
not get extra result sets. i assume @quesid is supposed to be the
identity of the insert. instead of returning it to the caller, save it
in a variable.


CREATE procedure AddQuestion
@quesdate datetime,
@topicid int,
@questions varchar(1000)
AS
Set NOCOUNT ON
declare @quesid int

INSERT INTO QuesNAns(quesdate,topicid, questions)
values(@quesdate,
@topicid,
@questions)

set @quesid = SCOPE_IDENTITY()

INSERT INTO Requestors(quesid)
values(@quesid)

select @quesid as quesid -- return to caller if needed

GO


-- bruce (sqlwork.com)

 

[JJ297]

you seem a bit confused.

set nocount, controls whether transaction result counts are sent to the
client. not sure why you turn it off and on. usually you turn it off to
not get extra result sets. i assume @quesid is supposed to be the
identity of the insert. instead of returning it to the caller, save it
in a variable.

CREATE procedure AddQuestion
@quesdate datetime,
@topicid int,
@questions varchar(1000)
AS
Set NOCOUNT ON
declare @quesid int

INSERT INTO QuesNAns(quesdate,topicid, questions)
values(@quesdate,
@topicid,
@questions)

set @quesid = SCOPE_IDENTITY()

INSERT INTO Requestors(quesid)
values(@quesid)

select @quesid as quesid -- return to caller if needed

GO

-- bruce (sqlwork.com)

how do I set up a stored procedure to insert into two tables. This is
what I have thus far...

I want the quesid from the QuesNAns table to go into the Requestors
table Getting an error message that I must declare the QuesID but
when I do it still does not work. Any suggestions?

CREATE procedure AddQuestion

@quesdate datetime,
@topicid int,
@questions varchar(1000)

AS
Set NOCOUNT ON

INSERT INTO QuesNAns
(quesdate,topicid, questions)

SET NOCOUNT OFF

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

INSERT INTO Requestors
(quesid)

GO- Hide quoted text -

- Show quoted text -

Great thanks!