CODEWITHSUNDEEP
sqlsql-servert-sql
Devin Goble
Devin Goble

Reputation: 2857

View, Stored Procedure, or Table Defined Function

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.

Upvotes: 3

Views: 488

Answers (7)

Andriy M
Andriy M

Reputation: 77677

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)

Upvotes: 0

gbn
gbn

Reputation: 432271

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

Upvotes: 5

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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.

Upvotes: 2

Matt
Matt

Reputation: 2098

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.

Upvotes: 0

Ken Downs
Ken Downs

Reputation: 4827

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.

Upvotes: 0

Matthew
Matthew

Reputation: 10444

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

Upvotes: 0

Related Questions