sql server - Stored Procedure for saving into two table -
i want store value of @additional variable getting front end , in format '98789797,879879879,987987978' . want store in different columns of table 'phoneno.s' removing comma between 2 no.s same username.
alter procedure [dbo].[addnewemployee] ( @name nvarchar(30), @surname nvarchar(20), @email nvarchar(30), @mobile nvarchar(60), @address nvarchar(65), @file nvarchar(40), @country nvarchar(20), @state nvarchar(20), @city nvarchar(20), @gender nvarchar(10), @additional nvarchar(100) ) begin insert users ( name, surname, email, mobile, address,filepath,country,state,city,gender) values (@name, @surname, @email, @mobile,@address,@file,@country,@state,@city,@gender) insert [phoneno.s] ( userid,phoneno) values ( @name, @additional ) end how can achieve this?? not pro in sql..it looking complex me can not proceed further!!
use split function. working in sql server. can change mysql
create function [dbo].[split] ( @rowdata nvarchar(max), @spliton nvarchar(5) ) returns @returnvalue table (data nvarchar(max)) begin declare @counter int set @counter = 1 while (charindex(@spliton,@rowdata)>0) begin insert @returnvalue (data) select data = ltrim(rtrim(substring(@rowdata,1,charindex(@spliton,@rowdata)-1))) set @rowdata = substring(@rowdata,charindex(@spliton,@rowdata)+1,len(@rowdata)) set @counter = @counter + 1 end insert @returnvalue (data) select data = ltrim(rtrim(@rowdata)) return end inside stored procedure
your first insert coding correct. replace second insert coding below coding.
declare @phonenumber varchar(max) set @phonenumber='98798797,897987,9879' insert addnewemployee(name,phonenumber) select @name, data dbo.split(@phonenumber,','). the above code split numbers , insert each row like
name phone number aj 98798797 aj 897987 aj 9879 hope you.
Comments
Post a Comment