If you want to export the result of a query or Stored procedure from SQL Server Management Studio without writing any scripts then use following procedure:
You don't have to change any formats for this option. You just execute your SP or SELECT statement and once you got the output on the Results Window. Just "Right click" on the result and choose "Save Result As" to save it as CSV file.
One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio.
Step1: Open SQL Server Management Studio.
Step2: Click on New Query
Step3: Goto Menu Query >> Query Options >> Within Results Choose Text
Step4: On the right window you can see an option called "Output Format". Select "Comma Delimited".
Step5: Next either Press Control + T (to select Results to Text option). Or goto Menu Query >> Results To >> Results to Text option.
Now you are all set. Just run your SELECT statement or SP with the SELECT statement to see the result as Comma Separated Values on the screen.
You can copy and save it as a file now.