Msg 8164, Level 16, State 1, Procedure getSalaryMonth, Line 31 [Batch Start Line 13] An INSERT EXEC statement cannot be nested.
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GOMsg 8164, Level 16, State 1, Procedure getSalaryMonth, Line 31 [Batch Start Line 13] An INSERT EXEC statement cannot be nested.
-- Option #3 : Use OPENROWSET
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
SELECT A.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Database=SQL2008;Uid=sshelper;Pwd=sshelper',
'EXECUTE [dbo].[usp_GetStudentGrades] 1 ') AS a
SELECT * FROM @StudentGradesMsg 8164, Level 16, State 1, Procedure getSalaryMonth, Line 31 [Batch Start Line 13] An INSERT EXEC statement cannot be nested.
-- Option #2 : Convert Stored Procedure to a Table-Valued Function
CREATE FUNCTION [dbo].[ufn_SearchStudents] (
@SubjectID INT
)
RETURNS TABLE
AS
RETURN (
SELECT B.[StudentID], B.[StudentName]
FROM [dbo].[StudentSubject] A INNER JOIN [dbo].[Student] B
ON A.[StudentID] = B.[StudentID]
WHERE A.[SubjectID] = @SubjectID
)
GOMsg 8164, Level 16, State 1, Procedure getSalaryMonth, Line 31 [Batch Start Line 13] An INSERT EXEC statement cannot be nested.
-- Option #1 : Integrate First Stored Procedure with the Second Stored Procedure
CREATE PROCEDURE [dbo].[usp_GetStudentGrades]
@SubjectID INT
AS
DECLARE @Students TABLE (
[StudentID] INT,
[StudentName] VARCHAR(50)
)
INSERT INTO @Students ( [StudentID], [StudentName] )
SELECT B.[StudentID], B.[StudentName]
FROM [dbo].[StudentSubject] A INNER JOIN [dbo].[Student] B
ON A.[StudentID] = B.[StudentID]
WHERE A.[SubjectID] = @SubjectID
SELECT A.[StudentID], A.[StudentName], C.[Grade]
FROM @Students A INNER JOIN [dbo].[StudentSubject] B
ON A.[StudentID] = B.[StudentID]
INNER JOIN [dbo].[StudentGrade] C
ON B.[StudentSubjectID] = C.[StudentSubjectID]
GOMsg 8164, Level 16, State 1, Procedure getSalaryMonth, Line 31 [Batch Start Line 13] An INSERT EXEC statement cannot be nested.
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GOCopyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
