Link to home
Create AccountLog in
Avatar of andrew67
andrew67

asked on

sql stored procedure sub querys

hi all i have the following ms sql server stored procedure but its throwing errors can any one tell me where im going wrong

PROCEDURE [dbo].[addNewSubjectategory]
(
      @subjectId int,
      @title varchar(200),
      @maxId = int,
      @sectionSubject varchar(200)
) AS
INSERT INTO subjects (title,sectionSubjectId) VALUES (@title,@subjectId)
SET @maxId = SELECT MAX(id) AS id FROM subjects
SET @sectionSubject = SELECT sectionSubjects FROM sectionSubjects WHERE id = @subjectId
INSERT INTO menuSubjects (id,description) VALUES (@maxId,@sectionSubject)
SELECT MAX(id) AS id FROM subjects

thanks
Avatar of HuyBD
HuyBD
Flag of Viet Nam image

change like this
PROCEDURE [dbo].[addNewSubjectategory]
(
      @subjectId int,
      @title varchar(200),
      @maxId = int,
      @sectionSubject varchar(200)
) AS
INSERT INTO subjects (title,sectionSubjectId) VALUES (@title,@subjectId)
SELECT @maxId = MAX(id) AS id FROM subjects
SELECT @sectionSubject =sectionSubjects FROM sectionSubjects WHERE id = @subjectId
INSERT INTO menuSubjects (id,description) VALUES (@maxId,@sectionSubject)
SELECT MAX(id) AS id FROM subjects

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HuyBD
HuyBD
Flag of Viet Nam image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of andrew67
andrew67

ASKER

Hi using this

 AS
INSERT INTO subjects (title,sectionSubjectId) VALUES (@title,@subjectId)
SELECT @maxId = @@identity
SELECT @sectionSubject = sectionSubjects FROM sectionSubjects WHERE id = @subjectId
INSERT INTO menuSubjects (id,description) VALUES (@maxId,@sectionSubject)
SELECT MAX(id) AS id FROM subjects

I get the following errors

Msg 170, Level 15, State 1, Procedure addNewSubjectategory, Line 5
Line 5: Incorrect syntax near '='.
Msg 137, Level 15, State 1, Procedure addNewSubjectategory, Line 10
Must declare the variable '@maxId'.
Msg 137, Level 15, State 1, Procedure addNewSubjectategory, Line 10
Must declare the variable '@sectionSubject'.
Msg 137, Level 15, State 2, Procedure addNewSubjectategory, Line 11
Must declare the variable '@maxId'.
its ok sorry ive sorted it the error is refering to the = ive put here

@maxId = int,