Notes July 1st to July 25th 2009


UDFs vs. Stored Procedures
UDFs and stored procedures are both SQL Server objects that store one or more T-SQL statements in a single named, executable routine. Although you can often implement the same or similar functionality using either a UDF or a stored procedure, the code will look significantly different depending on which technique you choose. Here are the main differences between UDFs and stored procedures:

A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn’t have to.
You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
A UDF can’t use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
A UDF can’t change server environment variables; a stored procedure can.
A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you’ve used proper error handling code.

======== pages are kept in pageviewer webpart
uploading documents to sp doclibrary :


Option 4: ASPX pages added to SharePoint Site — ADDED 15-March-2007 UPDATED 16-November 2007

(Thanks to Michal Gwozdek for emailing me an updated set of steps that work for him)

This option actually was suggested in the comments by a reader.  I thought it was so good i tried it out … and it works great!  So here it is.

This option allows you to add your ASP.Net application pages into your SharePoint Site.  It also provides for compiling all using the code behind your pages into a DLL.

In a nutshell this option allows you to build your ASP.Net application outside of SharePoint, build it, test it & then add it to SharePoint.  Its great!

Here is how to do it:

1. Install the Visual Studio 2005 Web Application Projects extension.  This gives you the ‘old style’ web projects in Visual Studio … so you can compile down to a single DLL etc…

2. START – File – New Project – ASP.NET Web Application – Name it “ItDoesWork”

3. Add reference to Microsoft.Sharepoint

Leave only Microsoft.SharePoint, System, and System.Web

4. In the Solution Explorer create folder “~masterurl” and add masterpage “default.master” inside

5. Replace code behind for the masterpage with:

using System;
using Microsoft.SharePoint;

namespace ItDoesWork._masterurl

public partial class _default : System.Web.UI.MasterPage

protected void Page_Load(object sender, EventArgs e)



6. In the designer, rename ContentPlaceHolder’s ID to “PlaceHolderMain”

7. Delete Default.aspx, and add new page – SamplePage.aspx

8. Replace source content with the following:

<%@ Page Language=”C#” MasterPageFile=”~masterurl/default.master” CodeBehind=”SamplePage.aspx.cs” Inherits=”ItDoesWork.SamplePage” Title=”Untitled Page” meta:webpartpageexpansion=”full” meta:progid=”SharePoint.WebPartPage.Document” %>

<asp:Content ID=”Content5″ ContentPlaceHolderID=”PlaceHolderMain” runat=”server”>

Testing Page…

<asp:Label ID=”Label1″ runat=”server” Text=”Label”></asp:Label>


9. Replace code behind for the page with:

using System;
using Microsoft.SharePoint;
namespace ItDoesWork

public partial class SamplePage : System.Web.UI.Page

protected void Page_Load(object sender, EventArgs e)

Label1.Text = SPContext.Current.Site.Url;




10. Project properties – Build – Output path:

Point it to \BIN folder of our SharePoint Web application. E.g.


You can also manually copy your projects DLL into the \BIN folder each time.

11. Compile your project.

12. Open the web.config file for the SharePoint Web Applicaiton E.g.


13. Add the following line to the SafeControls section (change to suit your assembly and namespace etc…)

<SafeControl Assembly=”ItDoesWork” Namespace=”ItDoesWork” TypeName=”*” />

14. Change the <trust level=”WSS_Minimal” originUrl=”” /> line to <trust level=”WSS_Medium” originUrl=”” />

15. Open your site in SharePoint Designer and drag and drop your SamplePage.aspx page into a folder in your site.

16. Browse to your page E.g.

17. Jackpot! (Hopefully) You should now have your aspx page running in SharePoint.

How do I use a variable in an ORDER BY clause?

    blatID INT,
    hits INT,
    firstname VARCHAR(3),
    email VARCHAR(9)
INSERT blat VALUES(1, 12, ‘bob’, ‘’)
INSERT blat VALUES(2, 8,  ‘sue’, ‘’)
INSERT blat VALUES(3, 17, ‘pat’, ‘’)
INSERT blat VALUES(4, 4,  ‘pam’, ‘’)
INSERT blat VALUES(5, 1,  ‘jen’, ‘’)
INSERT blat VALUES(6, 3,  ‘rod’, ‘’)
INSERT blat VALUES(7, 5,  ‘nat’, ‘’)
INSERT blat VALUES(8, 19, ‘rob’, ‘’)
INSERT blat VALUES(9, 24, ‘jan’, ‘’)
INSERT blat VALUES(10, 0, ‘meg’, ‘’)
The first thing people tend to try in T-SQL is the following:
SET @col = ‘firstname’
However, this returns:
Server: Msg 1008, Level 15, State 1, Line 4
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Clearly, SQL Server doesn’t like variables in the ORDER BY clause. Well, we can just use dynamic SQL, right?
SET @col = ‘firstname’
How to avoid cursors

How to avoid cursors in SQL Server
This item has been flagged as inapproriate content. Are you sure you want to view it?
Yes, I want to view it!
An example of a SQL Cursor that we want to avoid:

DECLARE @CustomerID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
— declare cursor called ActiveCustomers
DECLARE ActiveCustomers Cursor FOR
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1
— Open the cursor
OPEN ActiveCustomers
— Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName
— perform action whilst a row was found
Exec MyStoredProc @CustomerID, @Forename, @Surname
— get next row of cursor
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName
— Close the cursor to release locks
CLOSE ActiveCustomers
— Free memory used by cursor
DEALLOCATE ActiveCustomers

Cursor alternative 1: Using the SQL WHILE loop:

SQL provides us with the WHILE looping structure. This can be utilised with a temporary table that enables us to avoid using a cursor:

— Create a temporary table, note the IDENTITY
— column that will be used to loop through
— the rows of this table
CREATE TABLE #ActiveCustomer (
RowID int IDENTITY(1, 1),
CustomerID int,
FirstName varchar(30),
LastName varchar(30)
DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)

— Insert the resultset we want to loop through
— into the temporary table
INSERT INTO #ActiveCustomer (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1

— Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

— loop through all records in the temporary table
— using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
SELECT @CustomerID = CustomerID, @FirstName = FirstName, @LastName = LastName
FROM #ActiveCustomer
WHERE RowID = @RowCount

EXEC MyStoredProc @CustomerID, @FirstName, @LastName

SET @RowCount = @RowCount + 1

— drop the temporary table
DROP TABLE #ActiveCustomer

We can see the above code gives the same functionality as the first code example but without using a cursor. This gives us the benefits that the Customer table is not locked as we are looping through our resultset so other queries on the Customer table that are submitted by other users will execute much faster. We will also have a faster operating SQL script by avoiding cursors which are slow in themselves.

Cursor Alternative 2: Using User Defined Functions:

Cursors are sometimes used to perform a calculation on values that come from each row in its rowset. This scenario can also be achieved by replacing a Cursor with a User Defined Function. An example of a User Defined Function performing a calculation is given below:

— return a discount %age that the customer
— can recieve based on their no. and value
— of purchases
CREATE FUNCTION dbo.GetDiscountLevel(
@CustomerID int
DECLARE @DiscountPercent int
DECLARE @NumberOrders int, @SalesTotal float

SELECT @NumberOrders = COUNT(OrderID),
@SalesTotal = SUM(TotalCost)
FROM Sales
WHERE CustomerID = @CustomerID

IF @SalesTotal > 5000.00 AND @NumberOrders > 5
SET @DiscountPercent = 5
IF @SalesTotal > 3000.00 AND @NumberOrders > 3
SET @DiscountPercent = 3
SET @DiscountPercent = 0

Return @DiscountPercent
An example of this function being used to replace a cursor might look something like:

SELECT FirstName, LastName, dbo.GetDiscountLevel(CustomerID) As DiscountPercent
FROM Customer


In this article we have seen how SQL Cursors can cause performance problems and affect other queries by locking tables. We have demonstrated two popular ways of avoiding the use of cursors. The WHILE loop does avoid the use of a Cursor but it still uses an iterative loop. The User Defined Function option keeps our SELECT query tidy and enables us to perform calculations using column values from our SELECT statement.

When attempting to redesign your code to avoid cursors you should always check the execution time of your scripts. Very occassionally you may find a cursor gives better performance than an alternative method of performing the same task. When doing this remember that the script execution time is not the only thing to check, impact on queries being run by other users at the same time is also a key factor.

Visual Studi0

Through code how to put debugger




XML : USage of CDATA

From a UI screen we have different controls to enter data. On click of submit button we are preparing a xml string .
Characters like < , & are treated as special characters by XML parser.
use validaterequest= false in page directive for ignoring “<” and use CDATA in front of the controls to make the parser ignore special characters like &

<?xml version=’1.0′ encoding=’ISO-8859-1′ ?>




      <TicketShortDescription><![CDATA[Ticket Short Description 1]]></TicketShortDescription>

      <TicketDescription><![CDATA[Ticket Description]]></TicketDescription>

      <SourceToolCreatedDate>2/3/2009 10:30 AM</SourceToolCreatedDate>

      <CustToolCreatedDate>2/3/2009 10:30 AM</CustToolCreatedDate>     

      <CustomerCode>Customer Code 1</CustomerCode>











and passing this xml string to a webservice and reading the nodes and inserting the node values into db.But using cdata we can validate the input control values.
C#   string.IsNullOrEmpty


Build the query and assign it to a string and use sp_executesql and N to execute the query

USE AdventureWorks;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N’SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID’;
SET @ParmDefinition = N’@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT’;
SET @IntVariable = 22276;
EXECUTE sp_executesql
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
— This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
— This SELECT statement uses the value of the OUTPUT parameter in
— the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;


2 Responses to Notes July 1st to July 25th 2009

  1. мышкин says:

    Я, хоть и не ваш постоянный читатель, но всё же выскажу свое мнение. На ваш сайт попал совсем случайно. Однако узнал много чего нового и интересного. Так что, как говорится, АФФТАР ПЕШИ ИСЧО! 🙂

  2. Просто отлично. С нетерпением ждем новых сообщений на эту же тему 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: