drew.allen (6/11/2015)

---

ScottPletcher (6/11/2015)

---

You can't add Librarian id into the GROUP BY and accurately answer the query, since multiple librarians could have the same surname. You could pre-aggregate, but you...

You can't add Librarian id into the GROUP BY and accurately answer the query, since multiple librarians could have the same surname. You could pre-aggregate, but you must still...

I admit I can't imagine this type of proc being exposed to an external application or user. It seems much more internal to me, and thus the chance for...

Been busy today, but I think I've got the errors fixed now. I'm definitely not using XML and STUFF when a few simple functions will do. Just the...

ALTER PROCEDURE sampleprocedure

@tablename AS varchar(4000),

@date AS date

AS

SET NOCOUNT ON;

DECLARE @query nvarchar(4000);

SET @query = 'SELECT * FROM ' +

...

Put in a query hint to force a HASH join. I'd rely more on that than on an ARITHABORT setting.

Lynn Pettis (6/10/2015)

---

ScottPletcher (6/10/2015)

---

Sorry, I was being lazy and didn't include checks for db name and schema:

ALTER PROCEDURE sampleprocedure

@tablename AS varchar(4000),

@date AS...

Sorry, I was being lazy and didn't include checks for db name and schema:

ALTER PROCEDURE sampleprocedure

@tablename AS varchar(4000),

@date AS date

AS

SET NOCOUNT ON;

DECLARE...

CREATE PROCEDURE sampleprocedure

@tablename AS varchar(4000),

@date AS date

AS

SET NOCOUNT ON;

DECLARE @query nvarchar(4000);

SET @query = 'SELECT * FROM ' + QUOTENAME(@tablename) +

...

No, except in perhaps extraordinarily rare cases.

As a secondary question, will...

DECLARE @MyVari varchar(20)

SELECT [ProductID]

,[prod].[Name]

,[ProductNumber]

,[MakeFlag]

,[FinishedGoodsFlag]

,[MyRow] = @MyVari

FROM [AdventureWorks2014].[Production].[Product] prod

INNER JOIN [Production].[ProductModel] Prodmod

ON prod.Name = Prodmod.name

WHERE @MyVari = Case When [Production].[Product].[MakeFlag] = 0

...

...

where o.person_id not in (select distinct person_id from #wch_have where person_id is not null )

...

Ed Wagner (6/5/2015)

---

You...

If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.

You can't avoid blocking, and you don't want to. It is an integral part of SQL's engine to maintain data integrity.