SQL Server - Stored Procedure returning table with dynamic columns

We had a stored procedure that returns some data, later we wanted to add some columns to this data, and these columns are dynamic.
We found several articles on how to use pivot to create dynamic columns in tables, for example
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

and others,

All using the same method, constructing the column headers in a variable, then creating inline sql and using pivot statement and this variable as the columns

In our case we had already a very big select statement, that we didn't want to alter, we just wanted to add more columns to it.

We thought of using table user defined function, but then we had to define the table columns, which we can't do, as it should be dynamic.

We thought also of using a view, then to join with this view, but we can't use pivot in views

We thought also of using a separate stored procedure and calling this stored procedure from the original one, but we found that this is not supported solution.

Finally we tried something and it worked, we alerted the original stored procedure like the following: We inserted the output of the first select statement into a temp table, and then added the second select and pivot in an inline sql statement, and in this statement we inner joined with our temp table.

An example of what we did

Suppose the first statement is like the following

Select * from table1

and our pivot statement is like the following


@query = 'select * from table2 pivot (count(columnName1) for ColumnContainingData in (' + @columnNames + ')'

where columnsName1 is the column that we will perform the aggregate function on, @columnNames is a variable with column names comma seperated and surrounded with []

the final statement looked lke the following

select * into #tempTable from
(select Select * from table1) t
@query = 'select * from table2 pivot (count(columnName1) for ColumnContainingData in (' + @columnNames + ') t2 inner join #tempTable on #tempTable .SomeId = t2.SomeId'

Handling comma separated values parameter in a stored procedure without using inline sql

I have seen lots of implementation for comma separated values parameters, all using inline SQL to construct the query.
for example:
Suppose the parameter is @param having the value '1,2,3'
and we are selecting rows with state in (1,2,3)

Then the query would look like this

query='select * from TableName where state in (' + @param + ')'
exec query

Since i don't like inline SQL, i was looking for another methods

I found this method somewhere, i don't remember where exactly. It is based on constructing a table, and inserting the comma separated values in this  table, then in the query either we can inner join with this table to get the desired values only or we can use where clause

example on constructing this temp table



declare @param nvarchar(50)
set @param = N'1,2,3,4'
IF EXISTS
(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#temptable'))
BEGIN
DROP TABLE #temptable
END

CREATE TABLE #temptable(Code int)

   DECLARE @code varchar(10), @Pos int

    SET @param = LTRIM(RTRIM(@param))+ ','
    SET @Pos = CHARINDEX(',', @param, 1)

    IF REPLACE(@param, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
                SET @code = LTRIM(RTRIM(LEFT(@param, @Pos - 1)))
                IF @code <> ''
                BEGIN
                        INSERT INTO #temptable (code)
                        VALUES (CAST(@code AS int)) --Use Appropriate conversion
                END
                SET @param = RIGHT(@param, LEN(@param) - @Pos)
                SET @Pos = CHARINDEX(',', @param, 1)

        END
    END
--select * FROM #temptable

Next in the main query, it can be like this

Select * FROM TableName
INNER JOIN #temptable on TableName.State = #temptable.code

or

Select * FROM TableName
where state in (select code from #temptable)

MS CRM Plugin development best practices

While i was debugging, I noticed that update plugin was lots of times, and each time it was called all case properties was sent in the context, While not all of them are updated. so i started searching on how to make sure that only the updated property is sent to the update plugin.
I found this article which is very useful
http://blogs.inetium.com/blogs/azimmer/archive/2010/01/25/plugin-best-practices-in-crm-4-0.aspx

In summary what i was doing wrong is like the following:
- I was doing retrieve entity then after, i was doing update to that entity, while the correct thing is that to do update without doing retrieve.
- When retrieving for any other reason, i was getting all columns, the correct thing to do is to get the desired columns only

MS CRM passing parameters in the query string

By default MS CRM 4 doesn't accept passing parameters in the query string, and it gives CRM error message
To enable passing parameters
follow the instructions in this URL
http://blogs.msdn.com/b/rextang/archive/2008/09/24/8962549.aspx

by adding a DWORD registry key under MSCRM named DisableParameterFilter and setting the value to 1

MS CRM Changing business unit for a user gives an error

We were trying to change the business unit for a user and it was giving us CRM error.

We already have increased the timeout long time ago by adding the registry keys as instructed here http://support.microsoft.com/kb/918609

Still having the same error.

Lastly we tried cleaning up the Async table as instructed here http://support.microsoft.com/kb/968520

After doing this we were able to change the business unit for the users

MS CRM4: Creating View and setting filter criteria programmatically

We wanted to create a view with criteria on the “Due Date” field to show all records that have due date on or before today

Checking the view creation wizard, we can select on or before and we must specify specific date, we can’t specify  it as “Today”

I have came up with a workaround for this, I have created the view and set the criteria to specific date, 01/01/1999 for example, any date. Then I created a plugin to work on execute message, on pre-event, then in the plugin I was parsing the FetchXML of the execution, if the primary entity is same as the view entity, and there is a condition on “Due Date” and the value is “01/01/1999” then to change this value to today’s date in the FetchXML.

main code snippets

//get FetchXML from the context
fetchXml = (string)context.InputParameters.Properties["FetchXml"];

//get the entity name
string entity = xmlDoc.SelectSingleNode("fetch/entity").Attributes["name"].Value.ToString();

if (entity == "incident") // here I am using incident, replace with your entity name
{

    if (xmlDoc.SelectSingleNode("fetch/entity/filter") != null)
    {

        // Check if there are conditions exists
        if (xmlDoc.SelectSingleNode("fetch/entity/filter/condition") != null)
        {
            // Loop through the conditions to look for the followup by condition
            nodes = xmlDoc.SelectNodes("fetch/entity/filter/condition");
            foreach (XmlNode node in nodes)
            {
                if (node.Attributes["attribute"] != null && node.Attributes["operator"] != null && node.Attributes["value"] != null)
                {
                    string slaOnOrBefore = "01/01/1999";
                    if (node.Attributes["attribute"].Value.ToString().ToLower() == "followupby".ToLower() && node.Attributes["operator"].Value.ToString().ToLower() == "on-or-before".ToLower() && node.Attributes["value"].Value.ToString().ToLower() == slaOnOrBefore.ToLower())
                    {
                        node.Attributes["value"].Value = DateTime.Today.AddDays(-1).ToString("yyyy-MM-ddT00:00:00");
                    }
                }
            }
        }
    }

    //Update the fetch xml
    fetchXml = xmlDoc.InnerXml.ToString();
    context.InputParameters.Properties["FetchXml"] = fetchXml;