0

I have a table Zone that contains these columns:

ID
Name
Province_Id
City_Id

I want to pass a list of 3 columns and insert them into that table with a SQL Server stored procedure.

Like this

Name       'a,b,c,d'
Province_Id 1,2,3,4
City_Id     1,2,3,4

I write this procedure but this procedure works only for one column

CREATE PROCEDURE [dbo].[CreateZone]
    @Name                  NVARCHAR(50),
    @BuildingProvince_Id   INT,
    @BuildingCity_Id       INT
AS
BEGIN
    BEGIN TRY
        DECLARE @xml xml;   

        SET @xml = cast(('<X>'+replace(@Name,',' ,'</X><X>')+'</X>') as xml)

        INSERT INTO ZoneCategory
            SELECT  N.value('.', 'varchar(100)') as value 
            FROM @xml.nodes('X') as T(N)

        SELECT 100
    END TRY
BEGIN CATCH
    EXEC InsertSQLError
        SELECT -100 as Error
END CATCH
END

How can I change it to insert values for 3 columns?

Or create a procedure for this scenario?

I want insert values for 3 columns with comma

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kalim
  • 23
  • 5
  • Possible duplicate of [Split function equivalent in T-SQL?](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) – InbetweenWeekends Nov 05 '15 at 13:38
  • Your input parameter Province_Id and City_ID both INT, how do you assign multiple value to them? – EricZ Nov 05 '15 at 14:23

1 Answers1

-1

What you're attempting to do is pass an array (well, multiple arrays) as input parameters to a stored procedure.

With that understanding, rather than reword / plagiarize what is already an excellent answer, I'll just point you at this...

How to pass an array into a SQL Server stored procedure

Community
  • 1
  • 1
LDMJoe
  • 1,591
  • 13
  • 17