How to Insert array data through storeproceduare

Question

Post reply

How to Insert array data through storeproceduare

manojsemwal1 40990

SSC Enthusiast

Points: 190
More actions
January 30, 2024 at 9:41 am

#4370038

Hi ,
I am trying to getting and insert data through array using store procedure. how can I do it. I had tried hard but getting error :
EXEC GetSOR_Abstract @SOR_subactivityid =401,@SOR_activityid =301,@SOR_Block_id=0,@SOR_year_id=1,
@tempdata = [
{"item_id":"101","subitem_id":"290","no":"1","lngt":"1","wid":"2","hd":"3","qty":"6.00"},
{"item_id":"103","subitem_id":"201","no":"2","lngt":"3","wid":"2","hd":"2","qty":"24.00"},
{"item_id":"104","subitem_id":"202","no":"3","lngt":"4","wid":"3","hd":"3","qty":"108.00"},
{"item_id":"107","subitem_id":"203","no":"2","lngt":"3","wid":"2","hd":"2","qty":"24.00"}
]
"message": "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The identifier that starts with '{\"item_id\":\"101\",\"subitem_id\":\"290\",\"no\":\"1\",\"lngt\":\"3\",\"wid\":\"2\",\"hd\":\"2\",\"qty\":\"12.00\"},{\"item_id\":\"103\",\"subitem_id\":\"201\",\"n' is too long. Maximum length is 128.",
Thanks
January 31, 2024 at 9:48 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

The stored proc GetSOR_Abstract is specific to your environment, so we can't help in detail.

But it looks like @tempdata is defined with a maximum length of 128 within the definition of the stored proc.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

If you want more detailed answers, post the full set of code, and probably some of the structure as well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

are you trying to use table valued parameters ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

You need single quotes around the data that you're passing for @tempdata. I've added them in Red... one before the starting bracket and one after the ending bracket. Otherwise, your data is being treated basically as a column name.

EXEC GetSOR_Abstract @SOR_subactivityid =401,@SOR_activityid =301,@SOR_Block_id=0,@SOR_year_id=1,

@tempdata = '[

{"item_id":"101","subitem_id":"290","no":"1","lngt":"1","wid":"2","hd":"3","qty":"6.00"},

{"item_id":"103","subitem_id":"201","no":"2","lngt":"3","wid":"2","hd":"2","qty":"24.00"},

{"item_id":"104","subitem_id":"202","no":"3","lngt":"4","wid":"3","hd":"3","qty":"108.00"},

{"item_id":"107","subitem_id":"203","no":"2","lngt":"3","wid":"2","hd":"2","qty":"24.00"}

]'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

manojsemwal1 40990 SSC Enthusiast Points: 190 More actions · Answer 5

manojsemwal1 40990

SSC Enthusiast

Points: 190

January 31, 2024 at 6:24 am

#4370557

Thanks Jeff Moden,

Its working fine.

Manoj