Problema Con FillSchema y SP que se arma mediante Strings.

Hola a Todos:

Ante que nada quiero desearles un Feliz Año 2005 y que todos sus
propositos para este año se cumplan, sinceramente todo lo mejor para
cada uno de los suyos.

Bueno, tengo el siguiente problema.

Tengo un SP. que en el query analyzer funciona correctamente, pero
cuando los uso en mi programa mediante un SqlDataAdapter, me marca el
siguiente error: Incorrect syntax near the keyword 'LEFT'.


Sinceramente agradezco de antemano su ayuda, GRACIAS.

Este es mi codigo en VB.Net:

objDAdap.FillSchema(objDSet, SchemaType.Mapped, "DataBrowse")

donde: ObjDAdap, es mi SqlDataAdapter.
ObjDSet, es mi DataSet.
Hasta este momento ya se ha establecido la conexion a la B.D
El dataAdapter ya tiene ligado la conexion.

Y este es el SP:


CREATE PROCEDURE spGetPeopleByDefaultVw(
@iSearchGroupID INTEGER,
@sSearchString VARCHAR(60),
@sSearchZipCode VARCHAR(15),
@sOneP CHAR(1)
)
AS
BEGIN
SET QUOTED_IDENTIFIER OFF
SET CONCAT_NULL_YIELDS_NULL OFF


DECLARE @sSql as varchar(2000)
DECLARE @sSqlInit as varchar(2000)
DECLARE @sSqlLast as varchar(2000)


DECLARE @sCond VARCHAR(30)
DECLARE @sKindCond INTEGER
DECLARE @sEmail VARCHAR(80)
DECLARE @sEmp VARCHAR(80)
DECLARE @sContrac VARCHAR(80)
DECLARE @sClient VARCHAR(80)
DECLARE @sRoyal VARCHAR(80)
DECLARE @sPeople VARCHAR(80)
DECLARE @sPhone VARCHAR(150)
DECLARE @sOrder VARCHAR(150)


SET @sKindCond = 0
SET @sSql = ''
SET @sSqlLast = ''
SET @sEmail = ''
SET @sEmp = ''
SET @sContrac = ''
SET @sClient = ''
SET @sRoyal = ''
SET @sPeople = ''
SET @sPhone = ''
set @sOrder = ''


/*
e9999999 = Search by Employee number
c9999999 = Search by Contract Number or
d9999999 = client id, the routine will know
a9999999 = Search by Royal Resorts Points Account
p9999999 = People ID
!!!!!@!!!.com Search by Email Address
t999999999 = Search by Phone Number
r99999999 = Reservation
v99999999 = InvitationCOde
otherwise = Search by Last Name

*/

SET @sSearchString = dbo.fnTrim(@sSearchString)

ALL THE RECORDS
IF (@sOneP = 0)
BEGIN
SET @sSqlInit = 'Select DISTINCT p.pkPeopleID ,'+
' dbo.fnGetNameFormated( p.lName1 ,p.lName2 , p.fName ,p.mName , 1 )
as Name,' +
' a.ZipCode, a.City ,' + ' s.Description as StateDesc , ' + '
c.Description as CountryDesc '

SET @sSqlLast = ' FROM tblPeople p WITH (NOLOCK) ' +
'left join tblPeopleAddress pa on (p.pkPeopleID=pa.fkPeopleID
and pa.PrimaryAddress = 1 ) ' +
'left join tblAddress a on
(pa.fkAddressID=a.pkAddressID) ' +
'left join tblState s on (a.fkStateID = s.pkStateID) ' +
'left join tblCountry c on (a.fkCountryID =
c.pkCountryID) '
END
ELSE
BEGIN
SET @sSqlInit = 'Select p.pkPeopleID ,'+
' dbo.fnGetNameFormated( p.lName1 ,p.lName2 , p.fName ,p.mName , 1)
as Name,' +
' a.ZipCode, a.City ,' + ' s.Description as StateDesc , ' + '
c.Description as CountryDesc '


SET @sSqlLast = ' FROM tblPeople p WITH (NOLOCK) ' +
'left join tblPeopleAddress pa on (p.pkPeopleID=pa.fkPeopleID
) ' +
'left join tblAddress a on
(pa.fkAddressID=a.pkAddressID) ' +
'left join tblState s on (a.fkStateID = s.pkStateID) ' +
'left join tblCountry c on (a.fkCountryID =
c.pkCountryID) '
SET @sOrder = ' ORDER BY p.pkPeopleID , (p.lName1 + p.lName2 + '' ,
'' + p.fName + p.mName)'
END


IF (CHARINDEX ('@', @sSearchString) > 0)
BEGIN
SET @sKindCond = 1
SET @sSqlInit = @sSqlInit + ' , d.Description as Email '
SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblPeopleEmail b ON
b.fkPeopleId = p.pkPeopleID '+
' LEFT JOIN tblEmail d ON b.fkEmailID = d.pkEmailId '
SET @sEmail = ' AND d.Description LIKE ' + '''' + @sSearchString +
'%'+ ''''
GOTO EXECSQL
END


IF ( SUBSTRING (@sSearchString,1,1) = 'e') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 2
SET @sSqlInit = @sSqlInit + ' , e.efkEmpID , e.EmpInitial '
SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblEmp e on
(e.fkPeopleID=p.pkPeopleID) '
SET @sEmp = ' AND e.efkEmpID = ' + '''' + SUBSTRING(@sSearchString
,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END


IF ( SUBSTRING (@sSearchString,1,1) = 'c') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 3
SET @sSqlInit = @sSqlInit + ' ,h.ContractNo '
SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblTitlePeople f ON
f.fkPeopleId = p.pkPeopleId ' +
' LEFT JOIN tblTitle h ON f.fkTitleId = h.pkTitleId '
SET @sContrac = ' AND h.ContractNo = ' + '''' +
SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END

IF ( SUBSTRING (@sSearchString,1,1) = 'd') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 4
SET @sSqlInit = @sSqlInit + ' ,p.sfkClientId '
SET @sClient = ' AND p.sfkClientId LIKE ' + '''' +
SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END


IF ( SUBSTRING (@sSearchString,1,1) = 'a') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 5
SET @sSqlInit = @sSqlInit + ' ,i.RwdAccCode as RoyalCode '
SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblRwdAcc i ON I.fkPeopleID
= p.pkPeopleID '
SET @sRoyal= ' AND i.RwdAccCode LIKE ' + '''' +
SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END

IF ( SUBSTRING (@sSearchString,1,1) = 'p') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 6
SET @sPeople = ' AND p.pkPeopleId LIKE ' + '''' +
SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END

IF ( SUBSTRING (@sSearchString,1,1) = 't') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 7
SET @sSqlInit = @sSqlInit + ' , ( DBO.fnMskPhone (
dbo.fnTrim(l.CountryCode) , dbo.fnTrim(l.AreaCode) ,
dbo.fnTrim(l.PhoneNo), dbo.fnTrim(l.ExtNo) ) ) as Telephone ,
m.Description as PhoneType '
SET @sSqlLast = @sSqlLast + ' INNER JOIN tblPeopleTelephone k ON
k.fkPeopleId = p.pkPeopleId ' +
' INNER JOIN tblTelephone l ON k.fkTelephoneID =
l.pkTelephoneID ' +
' INNER JOIN tblTelephoneType m ON l.fkTelephoneTypeId
= m.pkTelephoneTypeId '

SET @sPhone = ' AND ( dbo.fnTrim(l.CountryCode) +
dbo.fnTrim(l.AreaCode) + dbo.fnTrim(l.PhoneNo) + dbo.fnTrim(l.ExtNo) ) =
' + '''' + SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END


IF ( SUBSTRING (@sSearchString,1,1) = 'r') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 8
SET @sSqlInit = @sSqlInit + ' ,r.pkUnitRsvID , rc.fkOccCode '
SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblUnitRsvGst o ON
o.fkPeopleId = p.pkPeopleId ' +
' LEFT JOIN tblUnitRsv r ON o.fkUnitRsvId =
r.pkUnitRsvId ' +
' LEFT JOIN tblUnitRsvCode rc ON rc.fkUnitRsvID = r.pkUnitRsvID '
SET @sPhone = ' AND r.pkUnitRsvId = ' + '''' +
SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END

IF ( SUBSTRING (@sSearchString,1,1) = 'v') AND (
ISNUMERIC(SUBSTRING(@sSearchString,2,LEN(@sSearchString))) = 1 )
BEGIN
SET @sKindCond = 9
SET @sSqlInit = @sSqlInit + ' , iv.InvitationCode ,
iv.ContactCode '
SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblLeadPeople ld ON
ld.fkPeopleID = p.pkPeopleId ' +
' LEFT JOIN tblInvitation iv ON iv.fkLeadID =
ld.fkLeadID '
SET @sPhone = ' AND iv.InvitationCode = ' + '''' +
SUBSTRING(@sSearchString ,2,LEN(@sSearchString)) + ''''
GOTO EXECSQL
END



EXECSQL:
SET @sSqlLast= @sSqlLast +' WHERE (1 = 1) '
IF (@iSearchGroupID > 0)
BEGIN

SET @sSqlLast = @sSqlLast + ' LEFT JOIN tblPeopleGroup pg on
(pg.fkPeopleID=p.pkPeopleID) ' +
' LEFT JOIN tblGroup g on (g.pkGroupID=pg.fkGroupID) '
SET @sSqlLast = @sSqlLast + ' AND g.pkGroupID=' + '''' +
RTRIM(CAST(@iSearchGroupID as char(3))) + ''''
END--if (@iSearchGroupID > 0)

IF (@sSearchZipCode IS NOT NULL) AND ( LEN(@sSearchZipCode) > 0)-- If
zip code has a value
BEGIN
SET @sSearchZipCode = dbo.fnTrim(@sSearchZipCode)
IF LEN(@sSearchZipCode) > 0
BEGIN
SET @sSqlLast = @sSqlLast + ' AND a.ZipCode LIKE ' + '''' +
@sSearchZipCode + '%' + ''''
END
END--if (@sSearchZipCode is not null)


IF (@sSearchString IS NOT NULL ) AND (@sKindCond = 0)
BEGIN
SET @sSqlLast = @sSqlLast + ' AND p.Lname1 LIKE ' + ''''+
@sSearchString + '%'+ ''''
END



SET @sSql = @sSqlInit + @sSqlLast + @sEmp + @sEmail+ @sContrac +
@sClient + @sRoyal + @sPeople + @sPhone + @sOrder
EXECUTE (@sSQL)


SET QUOTED_IDENTIFIER ON
SET CONCAT_NULL_YIELDS_NULL ON


END