Want to become a Vibe Coder? Join Vibe Coding Training here
x
C# Corner
Tech
News
Videos
Forums
Jobs
Books
Events
More
Interviews
Live
Learn
Training
Career
Members
Blogs
Challenges
Certification
Contribute
Article
Blog
Video
Ebook
Interview Question
Collapse
Feed
Dashboard
Wallet
Learn
Achievements
Network
Refer
Rewards
SharpGPT
Premium
Contribute
Article
Blog
Video
Ebook
Interview Question
Register
Login
Create JSON Table using Store Procedure in SQL
WhatsApp
Pintoo Yadav
Aug 25
2015
1.4
k
0
0
USE[golivecode]
GO
/****** Object: StoredProcedure [dbo].[Sp_GetVerificationDashBoardDetails] Script
Date
: 08/24/2015 16:47:09 ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
Create
PROCEDURE
[dbo].[Sp_golivecode.com]@ gridID
int
, @PresentationDate
varchar
(
max
)
AS
BEGIN
if@ gridID = 2
begin
select
distinct
CONVERT
(
varchar
, PresentationDate, 103)
as
[
Date
],
COUNT
( * )
AS
[CREATEDBATCH],
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
BatchStatusID
ELSE
null
END
)
AS
[COMPLETEAMOUNTFEED], (
COUNT
(
CASE
WHEN
BatchStatusID >= 2
THEN
BatchStatusID
ELSE
null
END
) -
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
batchNo
ELSE
null
END
))
AS
[PENDINGAMOUNTFEED]
from
OutwardBatch_East
WHERE
PresentationDate =
convert
(datetime, @PresentationDate, 103)
GROUP
BY
PresentationDate
end
else
if@ gridID = 3
begin
select
distinct
CONVERT
(
varchar
, PresentationDate, 103)
as
[
Date
],
COUNT
( * )
AS
[CREATEDBATCH],
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
BatchStatusID
ELSE
null
END
)
AS
[COMPLETEAMOUNTFEED], (
COUNT
(
CASE
WHEN
BatchStatusID >= 2
THEN
BatchStatusID
ELSE
null
END
) -
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
batchNo
ELSE
null
END
))
AS
[PENDINGAMOUNTFEED]
from
OutwardBatch_North
WHERE
PresentationDate =
convert
(datetime, @PresentationDate, 103)
GROUP
BY
PresentationDate
end
else
if@ gridID = 4
begin
select
distinct
CONVERT
(
varchar
, PresentationDate, 103)
as
[
Date
],
COUNT
( * )
AS
[CREATEDBATCH],
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
BatchStatusID
ELSE
null
END
)
AS
[COMPLETEAMOUNTFEED], (
COUNT
(
CASE
WHEN
BatchStatusID >= 2
THEN
BatchStatusID
ELSE
null
END
) -
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
batchNo
ELSE
null
END
))
AS
[PENDINGAMOUNTFEED]
from
OutwardBatch_South
WHERE
PresentationDate =
convert
(datetime, @PresentationDate, 103)
GROUP
BY
PresentationDate
end
else
if@ gridID = 5
begin
select
distinct
CONVERT
(
varchar
, PresentationDate, 103)
as
[
Date
],
COUNT
( * )
AS
[CREATEDBATCH],
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
BatchStatusID
ELSE
null
END
)
AS
[COMPLETEAMOUNTFEED], (
COUNT
(
CASE
WHEN
BatchStatusID >= 2
THEN
BatchStatusID
ELSE
null
END
) -
COUNT
(
CASE
WHEN
BatchStatusID >= 7
THEN
batchNo
ELSE
null
END
))
AS
[PENDINGAMOUNTFEED]
from
OutwardBatch_West
WHERE
PresentationDate =
convert
(datetime, @PresentationDate, 103)
GROUP
BY
PresentationDate
end
END
SQL
JSON
Store Procedure
Up Next
Create JSON Table using Store Procedure in SQL