Sql-server – T-SQL for Query that can be done in SQL. What do they want

sql servert-sql

We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId…)
I am going over a book exercise that asks for a T-SQL statement to find
all vendors with at least one invoice. But I am confused, since this can be perfectly done with standard SQL:

SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
GROUP BY VendorName having Count(InvoiceId) > = 1

So, what could they be asking? Should I come up with a procedure to select all vendors with more than n invoices; n= 0,1,2 ?

If so, why is this not working for the general case of n invoices:

 CREATE Procedure InvoiceCount @NumberofInvoices Int
AS 
BEGIN
SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
WHERE Count(InvoiceId) > = @Number of Invoices
EXEC InvoiceCount @Number of Invoices = n

Why doesn't this work?
Thank you.

Best Answer

You don't need the WHERE clause because you are doing an inner join, which ensures you will only get vendors that have an invoice:

SELECT DISTINCT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID

SELECT DISTINCT VendorName 
FROM Vendors V 
WHERE EXISTS (SELECT 1 FROM Invoices I WHERE V.VendorId=I.VendorID)

Related Solutions

Sql-server – What can be done, if anything, to tune a SharePoint database

There is not a lot you can do to tune the database it self. You aren't allowed to create indexes, or statistics.

Keep your content databases small. If they get to 100 Gigs you'll want to add another content database (there's ways to do this through the SharePoint UI).

As the tables grow you will want to be sure that you are doing index rebuilds and updating stats regularly.

You can look at using the EBS feature if you are using SharePoint 2010 (might be in SharePoint 2007 as well) which will allow you to move the blob data out of the database and store it on the file system using SQL Server's FILESTREAM feature.

Short of that there isn't much you can do besides throwing more hardware at the problem.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – What can be done, if anything, to tune a SharePoint database

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question