Msg 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 @StudentGrades
4.67
3
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GO
Thank you!
3
4.67
(3 Votes)
0
3.88
8
-- 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
)
GO
Thank you!
8
3.88
(8 Votes)
0
3.67
6
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GO
Thank you!
6
3.67
(6 Votes)
0
3.5
4
-- 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]
GO
Thank you!
4
3.5
(4 Votes)
0
Are there any code examples left?
New code examples in category SQL
