>Hi
>
>I've wriiten a Foxpro proceedure in VFP9 that allows the user to build up a command using different selected search criteria, this then creates an SQL type command that extracts data from a massic Postcode table.
>
>E.g.
>
>mfullcond="SUBS(DMOS,4,1)$AB and left(f17,1)='0' "
>SELECT * from q.dbf WHERE &mfullcond INTO TABLE results
>
>The customer is now interested in transfering the data into an SQL 2008 server, are the same/similar commands available with real SQL 2008.
>
>Is it possible to do contained within in SQL, and can you do left in SQL 2008?
>
>Any help extremley appreciated
>
>Rob
No, there is no macro substitution in SQL Server (no matter which version).
You have to use so called Dynamic SQL (buk,d the whole query in single string and then use EXEC () )
Also SUBS() in VFP = SUBSTRING() in SQL Server (LEFT() is the same)
Also there is no [$] you have to use LIKE or CHARINDEX()
SELECT * from Table WHERE Ab LIKE '%' + SUBSTRING(DMOS,4,1) + '%'
or
SELECT * from Table WHERE CHARINDEX(SUBSTRING(DMOS,4,1), Ab) > 0
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.