Hello Every one I need to create a store proc to store email id's in to a table called test. I have these columns

invited_isid (name of the user who sends the request)
invited_email (email id to whom he has sent)
invited_name (name who invited)
invitation_status (invited for first time or second time)
created_by (name of the user who created the request)
invited_by(name of the invitor)

we have an application in which from the front end they will invite with email id(one or many using semicolon) to the users. So after inviting them, this should call a store procedure "test" and this "test" stored procedure should run in back end and will store these  values into the table "test" and the vales are invited_isid ,invited_email, invited_name,invitation_status, created_by ,invited_by. Let me know for any questions and if it is ain't clear.   

Thanks,

sidhu

Hi Sidhu,

Do you want to store only the email id or all the other informations too?

And do you want to keep multiple email id's in a row or one email id per row?

Thanks

hi thanks for the reply... I want to store all the values in a table. And I can give only one email id or multiple email id's

Hi Sidhu,

Check out does below script helps for you.

-- Function Which spilts a strings into table And the delimeter is Semicolon

CREATE FUNCTION [dbo].Split1(@input AS Varchar(4000) )

RETURNS

      @Result TABLE(Value Varchar(100))

AS

BEGIN

      DECLARE @str VARCHAR(20)

      DECLARE @ind Int

      IF(@input is not null)

      BEGIN

            SET @ind = CharIndex(';',@input)

            WHILE @ind > 0

            BEGIN

                  SET @str = SUBSTRING(@input,1,@ind-1)

                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)

                  INSERT INTO @Result values (@str)

                  SET @ind = CharIndex(';',@input)

            END

            SET @str = @input

            INSERT INTO @Result values (@str)

      END

      RETURN

END

--NOTE: You can change the data type of an parameter as per you table design

CREATE PROC Sp_InsertInviteInfoTest (@invited_isid INT,@invited_email VARCHAR(500),@invited_name VARCHAR(500), @invitation_status VARCHAR(50),@created_by VARCHAR(100),@invited_by VARCHAR(100) )

AS

BEGIN

SET NOCOUNT ON

DECLARE @EmailTable TABLE

(Id INT IDENTITY(1,1),

EmailId Varchar(100))

DECLARE @Count INT = 1

DECLARE @CountMax INT

DECLARE @Emailid VARCHAR(100)

INSERT INTO @EmailTable

SELECT * FROM dbo.split1 (@invited_email)

SELECT @CountMax = @@ROWCOUNT

WHILE(@Count<=@CountMax)

BEGIN

SELECT  @Emailid =EmailId FROM  @EmailTable WHERE ID = @Count

INSERT INTO TEST (invited_isid ,invited_email,invited_name,invitation_status, created_by ,invited_by)

SELECT @invited_isid ,@EmailId,@invited_name,@invitation_status,@created_by ,@invited_by

SET @Count = @Count+1

END

END

-- to call the proc you need send the param values Ex

DECLARE @invited_isid INT

DECLARE @invited_email VARCHAR(500)

DECLARE @invited_name VARCHAR(500)

DECLARE @invitation_status VARCHAR(50)

DECLARE @created_by VARCHAR(100)

DECLARE @invited_by VARCHAR(100)

SET @invited_isid = 1

SET @invited_email= 'Test@gmail.com;Test1@gmail.com;'

SET @invited_name = 'Test'

SET @invitation_status ='Test1'

SET @created_by = 'Test User'

SET @invited_by = 'test user1 '

EXEC Sp_InsertInviteInfoTest @invited_isid,@invited_email,@invited_name,@invitation_status,@created_by,@invited_by

Thanks

--------------------------------------------------------------------------------------

Please Mark This As Answer if it solved your issue.

Please Vote This As Helpful if it helps to solve your issue

• Edited by Revathi jayakrishnan 4 hours 10 minutes ago