I get an error when I call my stored procedure 6

[ClaytonQ]

I'm having a tough time with this one. I've spent days trying to figure it out.

The stored procedure included below parses out and the procedure is created when I play it. But when I run it, I get this error message:

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 't'.

I can't figure out what this error message means. By my count, line 7 is a blank line. I don't know what 't' it's referring to.

CREATE PROCEDURE usp_AllMailingEntries

@EmpID int = null,
@DateEntered varchar(20) = '%'

AS

Declare @SQL     varchar(500),
	@WHERE	 varchar(500)

If @EmpID Is Null
	Set @WHERE = ' WHERE bitIsActive = 1 AND DateEntered LIKE ' + @DateEntered
Else
	Set @WHERE = ' WHERE bitIsActive = 1 AND DateEntered LIKE ' + @DateEntered + ' AND EmpID = ' + @EmpID


Set @SQL = 'SELECT
		FirstName, LastName, Address1, Address2, 
		City, StateID, StateAbbrev, Province, 
		Zip, ZipPlus4, Country, Phone,
                EmailAddress, HeardAboutID, EmpID, 
                DateEntered, Comments
	FROM People
	Left Join States ON People.StateID = States.StateID' + @WHERE

Exec(@SQL)

 

[JayKusch]

I am getting it to bomb out on line 8 ...

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '%'.

Looks like you are trying to use a wild card in a date lookup. It can be done but not the way you are going at it.

Just saw a post a few days ago that was using the wild card operator in it. Darn if I can find it though!!!

Can someone direct us to that thread ????

Thanks

J. Kusch

 

[ClaireHCM]

Try to alter the proc as following

alter PROCEDURE usp_AllMailingEntries @EmpID int = null,@DateEntered varchar(20) = '%'
AS

set quoted_identifier off
Declare @SQL varchar(500), @WHERE varchar(500)

If @EmpID Is Null
Set @WHERE = ' WHERE bitIsActive = 1 AND DateEntered LIKE "' + @DateEntered+'"'
Else
Set @WHERE = ' WHERE bitIsActive = 1 AND DateEntered LIKE "' + @DateEntered + '" AND EmpID = ' + @EmpID


Set @SQL = 'SELECT
FirstName, LastName, Address1, Address2,
City, StateID, StateAbbrev, Province,
Zip, ZipPlus4, Country, Phone,
EmailAddress, HeardAboutID, EmpID,
DateEntered, Comments
FROM People
Left Join States ON People.StateID = States.StateID' + @WHERE

exec (@SQL)

 

[ClaytonQ]

Thank you both.
JayKusch- The datatype of my dateEntered field should be a date datatype, but it isn't. I looked it up, and it's an nvarchar.
ClaireHSU- When I tried your idea, the parser got hung. It was wierd. I gave it a minute and then stopped it.
I'm using SQL 7, BTW.

Thanks again,

Clay

 

[SQLBill]

Any time you get an error message in Query Analyzer, double click on the error. It will highlight the line in the top pane.

-SQLBill

 

[ClaytonQ]

Wow, SQLBill, that is really cool and useful.

When I did that just now, it confirmed that the problem is indeed in the blank line. This makes no sense, of course. Is it haunted?

 

[SQLBill]

Delete the blank lines for now or else put two dashes (--) in front of the blank lines: (two dashes identify the line as a comment)

AS
--
IF ......

There might be a hidden character. Also, check your scroll bar and see if you can scroll over to the right. The line might not actually be blank.

-SQLBill

 

[ClaytonQ]

Thanks SQLBill.

The mystery deepens. Now that I've deleted all the blank lines in the code, it still says there's an error in line 7 near the 't', even though line 7 is a completely different line of code than the line formerly known as line 7. It's adamant that line 7 is the problem, regardless of what line 7 says. Wierd.

 

[SQLSister]

Ok here's one way to find the problem. Comment out all the code. Then put each commmand back in one at a time until you get the error.

 

[ClaytonQ]

That was helpful, SQLSister. Thank you. When I went through and commented out everything, everything was fine until I got all the way to the last line that says

Exec(@SQL)

. Of course that's the line that makes it execute the string I put together.

 

[ClaytonQ]

Mystery Solved!

And boy, do I feel dumb. After I realized (thanks to SQLSister) that the problem was in the line

Exec(@SQL)

, I changed the line to

print(@SQL)

and ran it. By doing that I discovered that my string I had concatenated together was over 500 characters, and so it was getting truncated. I changed @SQL to varchar(1000), and it worked perfectly.

Thank you all for your help. I'd been beating my head against the wall for hours before you helped me.

By the way, I'm new to T-SQL and I didn't know there was a Print command. I just guessed.

Also, I'd like to officially opine that the error messages SQL Server returns are misleading and unhelpful. Blech.

 

[ClaytonQ]

I'd also like to add that I also implemented the changes suggested by ClaireHSU concerning the quoted_identifier, and without those changes the code didn't work. Her changes were essential to solving the problem.

 

[JamesLean]

ClaytonQ,
Glad that you got it sorted. However, just as a little FYI, you should try and avoid dynamic SQL wherever possible. I would rewrite your SP like this:

CREATE PROCEDURE usp_AllMailingEntries
  @EmpID int = NULL,
  @DateEntered varchar(20) = '%'
AS

SELECT FirstName, LastName, Address1, Address2, City, StateID, StateAbbrev, Province, Zip, ZipPlus4, Country, Phone, EmailAddress, HeardAboutID, EmpID, DateEntered, Comments
FROM People p LEFT JOIN States s ON p.StateID = s.StateID
WHERE bitIsActive = 1
  AND DateEntered LIKE @DateEntered
  AND (@EmpID IS NULL OR EmpID = @EmpID)

--James

 

[ClaytonQ]

I like that much better, JamesLean. When I was using the EXEC statement, it was re-checking security credentials. Therefore I had to give people permissions not only on the stored procedure, but also on the underlying table. That made me uncomfortable, security-wise. And this is simply easier to manage.
I would have never figured this out on my own because checking in the WHERE statement whether @EmpID IS NULL seems counter-intuitive to me. Checking a column against an input parameter for each row seems normal, but checking an input parameter against null for each row seems... I don't know. It just looks like one should only have to check that once, and not for each row. I guess I've been thinking about the SELECT statement wrong.
Thanks for your help getting my thinking out of that box.