SQL Server 2008 – Table Valued Parameters

Here is a great article that explains how to use TVP’s via .Net and a stored procedure

SQL Grant Execute for Stored Procs

Found this great article that has a really simple script to create the grant commands needed for all the procs in a database

We’ve had to generate a search facility for books on a site. The user types in any number of values and the results are ranked based on how many of the terms are actually found.

The example below has a hard coded XML data, but obviously this would be passed in as a parameter, and as you build this externally you can include as many terms as you need.

DECLARE @xml XML
SET @xml = '<search>
              <term text="and" />
              <term text="the" />
              <term text="princess" />
              <term text="9780719554285" />
            </search>';

/* Use a CTE to load the terms into a table for later joining */
With MatchingTerms  (Term)
AS ( Select node.value('./@text', 'varchar(2000)') AS Term
        FROM @xml.nodes(N'//term') T(node))

SELECT  b.id, 
            b.coverTitle,
            b.ISBN13,
            shortName as [binding],
            'B' as [type],
            count(id) as score
FROM    Book b,
           MatchingTerms x
WHERE
     b.coverTitle like '%' + x.Term + '%'
OR 
     b.ISBN13 = x.Term
GROUP BY 
     b.id,  b.coverTitle,  b.ISBN13, shortName

Accessing SQL 2005 from Visual Studio 2008

If you are attempting to add a data connection to a SQL 2005 database within visual studio 2008 you might encounter the following error…

Could not load file or assembly ‘Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.

You need to install the following…

Microsoft SQL Server System CLR Types
Microsoft SQL Server 2008 Management Objects

They can be found here as part of the SQL Server 2008 Feature Pack.

Differences between SCOPE_IDENTITY and @@IDENTITY

@@IDENTITY will return the last id that was created using your connection, therefore if your insert kicks off a trigger you could get the wrong id.

But SCOPE_INDENTITY() will return the last identity created

Delete with Inner join

The following example shows how to perform a delete using an inner join

DELETE 
    a 
FROM 
    StatementlineassociatedPayment AS a
INNER JOIN 
    rebatePatientStatementLine AS b  
ON  
    a.StatementlineId = b.Id
INNER JOIN 
    rebatePatientSummary AS c 
ON 
    b.summaryId = c.Id
WHERE 
    c.IssueStatus <> 2

CTE – Common Table Expression

The following is an example of a CTE we have used

WITH ProcessedTasks (PatientID, LowestTaskId)
AS
(
SELECT DISTINCT 
    RebatePatientSummary.PatientId, 
    MIN(RebatePayment.TaskEventId) AS Expr1
FROM
    StatementLineAssociatedPayment 
INNER JOIN
    RebatePayment 
    ON StatementLineAssociatedPayment.RebatePaymentId = RebatePayment.Id 
INNER JOIN
    RebatePatientStatementLine 
    ON StatementLineAssociatedPayment.StatementLineId = RebatePatientStatementLine.Id 
INNER JOIN
    RebatePatientSummary 
    ON RebatePatientStatementLine.SummaryId = RebatePatientSummary.Id 
INNER JOIN
    TaskEvent 
    ON RebatePayment.TaskEventId = TaskEvent.Id
WHERE
    (RebatePatientSummary.IssueStatus = 2) 
AND 
    (RebatePayment.TaskEventId <> 0)
AND
    (TaskEvent.EventStatus = 5)
AND 
    taskevent.rebateprocessed = 0
GROUP BY 
    RebatePatientSummary.PatientId
)

SELECT 
    t.Id,
    t.PatientId,
    t.RebateProcessed, 
    p.lowestTaskId, 
    t.SystemTaskId
FROM 
    TaskEvent as t
INNER JOIN 
    ProcessedTasks AS p 
    ON t.PatientId = p.PatientId
AND 
    t.Id <= p.LowestTaskId
WHERE
    EventStatus = 5
AND
    rebateprocessed = 0

Output Parameters with returned value

When using output parameter values you need to check that the value is not DBNull before using it…

using (SqlConnection conn = new SqlConnection())
{
    conn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings.Get("DBConnection").ToString();
    conn.Open();
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@ChapterId", _ChapterId));
        cmd.Parameters.Add(new SqlParameter("@Sequence", _Sequence));
        cmd.CommandText = "Get_ContentPrevNext";
        SqlParameter parameterPreviousId = new SqlParameter("@PreviousId", System.Data.SqlDbType.Int)
        {
            Direction = System.Data.ParameterDirection.Output
        };
        cmd.Parameters.Add(parameterPreviousId);
        SqlParameter parameterNextId = new SqlParameter("@NextId", System.Data.SqlDbType.Int)
        {
            Direction = System.Data.ParameterDirection.Output
        };
        cmd.Parameters.Add(parameterNextId);
        cmd.ExecuteNonQuery();
        if (parameterPreviousId.Value != DBNull.Value)
        {
            _PreviousId = Convert.ToInt32(parameterPreviousId.Value);
        }
        if (parameterNextId.Value != DBNull.Value)
        {
            _NextId = Convert.ToInt32(parameterNextId.Value);
        }
    }
}

Loading Image/BLOB from SQL Server

If you have an image column in your SQL table you can load the bytes using the following code.

int bufferSize = Convert.ToInt32(reader.GetBytes(7,0,null,0,int.MaxValue));
_ThumbnailImage = new Byte[bufferSize];
reader.GetBytes(7, 0, _ThumbnailImage, 0, bufferSize);


The first parameter is the column number, in this case 7, call GetBytes with a null buffer first to get the number of bytes needed.

Remember that an image column, holds bytes and therefore can contain any sort of document, not necessarily an image, could be a PDF

TRUNCATE TABLE – execute permissions

In SQL 2005 you can now use WITH EXECUTE AS to allow a normal user to truncate tables. It basically means they run the specificed procedure with higher rights than they have.

CREATE PROCEDURE TruncateMyTable
WITH EXECUTE AS OWNER
AS TRUNCATE TABLE MyDB..MyTable;

See MSDN for more details.

Previous Entries