Tallan's Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Posts Tagged "T-SQL"

Making the Case for Entity Framework in the Enterprise

Recently, I was met with some friction by the IT department at a client where, they asserted, that a decision had been made years ago to ban Entity Framework. Like many enterprise environments, this client was understandably concerned with the potential pitfalls of embracing Entity Framework. That meant that my job was to convince them otherwise – not to discount their apprehension, but quite the contrary – to demonstrate how EF can be leveraged for its advantages, and avoided for its shortcomings.
Entity Framework (EF) is a broad framework with many optional parts. There are several aspects of EF that provide great benefit, while others are a source of great consternation – particularly from the perspective of the database purist. As the cliché goes, “with great power comes great responsibility,” and so this blog post explores different aspects of EF, and…

Retrieving Multiple XML Rows from a single row with an XML Column Type in SQL Server

Overview
I’m attempting to pull rows from a table with an XML Field Type.  I’m having issues getting multiple values from a single row.
Setup
Run the following query to set up the table:

CREATE TABLE CourseData (

CourseId int IDENTITY(1,1) PRIMARY KEY,

XmlData XML

)

INSERT INTO CourseData VALUES

(

‘<Course xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns=”http://someuri.local/CourseRecord.xsd”>

<SchemaVersion>1.1</SchemaVersion>

<CourseData>

<CourseCode>AAA999</CourseCode>

<CampusList>

<campus xmlns=”http://someuri.local/campus.xsd”>

<campusId>1</campusId>

</campus>

<campus xmlns=”http://someuri.local/campus.xsd”>

Two more tools for your bag of T-SQL tricks

Have you ever been writing a new view and had the need to use an ORDER BY only to be greeted by an error message when you go to create the view. Never fear, there is a solution. ORDER BY can only be used in views, inline functions, derived tables, sub-queries, and common table expressions if the keywords TOP or FOR XML are used. Thus we can either use TOP or FOR XML to get our solution. We will on talk about the TOP Keyword today and save FOR XML for another time.
TOP allows you to select the first ‘n’ records returned from a select. Consider the following table of s

Using Table Valued Variables instead of Dynamic SQL

I had a situation today where I have a stored procedure that is structured as follows:

1 CREATE PROCEDURE _Entity_GetByKeywordTypeDateRange( 2 @keyword varchar(255) = null, 3 @typeA bit = 0, 4 @typeB bit = 0, 5 @typeC bit = 0, 6 @startDate datetime, 7 @endDate datetime) AS 8 BEGIN 9 10 SELECT 11 EntityId, 12 TypeId, 13 — Field List 14 FROM 15 EntityTable 16 WHERE 17 — Where Clause 18 END

Due to the framework we’re using, I don’t have a lot of control over the argument list here, unfortunately.  I was trying to find out the best way to handle the bit flags.  Types A, B, and C correspond to TypeId’s 1,2, and 3 respectively.
I had toyed with using some kind of progression of OR statements…

\\\