SQLTeam.com | Weblogs | Forums

I am going mad

Hi Chaps,

Just registering as I am starting to get into TSQL, hoping I can learn and help out when I get some more experience :slightly_smiling:

I am creating a simple procedure for practice, but I just can't work out why it is not working and was hoping someone may be able to assist?

The query is as follows:

CREATE PROCEDURE dbo.AmountOwed
@VendorName Varchar(20) AS
SELECT
VEND.VendorName,
VEND.VendorID,
INV.InvoiceID AS 'Invoice Number',
INV.InvoiceDate,
InvoiceTotal - PaymentTotal AS 'Amount Owed'
FROM dbo.Vendors VEND
JOIN dbo.Invoices INV ON VEND.VendorID = INV.VendorID
WHERE VEND.VendorName LIKE '%'+@VendorName+'%'

I execute it by just entering the standard EXEC (procedurename) 'the'

No results.

I'll run a basic query like so:

SELECT * FROM dbo.Vendors
WHERE VendorName like '%THE%'

Of course it works. So I try my select query:

SELECT
		VEND.VendorName,
		VEND.VendorID,
		INV.InvoiceID AS 'Invoice Number',
		INV.InvoiceDate,
		InvoiceTotal - PaymentTotal AS 'Amount Owed'
FROM dbo.Vendors VEND
JOIN dbo.Invoices INV ON VEND.VendorID = INV.VendorID
WHERE VEND.VendorName LIKE 'THE'

No results?

I'm clearly missing something very basic, and I am either very stupid or blind, as I can't figure out why that query isn't working.

I'm selecting the relevant fields, joining the two tables on PK / FK and it's just not working.

I would think it was a problem with the two ID's not matching on the tables, but it should do.

Any advice on this would be appreciated, it's driving me nuts.

Many thanks.

Welcome :slightly_smiling:
You probably need to change this:

to this:

WHERE VEND.VendorName LIKE '%THE%'

Hi!

Thanks for the welcome :slightly_smiling:

Yes sorry I am an idiot, have tried that but still no results.

I think everything looks fine with the SP, just can't figure out why it's not returning any results still.

I'm going to check the join I think and see if the ID's matches.

The join appears to be fine, the ID data matches and returns results:

SELECT * FROM dbo.Vendors VEND
JOIN dbo.INVOICES INV on VEND.VendorID = INV.VendorID

I seriously am going crazy, must be something so simple, I just don't get it.

If this works:

then we must conclude, there is no vendorname containing "THE".
Please show samples of vendornames you'd expect.

Hi,

Thanks again for the reply.

That individually without the join or filtering the colums of data on the select statement does work:

The Library Ltd
The Windows Deck
The Mailers Guide Co
The Drawing Board
The Fresno Bee
The Presort Center

And their corresponding ID's in both tables match:

21
57
60
65
71
78

So the join is ok, the data I am asking for in the WHERE clause is there, but there are no results. It must be something quirky with the way I have written the query.

Try this:

WHERE VEND.VendorName LIKE 'The%'

Your procedure is trying to create a dynamic query, therefore you need to first create your query into a string and then execute

CREATE PROCEDURE dbo.AmountOwed
@VendorName Varchar(20) AS
as
set nocount on;
declare @sql as nvarchar(500)

set @sql = 'SELECT VEND.VendorName, VEND.VendorID, INV.InvoiceID AS ''Invoice Number'',
INV.InvoiceDate, InvoiceTotal - PaymentTotal AS ''Amount Owed'' FROM dbo.Vendors VEND
JOIN dbo.Invoices INV ON VEND.VendorID = INV.VendorID
WHERE VEND.VendorName LIKE ''%''+@VendorName+''%'''

exec (@sql)

hope that helps!

You mention,

How about this ?

SELECT VEND.VendorName
	,VEND.VendorID
	,INV.InvoiceID AS 'Invoice Number'
	,INV.InvoiceDate
	,InvoiceTotal - PaymentTotal AS 'Amount Owed'
FROM dbo.Vendors VEND
INNER JOIN dbo.Invoices INV ON VEND.VendorID = INV.VendorID
WHERE VEND.VendorName LIKE '%THE%'

Or try without the WHERE clause