how to insert commma seperated vales as a input?

Question

how to insert commma seperated vales as a input?

neethu payal

SSC Eights!

Points: 867
More actions
July 26, 2013 at 5:35 am

#280572

i have a table dbo.student_mark_details which contains 4 columns like
student_id,name,age,marks
in my usp_student_details procedure i have 2 parameters as a i/p like @columns,@values
my i/p format is
exec student_details @columns='name,age,marks',@values='a,22,85'
here how do i insert these column values in a single row in my procedure
can anyone help me?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

nitha jen (7/26/2013)
i have a table dbo.student_mark_details which contains 4 columns like
student_id,name,age,marks
in my usp_student_details procedure i have 2 parameters as a i/p like @columns,@values
my i/p format is
exec student_details @columns='name,age,marks',@values='a,22,85'
here how do i insert these column values in a single row in my procedure
can anyone help me?

The best way to handle this is to rewrite your stored proc. What you have going on here is nightmare to maintain. The only way you can do something like that is with dynamic sql in your proc. Now there is nothing wrong with dynamic sql but the way you would have to do it here is going to cause you nothing but hassles. Here are a few of the MAJOR challenges with type of approach:

1) It is nearly impossible to validate that the number of columns and values are the same.

2) It is very difficult to validate your data.

3) It is very likely to be vulnerable to sql injection.

4) You have to parse all of your columns and values inside your proc.

In short, don't do this. Create your proc to receive the values it needs each as their own parameter.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

neethu payal SSC Eights! Points: 867 More actions · Answer 2

so there is a way to get a o/p using dynamic sql right..but how....i don't know many thing about dynamic sql....how do i insert the values in a same row without changing the i/p format?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

nitha jen (7/26/2013)
so there is a way to get a o/p using dynamic sql right..but how....i don't know many thing about dynamic sql....how do i insert the values in a same row without changing the i/p format?

Before delving into the intricacies of dynamic SQL, you would be well advised to heed Sean's advice and learn the basics of stored procedure parameterisation. This could save you from following a disastrous design decision too far to backtrack and rewrite.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

nitha jen (7/26/2013)
so there is a way to get a o/p using dynamic sql right..but how....i don't know many thing about dynamic sql....how do i insert the values in a same row without changing the i/p format?

What is o/p and i/p? Is that input and output?

Trust me you do NOT want to do this with dynamic sql. You will spend countless hours on trouble tickets because it just isn't going to work correctly. Especially since you say you don't know much about dynamic sql. This is a very dangerous direction to go with an insert proc. Keep it simple. Change the list of parameters so that each column has it's own parameter.

If you are deadset on going down the path that will lead to your own doom that is your choice. Just keep in mind in a few months that you were warned.

You will have to parse or split each of your two inbound parameters. The best way to do that is by reading the article in my signature about splitting strings. You will then use the results of that parsing to build a dynamic sql string and ultimately execute it against your database. Beware that with a dynamic number of columns comes the very real threat of sql injection. You need to google/bing sql injection and understand what that means because the threat is very real.

Check out this article about dynamic sql. http://www.sommarskog.se/dynamic_sql.html

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

neethu payal SSC Eights! Points: 867 More actions · Answer 5

thank u sir for your advise....using dynamic sql is dangerous means surely i will change my procedure ....