FlexiCoder Blog

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.

Below is an example of an Update that has an Inner Join

UPDATE
    Taskevent
SET
    RebateProcessed = 1
FROM
    TaskEvent
INNER JOIN
    TaskEventAsset
    ON TaskEvent.Id = TaskEventAsset.Id
WHERE
    PatientId = @PatientID
AND
    JobDate < @PriorTo
AND
     ConcentratorId = @ConcentratorId

Found an article that had a nice SQL script to assign execute permissions to a user in SQL2000

Here is the script

DECLARE @sql nvarchar(4000)

DECLARE @db  sysname ;
SET @db = DB_NAME()

DECLARE @u   sysname ;
SET @u = QUOTENAME('')

SET @sql ='select ''grant exec on ''
        + QUOTENAME(ROUTINE_SCHEMA) + ''.''
        + QUOTENAME(ROUTINE_NAME) + '' TO ' + @u
        + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
        'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

EXEC master.dbo.xp_execresultset @sql,@db