FlexiCoder Blog

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

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

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.

@@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

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

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

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);
        }
    }
}

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

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.