EL Jerry (4/13/2012)

---

mtassin (4/13/2012)

---

I just pasted it into Excel and used text to columns, but the same principal applies. Looking at 10 rows not ordered by the amount that...

Henrico Bekker (4/12/2012)

---

I've done it as well... "how do you detach a Log Shipped tempdb that is in Read...

Hugo Kornelis (4/13/2012)

---

1. It would have been better to present the data ordered by salary. I first tried to order them by head, but I was constantly afraid that I'd...

I was aware of what contained databases are...

At first I thought this was a trick question, because if you can't use AD credentials against a contained database then what good...

If you have the linked servers setup correctly it's as simple as referencing a table via

[linked server name].[database name].[schema name].

Where the linked server name is the name you called...

And before you say it, I do realize that at least in the text of the query I have the name of the procedure (after all I did copy it...

They can get divorced from the procedure for other reasons.

For instance this stored procedure that's in my plan cache doesn't have a value for Object_Name(dest.objectid)

/****************************************** PROCEDURE: ...

marksquall (4/10/2012)

---

To Sir Grant Fritchey and Sir Mark Tassin:

You two have mentioned something about cache. I have certain things to know abut this cache and I hope it is...

And for that matter, if it was this... we'd call it a catch-all query no?

ALTER PROCEDURE [dbo].[SearchRecords]

@PersonID NVARCHAR(20),

@FirstName NVARCHAR(20),

@MiddleName NVARCHAR(20),

@LastName NVARCHAR(20)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'SELECT * FROM...

Grant Fritchey (4/10/2012)

---

First off, you are using sp_executesql, which is good, as I said. Because of this, you're not generating hundreds & thousands of variations on the query. However, we're...

GilaMonster (4/10/2012)

---

mtassin (4/10/2012)

---

Yes there's a CPU penalty to pay, but wouldn't it wind up with just three plans in the cache? The original one that builds the query plus...

Ok Grant, so now I can learn...

If he wrote it as

ALTER PROCEDURE [dbo].[SearchRecords]

@searchQuery AS NVARCHAR(100),

@col AS VARCHAR(100)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(1000)

DECLARE @value NVARCHAR(20)

SET @value = @searchQuery

IF (@col = 'PERSON_ID')

BEGIN

SET...

The correct answer, as always is... it depends.

If you choose to use Dynamic SQL, then you need to be warey of injection.

Creating a dynamic SQL string by appending text type...

Right... but if it hadn't been dynamic it would likely have been

SELECT * FROM [Student] WHERE [PERSON_ID] = @value

Which wouldn't have gone implicit. Because he chose to use dynamic...

GilaMonster (4/10/2012)

---

mtassin (4/10/2012)

---

Really? Wow... I recall a time when if you had two queries like this nested inside of IF statements

(granted this is a really simple one) that the...