Error on a stored proc

unathi guma

7y898Learn iOS Programming

I am creating a stored proc to insert JSON data on the DB and I am getting an error. See my code below.

CREATE PROCEDURE InsertJson
@json NVARCHAR(MAX)
AS
BEGIN
INSERT INTO dbo.tbl_SubmissionsMaster --dbo.tbl_SubmissionsMaster([pk_DataSetId, CompleteDate, Period, OrgUnit, DataValues, SubmittedTS, ResubmittedTS])
-- Insert statements for procedure here
SELECT *
FROM OPENJSON (@json)
WITH(
DataSetId uniqueidentifier
, CompleteDate DateTime
, period datetime
, OrgUnit varchar(50)
, DataValues varchar(200)
, SubmittedTS DateTime
, ResubmittedTS DateTime
)
END
GO

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajeesh Menoth7y ago
Accepted answer
Hi, If this is working mean you are doing the altering the procedure and inside with proper insert command and after that only you have using select statement.
+2
unathi guma7y ago
This is a working example

ALTER PROCEDURE [dbo].[InsertPerfCounterData]

@json NVARCHAR(max)

AS
BEGIN

INSERT INTO dbo.PerfCounter (
[RecordedDateTime]
, [RecordedDateTimeLocal]
, [CpuPctProcessorTime]
, [MemAvailGbytes])

SELECT
RecordedDateTime
,RecordedDateTimeLocal
,CpuPctProcessorTime
,MemAvailGbytes
FROM OPENJSON(@json)
WITH (
RecordedDateTime DATETIME2(0) '$.dateTime'
, RecordedDateTimeLocal DATETIME2(0) '$.dateTimeLocal'
, CpuPctProcessorTime SMALLINT '$.cpuPctProcessorTime'
, MemAvailGbytes SMALLINT '$.memAvailGbytes'
) AS jsonValues

END
+1
Rajeesh Menoth7y ago
Hi,Your code is totally incorrect. Don't compain insert with select statement.That is the reason you are getting the syntax error. Go through this article for basics of sp : https://www.tutorialspoint.com/t_sql/t_sql_stored_procedures.htm
+1
unathi guma7y ago
--dbo.tbl_SubmissionsMaster([pk_DataSetId, CompleteDate, Period, OrgUnit, DataValues, SubmittedTS, ResubmittedTS])
The yellow part is commented out
0
unathi guma7y ago
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
0
Rajeesh Menoth7y ago
Hi,

What error you have getting and please explain more ? I am not sure why you have put "--" in the code.

CREATE PROCEDURE InsertJson
@json NVARCHAR(MAX)
AS
BEGIN
INSERT INTO dbo.tbl_SubmissionsMaster --dbo.tbl_SubmissionsMaster([pk_DataSetId, CompleteDate, Period, OrgUnit, DataValues, SubmittedTS, ResubmittedTS])

+1