Dynamic SQL issue

While I realize there are certain inherent pitfalls with Dynamic SQL, I'm
curious what is causing this so I can recognize it in the future. Anyway,
we have a stored procedure. The stored procedure concatenates a string that
is ultimately executed via EXEC(). The stored procedure is called from PHP
and is returning different results than what I'm seeing when I run it in
query analyzer. For some reason I'm getting a leading comma in PHP that I'm
not seeing in Query Analyzer in the "City_State" field. Now, if I change
City_State to be hardcoded to something for sake of example, like City_State
= ''something'', the comma goes away. But it doesn't make any sense that a
UDF nested in a Stored Proc would somehow return a different value when it's
called via PHP as opposed to Query Analyzer. ...does it? Thanks a billion.
set @.x = 'SELECT DISTINCT TOP 100 PERCENT
List.List_ID,
City_State = dbo.udf_AddressCityStateZip('''', List.List_City,
List.List_State, SUBSTRING(List.List_Zip, 1, 5)),
List.List_Latitude, List.List_Longitude
WHERE List_ID = 5'
EXEC (@.x)It could be how you are pulling your results out of the resultset, rather
than the stored procedure. That is, unless PHP is passing in a value for
'address' that is a single space, or something other than what you expect.
Also, where is the "FROM" clause, or is this not all the code we would need
to help out?
> While I realize there are certain inherent pitfalls with Dynamic SQL,
> I'm
> curious what is causing this so I can recognize it in the future.
> Anyway,
> we have a stored procedure. The stored procedure concatenates a
> string that
> is ultimately executed via EXEC(). The stored procedure is called
> from PHP
> and is returning different results than what I'm seeing when I run it
> in
> query analyzer. For some reason I'm getting a leading comma in PHP
> that I'm
> not seeing in Query Analyzer in the "City_State" field. Now, if I
> change
> City_State to be hardcoded to something for sake of example, like
> City_State
> = ''something'', the comma goes away. But it doesn't make any sense
> that a
> UDF nested in a Stored Proc would somehow return a different value
> when it's
> called via PHP as opposed to Query Analyzer. ...does it? Thanks a
> billion.
> set @.x = 'SELECT DISTINCT TOP 100 PERCENT
> List.List_ID,
> City_State = dbo.udf_AddressCityStateZip('''', List.List_City,
> List.List_State, SUBSTRING(List.List_Zip, 1, 5)),
> List.List_Latitude, List.List_Longitude
> WHERE List_ID = 5'
> EXEC (@.x)
>|||I had considered that, but they use the same process pretty much everywhere
and haven't had this problem before. That isn't to say it's not the
problem. Sorry for missing the FROM clause, I had just trimmed it to make
it more readable (parts that were 100% not the issue). Standard FROM before
the WHERE...FROM Tbl_List AS List I believe. When I run it in .NET and use
the results to populate a DataSet, I don't get the leading comma either.
"Andrew Backer" <awbacker@.gmail.com> wrote in message
news:1e2aa300c5b48c9bd137b610158@.news.microsoft.com...
> It could be how you are pulling your results out of the resultset, rather
> than the stored procedure. That is, unless PHP is passing in a value for
> 'address' that is a single space, or something other than what you expect.
> Also, where is the "FROM" clause, or is this not all the code we would
> need to help out?
>
>> While I realize there are certain inherent pitfalls with Dynamic SQL,
>> I'm
>> curious what is causing this so I can recognize it in the future.
>> Anyway,
>> we have a stored procedure. The stored procedure concatenates a
>> string that
>> is ultimately executed via EXEC(). The stored procedure is called
>> from PHP
>> and is returning different results than what I'm seeing when I run it
>> in
>> query analyzer. For some reason I'm getting a leading comma in PHP
>> that I'm
>> not seeing in Query Analyzer in the "City_State" field. Now, if I
>> change
>> City_State to be hardcoded to something for sake of example, like
>> City_State
>> = ''something'', the comma goes away. But it doesn't make any sense
>> that a
>> UDF nested in a Stored Proc would somehow return a different value
>> when it's
>> called via PHP as opposed to Query Analyzer. ...does it? Thanks a
>> billion.
>> set @.x = 'SELECT DISTINCT TOP 100 PERCENT
>> List.List_ID,
>> City_State = dbo.udf_AddressCityStateZip('''', List.List_City,
>> List.List_State, SUBSTRING(List.List_Zip, 1, 5)),
>> List.List_Latitude, List.List_Longitude
>> WHERE List_ID = 5'
>> EXEC (@.x)
>