0

I would like to perform an insert statement which the value come from the parameter. The value will consist of '|' delimiter. I would like to insert the new row with the id come from SCOPE_IDENTITY as well. I have tried using the SPLIT_STRING but not sure on how to use with the external parameter and with the additional value.

Parameter Value from external source

@StudentName = John 
@score =Science-90|Biology-100|Math-90

Insert table:

Main table

ID StudentName
1 John

Score table

ID StudentID Score
1 1 Science-90
2 1 Biology-100
3 1 Math-90

This is my stored procedure:

CREATE PROCEDURE [dhub_PushData]    
    @studentName varchar(50),   
    @score varchar(50)
AS 
BEGIN 
    DECLARE @id bigint
 
    INSERT INTO Student_Main (studentName) 
    VALUES (@studentName) 

    SELECT @id = SCOPE_IDENTITY()
 
    -- Insert the score in every row which split by '|' with the @id from Student_Main table
Aiman Migo
  • 25
  • 1
  • 6
  • Would it not be simpler to split the string in whatever language your using to call the USP and either call it 3 times or pass in a table valued param? It also looks like Science-90 should be split in to subject & score. – Alex K. Jul 15 '22 at 10:54
  • @AlexK., yes Alex you are right. But because of now the input file that I need to deal with is in that format and it will be push from other system. – Aiman Migo Jul 15 '22 at 10:59
  • FYI, your column `Score` should be *2 columns*; you have a denormalised design. Instead, there should be a `Subject` column (with values like `'Science'` and `'Math'`) and a `Score` column with the *actual* score (`90, `100` etc). As for provided the data, a Table type parameter would be better choice here. There are, however, 1,000's of duplicates on splitting a delimited list in SQL Server; what about those questions didn't you udnerstand? – Thom A Jul 15 '22 at 10:59
  • Does this answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – Thom A Jul 15 '22 at 11:00
  • 1
    On a separate note, your SQL has syntax errors. Your parameter `@studentName` doesn't have a data type and doesn't have a comma (`,`) separator before the next parameter, and you attempt to assign `@id` twice within the same statement: `select @id = @id=SCOPE_IDENTITY()` – Thom A Jul 15 '22 at 11:02
  • 1
    @Larnu, I have a challenge to add the ID value with the Score parameter when using the SPLIT_STRING. Because based on the other example, mostly it come from the table. However, in my case, the value is from the parameter. StudentName = John score =Science-90|Biology-100|Math-90 – Aiman Migo Jul 15 '22 at 11:05
  • Why is `SELECT`ing the value of `@ID` a challenge? – Thom A Jul 15 '22 at 11:06
  • Because the @Id come from the parameter value. Most of the example, it will come from the table. select OtherID, cs.Value --SplitData from yourtable cross apply STRING_SPLIT (Data, ',') cs How I can add the id in this case? – Aiman Migo Jul 15 '22 at 11:12
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246462/discussion-between-aiman-migo-and-larnu). – Aiman Migo Jul 15 '22 at 11:12
  • `@id` isn't coming from a parameter, it's a variable defined in your procedure; your parameters are the undatatyped `@studentName` and `@Score`. – Thom A Jul 15 '22 at 11:13
  • And the `INSERT` syntax is off, too - it needs to be `INSERT INTO tableName (list-of-columns) VALUE**S** (list-of-values)` - using `VALUES` in the plural form - not `VALUE` .... – marc_s Jul 15 '22 at 11:20
  • ok, my apologies for the term. However, do you the suggestion on the SQL script part to select the @id and the SPLIT_STRING(@score, '|') and the insertion? – Aiman Migo Jul 15 '22 at 11:21
  • 2
    Can you not just do `INSERT Score (StudentID, Score) SELECT @id, value FROM STRING_SPLIT(@score, '|')` Also shouldn't `Score` be two separate columns `Subject` and `Score`? – Charlieface Jul 15 '22 at 11:34
  • Yeah, I mentioned that earlier to the OP, @Charlieface , and they've not addressed that comment. – Thom A Jul 15 '22 at 11:35
  • Thank you all, I can use the sql statement as suggested by @Charlieface. Now need to work on the splitting the score with the subject. – Aiman Migo Jul 15 '22 at 11:55
  • I'd advise you not do this anyway. Instead use a Table Valued Parameter, with two columns `Subject` and `Score`. Alternatively use JSON with `OPENJSON` or XML with `.nodes` `.value` – Charlieface Jul 15 '22 at 11:58

0 Answers0