SQLTeam.com | Weblogs | Forums

I am going mad


#1

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.


#2

Welcome :slightly_smiling:
You probably need to change this:

to this:

WHERE VEND.VendorName LIKE '%THE%'

#3

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.


#4

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.


#5

If this works:

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


#6

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.


#7

Try this:

WHERE VEND.VendorName LIKE 'The%'

#8

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!


#9

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