Problems Using Fields to Build SQL Strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems Using Fields to Build SQL Strings

Author

Topic

martinch
Starting Member

35 Posts

Posted - 2004-11-23 : 03:43:39

Hi all,
I'm working on a student database, which is supposed to check every night if certain "events" have happened, and automatically perform an "action" based on the user's settings. For each "event", it is possible for multiple "actions" to need to be performed. For example, if a student is a week late in handing in their dissertation ("event"), we are supposed to e-mail them and their tutor ("actions").

Currently, this works by having one (automatically generated) stored proc per event which checks and processes the data. These Stored Procs are job steps chained into one long scheduled job.

Table structure:
Events Table:
ID
SP to run
(some other non-relevant stuff)

Actions table
Action ID
Owning Event
Action Type
E-Mail Body
(some other non-relevant stuff)

So far, so straight-forward

The problem is, the user wants to be able to include certain fields in the message body, e.g. "Your dissertation was supposed to be handed in on [Date Due], but you haven't". Currently, the message body is hard-coded into the Stored Proc when it is created, but we would like it to be able to read the body from the Actions Table, so the user can modify the message body themselves, instead of pestering us

. This works, as long as you don't include references to variables in the string (which are needed), at which point, it complains that the variables aren't declared.

The code I am using looks like this (cut down, a lot

):

Declare @Due datetime
Select @Due = ...

Select @Message = Body From Actions Where ActionEvent = @EventID
Exec(@Message)

Where the value in @Message reads something like:
"Select 'This students project was due on ' + Convert(varchar(11), @Due))"
where @Due is defined in the body of the Stored Proc (as these Stored Procs are generated automatically, we know a bit about what the user wants, and some "data-sensitive" variables are written in).

This code falls over on the Exec statement, as, apparently, none of the variables have been declared (even though they have). Would I be correct in assuming that @Message can't contain variables, as they've not been declared in (or rather, within) the Exec statment?

Is there any way to fix this, or are we going to have to leave it hard-coded?

Hopfully I've been clear on this one (I have an alarming tendency to ramble...

)..!

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-23 : 04:31:14

Instead of @Due put a value contained in control characters e.g. <Due>
then before the exec run a series of replaces. You just need to make sue there is a replace for each possible control character.
You might decide to have an SP to do the replaces to which you pass parameters for the replace varuables then you can do format changes easily.

select @message = replace(@message, '<Due', convert(varchar(30), @Due))
exec (@meassage)

sp to do the replaces

create proc s_replace
@message varchar(1000) out ,
@due datetime = null ,
@name varchar(20) = null
...
as

if @message like '%<Due>%'
begin
if @Due is null
begin
raiserror('no Due date', 16, -1)
return
end
else
begin
select @message = replace(@message, '<Due', convert(varchar(30), @Due, 101))
end
.....

end

call by

exec s_replace @message = @message out, @Due = @Due

That will give you an error if the data in your SP doesn't match that needed for the message.

==========================================
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.

martinch
Starting Member

35 Posts

Posted - 2004-11-23 : 05:22:59

That works fine - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources