New Yak around... after having stuggled with my blogs @Dotnetjunkies and sending numerous emails to the admin to fix the issues and not getting any response, I decided to pull the chord off from DNJ. I am setting up my shop here @SQLTeam and hopefully I will have good time here. The initial response from the forums at least, has been very good

Parse delimited string in a Stored procedure

Sometimes we need to pass an array to the Stored Procrdure and split the array inside the stored proc. For example, lets say there is a datagrid displaying sales orders, each sales order associated with an orderid (PK in the Sales table). If the user needs to delete a bunch of sales orders ( say 10-15 etc) would be easier to concatenate all the orderid's into one string like 10-24-23-34-56-57-....etc and pass it to the sql server stored proc and inside the stored proc, split the string into individual ids and delete each sales order.

There can be plenty of other situations where passing a delimited string to the stored proc is faster than making n number of trips to the server.

CREATE PROCEDURE ParseArray (@Array VARCHAR(1000),@separator CHAR(1))


-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma

        DECLARE @separator_position INT -- This is used to locate each separator character
        DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value

        SET @array = @array + @separator

-- Loop through the string searching for separtor characters
        WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
                -- patindex matches the a pattern against a string
                SELECT  @separator_position = PATINDEX('%' + @separator + '%',@array)
                SELECT  @array_value = LEFT(@array, @separator_position - 1)
                -- This is where you process the values passed.

                -- Replace this select statement with your processing
                -- @array_value holds the value of this element of the array
                SELECT  Array_Value = @array_value
                -- This replaces what we just processed with and empty string
                SELECT  @array = STUFF(@array, 1, @separator_position, '')


Exporting blank SRS report to excel does not render the headers

I worked on RS reports for a while and had been confonted with this issue. When the report does not receive any resultset from the datasource it shows up only the header columns in the report manager which is all ok. In our case this particular report was being exported to Excel and strangely the RS did not send anything except a huge textbox with nothing in it.
And our requirement was that the excel spreadsheet should have the header columns even if there was no data. Now I thought of concatenating all the column names and use the NoRows property but that will be exported as 1 column rather than individual columns.

So I found some work-around for that and wrote up an article under SQL Server 2000. Check it out here.