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

Passing xml to use as search criteria in a query (optional parameters)

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

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

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.

T-SQL Update with Inner Join

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

SQL Grant Execute

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