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

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

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