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

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 wrote:

> 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
> 

On Aug 17, 11:11 am, bruce barker  wrote:

> 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 wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -


Great thanks!