Convert Column results to delimited string in TSQL

Use the below to convert the results of a query from this
123
125
156
254
214
To
123,125,156,254,214



DECLARE @result varchar(1000)

SET @result = ''

SELECT @result = @result + cast(order_id as varchar) + ',' FROM tblorders WHERE tblstatus = 2

select substring(@result, 0, len(@result))

This has worked for me no problems you can change the delimiter by changing the ' ,'



Original code that this was based on can be  found here

Check if Temp table exists

To check if a temp table exists and if not create one use the following

IF OBJECT_ID('tempdb..#archive') IS NOT NULL
begin
 drop table #archive
end
else
begin

create table #archive(orderid int)


This checks to see if the temp table #archive exists and if it does them it drops it before recreating it

Error Creating Data Table

Hi All just thought i would post this as i have just spent a bout an hour trying to work out why it would not work in my current project.

I have a  a function to create a data table and return it.


Public Function SQL2DataTable(ByVal sql As String)
        Dim SqlCon1 As String = "Data Source=sprintsql;Initial Catalog = database ;Integrated Security=SSPI;"
        Dim dt As New DataTable
        Dim da As New SqlClient.SqlDataAdapter(sql, SqlCon1)
        da.Fill(dt)
        da.Dispose()
        Return dt
    End Function

This is called like this

Dim dt As DataTable = SQL2DataTable(sql)

 now in this instance this would not work and came back with the error

    Unable to cast object of type 'System.Data.DataTable' to type 'Excel.DataTable'.

After a lot of frustration i found it was cause by having Imports Excel  declared at the beginning  Hope this saved you some time.