[mssql] How do I format money/decimal with commas?

Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that

Below is an example:

DECLARE @v MONEY

SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)    --1322323.67    

/* Rounded but no formatting */

SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67    

/* Formatted with commas */

SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666 

/* No formatting */

 

If you have a decimal field it doesn't work with the convert function
The work around is to convert it to money

DECLARE @v2 DECIMAL (36,10)

SELECT @v2 = 13243543.56565656

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57

/* Formatted with commas */

[mssql] How To Get Output Into SQL Server Table

Have you every needed to get the information returned from a stored procedure (SP) into a SQL Server table? Or have you wondered how you might get the contents of an operating system (OS) file into a SQL Server table? How about placing the output of some Windows executable into a SQL Server table so you can manipulate it with T-SQL in some way? So, what methods can you use to accomplish these different, but similar tasks? This article will show you how to insert rows into a SQL Server table from various sources.

Getting Output from a Stored Procedure into a Table

The most commonly asked question I get is "How can I get the output of a SP into a SQL Server table." Now, if you did not know how to do this you might be inclined to just copy the code from the stored procedure and paste it into your T-SQL code. This way your code can insert the record set into a table instead of trying to get it from an existing SP. However, there are alternatives to this cut and paste method. You can use the "execute_statement" option on an "INSERT" statement, to get the output of a stored procedure into a table easily. Here is an example of getting the output of the "sp_who" SP into a table:

set nocount on

create table #sp_who (

  spid      smallint,

  ecid      smallint,

  status    nchar(30),

  loginame  nchar(128),

  hostname  nchar(128),

  blk       char(5),

  dbname    nchar(128),

  cmd       nchar(16))

insert into #sp_who execute sp_who

  select * from #sp_who

drop table #sp_who

As you can see, this is a very simple process. I first created a table that contains a column for each column returned from the "sp_who" SP. In my example, each column of my temporary table has the same name, data type and length as those returned from the "sp_who" SP. After the table is created, all it takes to get the SP's output into a table is to issue an INSERT statement that executes the "sp_who" stored procedure. Once this INSERT statement completes SQL Server will place all the records displayed by the "sp_who" SP in the temporary table #sp_who. Using this method allows me then to manipulate the "sp_who" output however I would like. If a store procedure outputs multiple record sets then the above method, to get the output of the stored procedure into a table, will not work. In fact, if you try it you will receive an error.

Getting the Content of an OS File into a Table

Now suppose that you want to get the contents of an OS file into a T-SQL table.
I am not talking about the raw, comma delimited text file here, but instead I am referring to just a plan unformatted text file. Do you have any ideas how this might be accomplished? It is quite easy to accomplish this with "xp_cmdshell" and a one-column table. Let's go through an example of how this would be done. For my example, let's get the current SQL Server ERRORLOG file into a temporary table. Here is the code to do that:

create table #errorlog(line varchar(2000))

insert into #errorlog

  execute xp_cmdshell 'type "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG" '

select line from #errorlog

drop table #errorlog

As you can see, I have basically used the same method as getting a stored procedure's output into a table. Except this time I used the xp_cmdshell extended stored procedure to execute the DOS "type" command. The DOS "type" command just displays the content of the file specified. Therefore by using the execute clause on the INSERT statement in conjunction with the "type" command of DOS I was able to get the contents of the ERRORLOG OS file into a SQL Server table.
Now I can use T-SQL to manipulate the data from an OS file by using this process. The only drawback here is each line in the OS file is stored in a single column within a row. Therefore, if you want to search and extract stuff you will need to use the CHARINDEX, PATINDEX, and/or SUBSTRING functions.

Getting Multiple Record Sets into a Table

Remember earlier when I said if a store procedure returns multiple record sets that you get an error when trying to use the EXECUTE clause of an INSERT statement. Now let me show you a way to get around this issue. The trick to making this work is to first route the output from the SP into an OS file, then use the method I showed you above to get the OS file into a SQL Server table. Let me go through an example.
Say I want to get the output of the "sp_spaceused" SP, into a SQL Server table. Because it produces multiple record sets, I am not able to use the first method I discussed above. Here is some code that will allow me to get the output of the "sp_spaceused" SP into a SQL Server table.

create table #errorlog(line varchar(2000))

execute master.dbo.xp_cmdshell 'osql -SYourSQLMachine

  -E -Q"execute sp_spaceused" -o"c:\temp\sp_out.txt" -s"" '

insert into #errorlog

  execute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt" '

select line from #errorlog

drop table #errorlog

Here you can see I used xp_cmdshell to execute the OSQL utility. By using this utility I was able to direct the output of the "sp_spaceused" SP directly into an OS file using the -o option. I then used the procedure described in the preceding section to get the OS file created by the OSQL utility into my temporary table named #errorlog. Once again, using this method causes each line of output from the "sp_spaceused" SP to be stored in a single column record in my temporary table.

Output From an Executable Into a Table

For my last example, I will show you how to get the output of an ".exe" into a SQL Server table. Suppose I want to get my server's IP configuration information into a SQL Server table so I can manipulate it. To display this information you would run the "ipconfig.exe" executable. So here is the code I would use to get the IP configure information into a table:

create table #ipconfig(line varchar(2000))

insert into #ipconfig

  execute xp_cmdshell 'ipconfig.exe'

select line from #ipconfig

drop table #ipconfig

Here the code is similar to the code I used to get the contents of an OS file into a table. The only difference is instead of running a DOS command with xp_cmdshell , I ran the "ipconfig.exe" executable.

Conclusion

With the use of an INSERT statement, a SQL Server table, and the occasional execution of the xp_cmdshell extended stored procedure it is possible to get almost anything into a SQL Server table. I hope that the next time you need to get the output of a stored procedure, physical file, and/or the output of an ".exe" into a SQL Server table you will consider using one of the processes I described in this article.