CSV Parameter in proc

Question

Post reply

CSV Parameter in proc

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
February 25, 2012 at 9:13 am

#256429

Hi ;
This is my procedure input parameter , I want split the column name and column value to insert the temp table, and next to move the main table production table.
Temp table like columnname and value
Example
Create Proc sample
(
@name VARCHAR(MAX)
)
@NAME='column1=value1,column2=value2,column3=value3'
Create Table #temp(columnname varchar(max),value varchar(max))
My Question :
1.How can I split the input parameter in desired format
2.How to find the column Value
any help would be appreciated

Viewing 10 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use a three-step scenario:

Step1: use the DelimitedSplit8K function referenced in my signature with delimiter ','

Step2: using the result set, apply the DelimitedSplit8K function with delimiter '='

Ste3: Use The CrossTab method (also referenced in my signature) to build a table-like structure with the correct column names and corresponding value

As a side note: I recommend not to use your desired #temp structure since it really isn't in a normalized structure.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sqlfriends@sql.com SSCommitted Points: 1504 More actions · Answer 2

Hi;

Here i use the DelimitedSplit8K Function and splitting the string.

DECLARE @Tmp TABLE(TID INT,ITEM VARCHAR(MAX))

DECLARE @STR VARCHAR(MAX)

SET @STR='COL1=VAL1,COL2=VAL2,COL3=VAL3,COL4=VAL4,COL5=VAL5'

INSERT INTO @Tmp

select * from dbo.DelimitedSplit8K(@STR,'=')

SELECT *

FROM @Tmp

TID COLUMNNAME

1COL1

2VAL1,COL2

3VAL2,COL3

4VAL3,COL4

5VAL4,COL5

6VAL5

I Want this type of format temp table;

TID columnname Value

1 COL1 VAL1

2 COL1 VAL1

3 COL3 VAL3

4 COL4 VAL4

5 COL5 VAL5

My question is ,

1. The column names are comes at dynamically , How to spllit the row for column value and column name.

Please given the any solution above my question.

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

Let me quote from my previous post:

Step1: use the DelimitedSplit8K function referenced in my signature with delimiter ','
Step2: using the result set, apply the DelimitedSplit8K function with delimiter '='

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

I would definitly go for splitting the string into rows at the ','-characters using the DelimitedSplit8K procedure. i.e. I agree with "step 1". Be aware though of 2 things: the 8K limit on the string you provide (the name of the procedure should be enough warning on that, but still..). And secondly, you must be sure that there can never be a ',' in the data (or the column names) that you want to provide through your procedure.

I would however not use the procedure again to split at the '=' signs in step 2. This because we know that in step 2 we always need to split the strings exactly once, at the first '='-character. I would prefer to use in step 2 either charindex() or a tally table to find the position of the first '='-character per string and then do 2 substring() calls to get the name of the column and it's value.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

LutzM SSC Guru Points: 107049 More actions · Answer 5

@r.P.Rozema:

I absolutely agree. Completely missed the fact that we have a "known scenario" for step 2.

I'd go for the CHARINDEX approach then. Thank you for the correction!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

I have to agree with Joe here,

SQL is not good at this.

But more than it not being good functionaly, it is very bad practice. Carrying on down this route will be setting yourself up for further pain further on.

Dave

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Dave Ballantyne (2/26/2012)
But more than it not being good functionaly, it is very bad practice.

Ok... so what is the alternative best practice that you're offering?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

faijurrahuman17 (2/25/2012)
Hi;
Here i use the DelimitedSplit8K Function and splitting the string.
DECLARE @Tmp TABLE(TID INT,ITEM VARCHAR(MAX))
DECLARE @STR VARCHAR(MAX)
SET @STR='COL1=VAL1,COL2=VAL2,COL3=VAL3,COL4=VAL4,COL5=VAL5'
INSERT INTO @Tmp
select * from dbo.DelimitedSplit8K(@STR,'=')
SELECT *
FROM @Tmp
TID COLUMNNAME
1COL1
2VAL1,COL2
3VAL2,COL3
4VAL3,COL4
5VAL4,COL5
6VAL5
I Want this type of format temp table;
TID columnname Value
1 COL1 VAL1
2 COL1 VAL1
3 COL3 VAL3
4 COL4 VAL4
5 COL5 VAL5
My question is ,
1. The column names are comes at dynamically , How to spllit the row for column value and column name.
Please given the any solution above my question.
Thanks

As the others have stated, SQL Server isn't very good at this type of thing. That, notwithstanding, I believe you may find the answer to this problem in the following article.

http://www.sqlservercentral.com/articles/T-SQL/63003/

Of course, you could always use (ugh!) XML for such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Jeff Moden (2/26/2012)
Dave Ballantyne (2/26/2012)
But more than it not being good functionaly, it is very bad practice.
Ok... so what is the alternative best practice that you're offering?

Point 🙂 Best practice and best way forward here .. uknown.

The issue to me would be to start with why the app needs to tell sqlserver which columns it is expecting. Why in this case can there not be a 'hardwired' select statement.

Is this just lazyness ? Lack of knowledge ? Trying to be so sharp , that you will cut yourself 🙂

If the columns are unknown , are we dealing with unstructured data ?

Sparse columns , XML , EAV are the things that i would be investigating, but this is sounding like a bad design choice. Even leaving aside injection attacks.

From this distance , hard to know.....

Clear Sky SQL
My Blog[/url]