sql server (alternate): Dear Group

I'm having trouble with the SQL statement below that works fine if not
run as dynamic SQL but fails with an ambigious column name error on
'Status' if build dynamic.

I explicitely qualified 'Status' with the table prefix but no luck. I
reckon dynamic SQL doesn't recognize my table prefix. Any way I can do
this in dynamic SQL?

SET @cmdSQL = 'SELECT PropertyID, Code, Quickname, Address1, Address2,
Suburb, City, Postcode, i2b_sys_status.Status FROM i2b_property JOIN
i2b_address ON (i2b_property.AddressID = i2b_address.AddressID) JOIN
i2b_sys_status ON (i2b_property.Status = i2b_sys_status.StatusID)
WHERE ProgClientID = ' + CONVERT(varchar(10),@ProgClientID)

Thanks for your time & efforts!

[quoted text, click to view]

Why do you need dynamic SQL at all? Just run the query normally:

SELECT PropertyID, Code, Quickname, Address1, Address2,
Suburb, City, Postcode, i2b_sys_status.Status FROM i2b_property JOIN
i2b_address ON (i2b_property.AddressID = i2b_address.AddressID) JOIN
i2b_sys_status ON (i2b_property.Status = i2b_sys_status.StatusID)
WHERE ProgClientID = @ProgClientID

Based on what you've posted, perhaps you have a @ProgClientID of
'Status', in which case you would get the error because you haven't
quoted the value:

WHERE ProgClientID = Status -- column name
WHERE ProgClientID = 'Status' -- string

You can PRINT @cmdSQL just before you execute it to see if this is the
case. If so, you can do this instead:

set @cmdSQL = '
....
WHERE ProgClientID = ''' + CONVERT(varchar(10),@ProgClientID) + ''''

But there's no obvious reason why you need dynamic SQL here anyway,
and if you don't need dynamic SQL then you shouldn't use it. See here
for a discussion of the reasons why to avoid it:

http://www.sommarskog.se/dynamic_sql.html