View, Stored Procedure, or Table Defined Function

Question

This query executes just fine as-is. However, SQL Management Studio won't save it as a view because I define a variable.

DECLARE @HighestTransaction int

SET @HighestTransaction = (SELECT     MAX(CardID)
                            FROM          dbo.Transactions)

SELECT Uploads.*, Transactions.*
FROM   Uploads LEFT OUTER JOIN
       dbo.Transactions ON dbo.Uploads.Code = dbo.Transactions.CardID
WHERE  (Uploads.Code > CASE WHEN
           @HighestTransaction IS NULL THEN -1  ELSE @HighestTransaction END)

I haven't really played around with stored procedures, or user defined functions a whole lot, so I'm not sure of the best way to go about doing this. Or, if there's a better way to write this, I'm open to suggestions as well.

And specify the fields you want in the select. Select * is a particularly poor coding practice and since you have a join you would be returning one field twice which is a waste of resources. — HLGEM, Jan 26 '11 at 20:39
Does SELECT * present a problem if one of the tables has a few number of columns, the other is a very specific view, and all of the resulting columns will be used by the caller? Anyway, I wrote SELECT * to save space and not distract from the other stuff. The real query does expand all of the columns, because that's what VS and SQL Management Studio both do automatically when you put in SELECT *. — Devin Goble, Jan 26 '11 at 22:56

gbn · Accepted Answer · 2011-01-27T11:07:48.210

5

You can isolate the MAX into a CTE
MAX with no GROUP BY gives one row, so you use ISNULL there

Something like...

WITh cHighestCard AS
(
   SELECT ISNULL(MAX(CardID), -1) AS MaxCard FROM dbo.Transactions
)
SELECT     STAUpload.*, Transactions.*
FROM         dbo.STAUpload LEFT OUTER JOIN
                      dbo.Transactions ON dbo.STAUpload.Code = dbo.Transactions.CardID
WHERE     dbo.STAUpload.Code > MaxCard --edit, error spotted by martin

Edit: CTE not needed: it mixes sets and scalars. Oops.

SELECT     STAUpload.*, Transactions.*
FROM         dbo.STAUpload LEFT OUTER JOIN
                      dbo.Transactions ON dbo.STAUpload.Code = dbo.Transactions.CardID
WHERE     dbo.STAUpload.Code >
            (SELECT ISNULL(MAX(CardID), -1) AS MaxCard
                   FROM dbo.Transactions)

edited Jan 27 '11 at 11:07

answered Jan 26 '11 at 20:23

gbn

422,506
82
585
676

Would the CTE return -1 if there are no records at all in the TRANSACTIONS table? – OMG Ponies Jan 26 '11 at 20:43
1

@OMG Ponies: yes `CREATE TABLE OMG (Ponies int); SELECT MAX(Ponies) FROM OMG` – gbn Jan 26 '11 at 20:46
@OMG Ponies: Shameless plug... http://stackoverflow.com/questions/2552086/does-count-always-return-a-result/2552102#2552102 – gbn Jan 26 '11 at 21:13
As the above SQL is written, I get errors about not being able to find MaxCard, even if I add cHighestCard.MaxCard to the column list of the second SELECT. – Devin Goble Jan 26 '11 at 23:44

score 2 · Answer 2 · answered Jan 26 '11 at 20:24

2

Since you mentioned that you tried to create this as a view, that implies you might want to use the results within another query. If that is true, I'd make this a table-valued function.

answered Jan 26 '11 at 20:24

Joe Stefanelli

132,803
19
237
235

I can't seem to get a table-valued function to work without at least one parameter. I really don't want any at all. Can that be done, or should I just define a blank on and set the default? – Devin Goble Jan 26 '11 at 20:37
It would have to be a multi statement TVF which is a black box to the optimiser http://stackoverflow.com/questions/510743/why-is-udf-so-much-slower-than-subquery/510754#510754 – gbn Jan 26 '11 at 20:40

score 0 · Answer 3 · answered Jan 26 '11 at 20:19

0

That is a Stored Procedure. It's easy as pie to use. Just tell the management studio you want to make a new procedure and it will even build all the basic framework syntax for you.

http://msdn.microsoft.com/en-us/library/ms345415.aspx

answered Jan 26 '11 at 20:19

Matthew

10,244
5
49
104

score 0 · Answer 4 · answered Jan 26 '11 at 20:20

0

You can recode it as a subquery w/COALESCE to avoid double-typing it:

SELECT ....
  FROM ....
 WHERE dbo.STAUpload.code > COALESCE( 
       (Select max(cardId) from dbo.transactions),-1)

Then it becomes a view.

answered Jan 26 '11 at 20:20

Ken Downs

4,707
1
22
20

This will still execute it twice. (http://connect.microsoft.com/SQLServer/feedback/details/336002/unnecessarily-bad-performance-for-coalesce-subquery) – Martin Smith Jan 26 '11 at 20:22
@Martin, @Ken Downs: I'd consider ISNULL then. If cardID is smallint, say, then .code may be cast to int because of COALESCE datatype handling (optimiser may treat -1 as smallint though) – gbn Jan 26 '11 at 20:30

score 0 · Answer 5 · answered Jan 26 '11 at 20:20

0

You can do this in a stored procedure. Just pass your variable in and it will work as desired.

Do a quick search and review stored procedures on msdn or books online.

answered Jan 26 '11 at 20:20

Matt

2,078
2
27
40

score 0 · Answer 6 · answered Jan 26 '11 at 21:54

This could be just one simple view, works without using CTE. The ISNULL against the SELECT MAX in a subquery could be easier to interpret that in-lining the ISNULL into the subquery.

CREATE VIEW Q_SO
AS
SELECT Uploads.*, Transactions.*
FROM Uploads
LEFT OUTER JOIN dbo.Transactions
    ON dbo.Uploads.Code = dbo.Transactions.CardID
WHERE (Uploads.Code > ISNULL((SELECT MAX(CardID) FROM dbo.Transactions),-1))

As a parameterless Table Valued Function

CREATE FUNCTION Q_FN() RETURNS TABLE AS RETURN
SELECT Uploads.*, Transactions.*
FROM Uploads
LEFT OUTER JOIN dbo.Transactions
    ON dbo.Uploads.Code = dbo.Transactions.CardID
WHERE (Uploads.Code > ISNULL((SELECT MAX(CardID) FROM dbo.Transactions),-1))

Andriy M · Answer 7 · 2011-02-01T22:00:33.427

0

Just CROSS JOIN the subquery that initialises @HighestTansaction to your main query, like this:

SELECT Uploads.*, Transactions.*
FROM   Uploads LEFT OUTER JOIN
       dbo.Transactions ON dbo.Uploads.Code = dbo.Transactions.CardID CROSS JOIN
       (SELECT ISNULL(MAX(CardID),-1) FROM dbo.Transactions) m(HighestTransaction)
WHERE  (Uploads.Code > m.HighestTransaction)

edited Feb 01 '11 at 22:00

answered Jan 27 '11 at 10:13

Andriy M

76,112
17
94
154

View, Stored Procedure, or Table Defined Function

7 Answers7