Embedded SQL?

[misuser]

I am trying to find out if I can write SQL in a certain fashion which I want to put into a Stored Proc.

I want to select a field from the database which is a text field - ie:-

Hello $customer$,

You owe me $sales ammount$ can I have it please

etc etc

The user can define this text and it is loaded into the DB. The power here obviously is that they can enter a field they want between dollar signs without having to know how to write SQL. I want to write SQL coding to go through each charchter in the field and then when it sees a Dollar sign, call upon SQL to gather this information, and then carry on through the rest of the text field etc etc.

Can anyone give me an idea if this is possible, and the SQL I can use.

 

[nigelrivett]

Have a look at the replace function.
If you have the text stored as a text field you will have to split it into varchars to do the replaces.

If you want to return a resultset then something like

select
replace(replace(t.txt,'$customer$',u.customer), $sales amount$, d.amount)
from users u
join debts d
on u.user_id = d.user_id
and d.Company_id = @Company_id
cross join UserData t
where t.lettertype = 'SendDemand'
and t.Company_id = @Company_id

You might find it better to get the data into a temp table and run a series of replaces.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.