Dynamic SQL,Tough One - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Dynamic SQL,Tough One

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-28 : 07:12:28

Sach writes "Hi ,
My requirement is peculiar . Following is the signature of a stored procedure :::


procedure get_csv
(  @I_Table     VARCHAR(200),
   @v_csv_data  varchar(8000)  output
)

This procedure accepts the name of a table as parameter and
returns all rows in this tables in form of CSV string .
eg


               Table  Mytable
           col1       col2       col3     col4
             1       abcde      pqrst       4
             1       abcde      pqrst       4
             1       abcde      pqrst       4

should end up as

           
            col1,col2,col3,col4 
            1,abcde,pqrst,4
	    1,abcde,pqrst,4
            1,abcde,pqrst,4

and this will be assigned to @v_csv_data .
I tried in vain using information_schema.columns view and cursors. But since the column names are also obtained dynamically I had problems fetching from cursor into variables.

Thanks in advance for any insight ."

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-28 : 08:23:42

These articles touch on the subject of converting rows to CSV.

[url]http://sqlteam.com/item.asp?ItemID=11021[/url]
[url]http://sqlteam.com/item.asp?ItemID=11021[/url]
[url]http://sqlteam.com/item.asp?ItemID=2368[/url]

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-28 : 08:24:54

NOt sure a stored procedure is the best place for this ... remember, you can only build a string of 8000 characters, so if there are more than, say 400 rows in a table and it takes about 20 characters to display 1 csv row for that table, you've reached your limit. That's a mighty small table!

Doesn't BCP do this?

(I am not reall an import/export/replication kind of guy so I never post here normally ....)

- Jeff

Edited by - jsmith8858 on 03/28/2003 08:26:12

Edited by - jsmith8858 on 03/28/2003 08:26:36

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 13:01:08

I am the bcp Queen! Well maybe the princess :-). Bcp can output rows from SQL Server into comma delimited rows in a text file. But Sach needs to be able to do this inside SQL Server so that he/she can use the string for later use.

Tara

mi_tu_ti
Starting Member

4 Posts

Posted - 2003-04-12 : 00:19:03

Hi All ,
I need to be able to pass the table name to the procedure as a parameter and inside the procedure , build a CSV . Thanks to you , I am aware of the limitations of this approach . But could anybody provide a guideline as to how this can be achieved ?
Unfortunately , all links given by Sam use hard coded table names.

Edited by - mi_tu_ti on 04/12/2003 01:04:52

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-04-12 : 01:37:20

why are you trying to do this in SQL server ... maybe it's best to do this sort of thing outside the database environment ... if you are trying to build a file that can be used to send updates or something along those lines it would be much simplier to get the table data and simply write it out in a loop ... rather than concatenate a string (which is not very optimized)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources