how to insert a string after split the string in storedprocedure

Question

0.00/5 (No votes)

See more:

This is my stored procedure

SQL

create procedure sptest(@roleid int, @privileges varchar(50))
 as
 begin
 insert into users(Roleid, Roles) values(roleid=@roleid , privileges=@privileges)
 end

@privileges contains R,W,D

We need to store it in Database like below

SQL

Roleid    Prv
1         R
1         W
1         D

How can we do that...?
Can i use Loop or something else..

Posted 26-May-14 22:51pm

Venkat Kumar chigulla

Updated 26-May-14 23:01pm

v2

Add a Solution

Comments

CHill60 27-May-14 4:58am

Well for starters that procedure will never work as the syntax is incorrect. You are also only passing the privileges so is it safe to assume that Roleid is an autoincrement column?

Venkat Kumar chigulla 27-May-14 5:01am

Roleid is not autoincrement... I changed the code now. Sorry

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-05-26T22:59:00

Solution 1

Breaking it isn't that simple, but Using comma separated value parameter strings in SQL IN clauses[^] does what you want with a temporary table.

Posted 26-May-14 22:59pm

OriginalGriff

DaveAuld · Answer 2 · 2014-05-26T23:03:00

Solution 2

Yes you could split the list in SQL and then work on a loop inserting each item.

There is a similar thing being done here: Parse delimited string in a Stored procedure[^]

Posted 26-May-14 23:03pm

DaveAuld

Updated 26-May-14 23:08pm

v2

Mandip Grewal · Answer 3 · 2014-05-26T23:38:00

There is no built in function in SQL to split the comma separaed values. You have to create user defined function which split the comma separated data..

Please refer following link containing the details of User Defined function used to split the data..

Split function in SQL[^]

Once you created the user defined function to split data in SQL, you can insert values into your table as following

SQL

CREATE PROCEDURE procTest(@roleID INT, @privileges VARCHAR(50))
 AS
 BEGIN
  INSERT INTO tblRoles(RoleID, Roles)
  SELECT @roleID, value FROM dbo.[NAME-OF-USER-DEFINED-SPLIT-FUNCTION](@privileges,',')
 END