Pass JSON as a parameter to the procedure

Hello Here i would like to explain about JSON (Java script object notation)

Here I divide 3 parts

a) Generate JSON text Data
b) Read JSON Data
c) Pass JSON as a parameter to the procedure
Insert JSON data into a table
Update JSON parameter data
Delete JSON based data
Return JSON data
Eg:
create table employee
(
eid int,
empname varchar(250),
empadd1 varchar(250),
empadd2 varchar(250),
empadd3 varchar(250),
empphone char(10),
emppin   char(6),
empdate  datetime
)
go
insert into employee values(1,'Sample Name','Address Line1','Address Line2','Address Line3','1234567891','500012',getdate())
insert into employee values(2,'Sample Name2','Address Line1','Address Line2','Address Line3','1234567881','500013',getdate())

go
select *from employee -- Default select

Eid	empname	empadd1	empadd2	empadd3	empphone	emppin	empdate
1	Sample Name	Address Line1	Address Line2	Address Line3	1234567891	500012	11-10-2018
2	Sample Name2	Address Line1	Address Line2	Address Line3	1234567881	500013	11-10-2018

a) Generate JSON Data

SELECT *FROM employee FOR JSON AUTO -- JSON 
[
{ --row start
"eid":1,
"empname":"Sample Name",
"empadd1":"Address Line1",
"empadd2":"Address Line2",
"empadd3":"Address Line3",
"empphone":"1234567891",
"emppin":"500012",
"empdate":"2018-10-11T18:23:43.520"
}, -- row end
{  -- row start
"eid":2,
"empname":"Sample Name2",
"empadd1":"Address Line1",
"empadd2":"Address Line2",
"empadd3":"Address Line3",
"empphone":"1234567881",
"emppin":"500013",
"empdate":"2018-10-11T18:24:46.477"
} -- row end
]
b ) Read JSON text Data
create proc usp_myprocforJson
(
  @peramjson NVARCHAR(MAX)
)
AS
BEGIN
   
    SELECT eid,empname,empadd1,empadd2,empadd3,empphone,emppin,empdate
    INTO #tblPersons
    FROM OPENJSON (@peramjson, '$.root')
    WITH (
    eid int,
empname varchar(250),
empadd1 varchar(250),
empadd2 varchar(250),
empadd3 varchar(250),
empphone char(10),
emppin char(6),
empdate datetime
    )
 SELECT *
        FROM #tblPersons tmp

    DROP TABLE #tblPersons
END
GO

Declare @myparam varchar(max) = '{"root": [
  {
    "eid": 1,
    "empname": "Sample Name",
    "empadd1": "Address Line1",
    "empadd2": "Address Line2",
    "empadd3": "Address Line3",
    "empphone": "1234567891",
    "emppin": "500012",
    "empdate": "2018-10-11T18:23:43.520"
  },
  {
    "eid": 2,
    "empname": "Sample Name2",
    "empadd1": "Address Line1",
    "empadd2": "Address Line2",
    "empadd3": "Address Line3",
    "empphone": "1234567881",
    "emppin": "500013",
    "empdate": "2018-10-11T18:24:46.477"
  }]}'
  Exec usp_myprocforJson @myparam
go

Eid empname empadd1 empadd2 empadd3 empphone emppin empdate 1 Sample Name Address Line1 Address Line2 Address Line3 1.235E+09 500012 11-10-2018 2 Sample Name2 Address Line1 Address Line2 Address Line3 1.235E+09 500013 11-10-2018

Working on next ...,

Oracle plsql interview questions

Oracle Plsql interview questions.

1. What's nocopy
2. What's Context Switching
3. What's Advanced Queuing
4. Explain Merge
5. Explain Materialized view
6. Print the only alpha numeric data contains in a column.
    Column contains numeric,alphanumeric,alpha characters data
7. Print a fourth Sunday of given month/sysdate
8. Get the data of given Date column by till date(sysdate) from beginning of date (calculate field)
9. Give an example of a anonymous code using bulk collection move data from one table to another table 
10.  While loading data from 1st row to 20000 rows if 500 th row gets error how to over come remaining data loading.
11. What's Return statement.
12. Given string contains 'A123A456A987A' count how many times 'A' occured.
13. How do you return multiple rows/records data from function.
14. How many files are there while executing control file data
15. Load data from one table to another table (without using loop)
16. What's pragma autonomous transaction.
17. I have a partitioned one table example
       Partition1 contains 0......1000
       Partition2 contains 1001 ..... 2000
       Partition3 contains 2001 ..... 3000
if i have updated a 600th row/record what happens my partition ?
18. What's Global temporary table.
19. What's Procedure overloading
20. One column containing numeric data. I need to print the data of even records count and odd records count

SQL Server Interview Questions

1. What's magic table
2. Find the Duplicate rows
3. Delete the Duplicate rows 2nd duplicate rows on wards
4. Difference between procedure and function
5. How to view your stored procedure code
6. Can i give alias name in order by clause
7. How can i give sort column while you are using union all sets data
8. What's the sequence of execution Select statement to order by clause
9. How many clustered indexes will have one table?
10. How many non clustered indexes can create for a table?
11. What you can see in the execution plan?
12. Which one faster Index seek/Index scan/ Table scan?
13. What's the purpose of Identity?
14. What's Merge ?
15. How do you find the indexes ?
16. How do you handle exceptions?
17. How do you encrypt the stored procedure instead of viewing entire code?
18. What's read_commited_snapshot ?
19. How do you read a file stored as a column by using T-SQL query?
20. What are the locks ?

Mobile Phone e-Wastage

Hello world,
I just came to know today by repairing my mobile and went to workshop says that its repair cost is equal to new phone cost.

So people lets think 1 sec what happens its just one minute think and great help to..

--> Our new generation.
--> Make Mobiles eco-friendly
--> Re-cycle the parts/material
--> Before buying let's think is the parts are available with least cost

Peoples need to think seriously on Electronic waste in our world now a days are gradually increasing more and more usage Electronic is mobile.
Here I came to know in India I didn't see E-Wastage dump.?
If Govt needs to take initiation on this by collecting /dumping / destroying the E-Waste
Now a days in every home having at least 3 to 4 waste /UN-used mobiles are having don't know what to do with that...
Hope Let us know if any E-waste collecting shows upcoming...

Thanks,

Sorry for the too delay to update my blog

Hello All viewers,
Thanks for your support !!!. Once again Sorry for too delay in response/ update my blog.
Will try to update regular as possible.
Thanks,

:-) Happy Valentines Day to All Lover's