View, stored procedure or function defined by table

Question

This query is executed simply as is. However, SQL Management Studio will not save it as a view because I am defining 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 didn't actually play with stored procedures or user-defined functions, so I'm not sure how best to do this. Or, if there is a better way to write this, I am also open to suggestions.

Answer 1

• You can highlight MAX in CTE
• MAX without GROUP BY gives one row, so you use ISNULL there

Sort of...

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 is not required: it mixes sets and scalars. Unfortunately.

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)

Answer 2

Since you mentioned that you were trying to create this as a view, this means that you can use the results in another query. If so, I would make it a table function .

Answer 3

. . , , .

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

Answer 4

w/COALESCE, :

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

.

Answer 5

. .

msdn .

Answer 6

, CTE. ISNULL SELECT MAX , ISNULL .

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))

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))

Answer 7

Just CROSS , @HighestTansaction , :

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)