Sunday, February 26, 2012

Returning a value from a dynamic SQL Statement

There have been times when I have needed to create a dynamic SQL Statement within a stored procedure.  You need to consider permissions of the process that is executing the dynamic sql statement but that will not be addressed here.  I had a need on a recent project to be able to execute a dynamic sql statement from a stored procedure and use its result within that same procedure.  Here is simple example of how you can do this.

This stored procedure accepts two integer parameters.  It converts them into a string values as it builds the SQL statement.  The key is that the data we desire to use is inserted into a temporary table within the dynamic sql statement.  This temporary table is created within this stored procedure and is accessible by the dynamic sql statement.  This allows us to query the data from the temporary table and use it later on within the stored procedure.


CREATE PROCEDURE dbo.Test
  @a int
  , @b int
AS
BEGIN

 DECLARE @return_status int
 DECLARE @sql varchar(8000)
   
 SET @sql = 'INSERT INTO #Temp(result_value)
             SELECT ' + convert(varchar(20), @a) + ' + ' + convert(varchar(20), @b);
   
 CREATE TABLE #Temp (
      result_value int null
 )


 EXEC (@sql);

 SELECT *
 FROM #Temp

 DROP TABLE #temp

END



This is the execution and results.

exec dbo.Test 1, 2

result_value
3

(1 row(s) affected)

SharePoint Online Development experiences

These are some things that I have learned while doing SharePoint Online development that are not explicitly stated on the SharePoint online developer site (http://msdn.microsoft.com/en-us/library/hh147180.aspx). Microsoft’s basic philosophy is that you can deploy sandbox solutions. Well … that is not completely true. Here are some things you can do in sandbox solutions that you cannot do SharePoint online

1.) You do not have access to some of the most useful objects in the Microsoft.SharePoint.Publishing assembly.  This make it difficult to make adjustments to the Global Navigation in code. For example, if you try to reference the PublishingWeb object, you get this error when you attempt to load the solution into the solution gallery.
















2.) You do not have access to the SPLimitedWebPartManager. This rules out adding or modifying web parts in code. The funny thing is their site says that you can deploy web parts. However, you are not allowed to add your deployed web part to a page via code.

System.TypeLoadException: Could not load type 'Microsoft.SharePoint.WebPartPages.SPLimitedWebPartManager' from assembly 'Microsoft.SharePoint, Version=14.900.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'


3.) Calendar Overlays are not functional even though they appear to be available through the SharePoint Online UI. If you try to add the overlay via code, you get this message.

System.MissingMethodException: Method not found: 'Microsoft.SharePoint.SPForm Microsoft.SharePoint.SPFormCollection.get_Item(Microsoft.SharePoint.PAGETYPE)'.

This is a documented bug. It’s not specific to SharePoint Online.  The Calendar Overlay functionality only works in the Default Zone.


4.) At one point, I added a simple webpart to my Visual Studio 2010 SharePoint project.  The web part only printed into a label the text "Hello World". I was able to deploy the solution into the Solution Gallery. However, I received the following error when I tried to insert the web part into a page.












I got around this issue by separating the control out into a different project.