January 3, 2003 at 3:50 pm
I keep getting invalid column name error on this dynamic sql statement. I am sure i have the apostrophes screwed up somehow.
here is the sp:
CREATE PROCEDURE SearchContacts
(
@ContactID int,
@Email varchar(100),
@FirstName varchar(25),
@LastName varchar(25),
@City varchar(100),
@State varchar(3),
@Zip varchar(10)
)
as
Declare @SQL VarChar(2000)
SET @SQL = 'SELECT * FROM Contacts '
SET @SQL = @SQL + ' left outer join ContactAddresses on Contacts.ContactId = ContactAddresses.ContactID'
SET @SQL = @SQL + ' left outer join ContactEmail on Contacts.ContactId = ContactEmail.ContactID '
IF @ContactID IS NOT NULL OR
@Email IS NOT NULL OR
@FirstName IS NOT NULL OR
@LastName IS NOT NULL OR
@City IS NOT NULL OR
@State IS NOT NULL OR
@Zip IS NOT NULL
SET @SQL = @SQL + ' WHERE '
IF @ContactID <> ' '
SET @SQL = @SQL + 'Contacts.ContactID = ' + @ContactID
else
SET @SQL = @SQL + 'Contacts.ContactID = Contacts.ContactID'
IF @Email <> ' '
SET @SQL = @SQL + ' or Contacts.EmailAddress = ' @Email
IF @FirstName <> ' '
SET @SQL = @SQL + ' or Contacts.FirstName =' @FirstName
IF @LastName <> ' '
SET @SQL = @SQL + ' or Contacts.LastName = ' @LastName
IF @City <> ' '
SET @SQL = @SQL + ' or Contacts.City = ' + @City
IF @State <> ' '
SET @SQL = @SQL + ' or Contacts.State = ' + @State
IF @Zip <> ' '
begin
SET @SQL = @SQL + ' or Contacts.Zip = ' + @Zip
end
Exec(@SQL)
GO
and one other question, this does not seem to work. can you not use % in a Coalesce function?
COALESCE('%' + @FirstName + '%',Contacts.FirstName) </cm>
</cm>
January 3, 2003 at 4:11 pm
Try commenting out the
Exec(@SQL)
and adding the line
PRINT @SQL
this will give you the SQL syntax of how the server sees it, it is great for troubleshooting, and you can return to your sproc and make the proper edits
-Francisco
-Francisco
January 3, 2003 at 5:17 pm
Well, one thing that leaps out is that you seem to be missing a few '+' signs in your SET statements (right before @Email, @FirstName, and @LastName).
Also, you test your input variables against NULL early in the procedure, but later test them against '' -- be aware that
IF NULL <> ''
evaluates to true. You should stick with IS NOT NULL for your test if you're working with potentially null values.
-Lam
January 3, 2003 at 5:28 pm
Um, disregard my second comment. Apparently, I'd set ANSI_NULLS to OFF when I tested it...
January 5, 2003 at 6:21 am
The values being passed as parameters are literals, yet as you construct your where clause their value will be assumed to be another field name for character strings when the EXEC() occurs.
For those parameters that are varchar you need to quote the value.
try changing the lines like:
SET @SQL = @SQL + ' or Contacts.EmailAddress = ' @Email
to:
SET @SQL = @SQL + ' or Contacts.EmailAddress = ' + '''' + @Email + ''''
For example, say @Email contains : myemail@somewher.com
the first string will contain:
[or Contacts.EmailAddress = myemail@somewher.com]
The second exmaple will contain:
[or Contacts.EmailAddress = 'myemail@somewher.com']
This puts quotes around the literal values, so the value passed to EXEC should now evaluate correctly.
Coalesce.
Your question on coalesce - what are you trying to do?
Coalesce is functionally equilavent to isnull() but supports multiple parameters.
January 7, 2003 at 6:20 am
Thanks for the replies. COALESCE('%' + @FirstName + '%',Contacts.FirstName) I am simply trying to add a a wildcard filter on to the parameter but it does not seem to work in this function.
</cm>
January 7, 2003 at 6:27 am
FOrget it. I was caught a stupid mistake on my end. Thanks again.
</cm>
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply