1

I need to create a ticket with its details. From visual studio I send a string list or string array to sql with the data to create the ticket. The amount of details is variable, my question is how to execute that x amount of details in sql server.

I am using visual studio 2017 and sql server 2008.

GO
create procedure newTicketWithDetails
@idProduct int,
as
BEGIN TRANSACTION 
BEGIN TRY

/*
idTicket is identity
idTicket and dateTicket are PK
*/

insert into Tickets(idTicket,dateTicket))
values (GETDATE());

insert into TicketsDetails(idTicket,dateTicket,idProduct) 
values(IDENT_CURRENT('Tickets'),GETDATE(),@idProduct); 


/*
...
...        x inserts here
...
*/

COMMIT TRANSACTION 

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION
SELECT @@ROWCOUNT

END CATCH

Something like that is what I am trying to do. How should I go through that array with ticket data or how should I send the details from visual studio?

Franco Torres
  • 253
  • 1
  • 6
  • 1
    Since you said that the "amount of details" is variable, you should probably create parameters and fill them out and send it to your stored proc. – Rakesh Oct 21 '19 at 15:54
  • 1
    "From visual studio I send a string list or string array to sql with the data to create the ticket." - Is this happening? Looking at your stored procedure, the only data being passed in is idProduct which is an integer. – rainydevbs Oct 21 '19 at 15:58
  • 1
    This code is by way of example. My question is whether SQL server can receive a string array with all the query inside and read it, or if I have to do that tour in visual studio and from there make an insert for every detail. If it was only 1 or 2 details I can do it, my question is how to do it when the quantity is variable. – Franco Torres Oct 21 '19 at 16:15
  • 1
    What is Visual Studio got to do with this question? The question is a SQL Server question. – Eric Oct 21 '19 at 16:20
  • 1
    There's no such thing as array in SQL Server. – Eric Oct 21 '19 at 16:22
  • My idea is to send an array with the data for the insert. Another idea is to use a for each to read that array and in each iteration insert a row, using command.CommandText , but I don't know which is the best idea or the most correct one. I want to try to leave the transaction closer to the server than to the desktop application. – Franco Torres Oct 21 '19 at 17:32

1 Answers1

1

Send your parameters in an XML string as a single parameter. Then use XML parsing on the server side to get values out from the XML string and insert NON NULL ticket details in your table(s).

Refer: XML as parameter in stored procedure (sql server)

This will save you a lot of code on the client side as you ever have to send a single parameter.

banavalikar
  • 272
  • 1
  • 7