August 26, 2003 at 2:15 pm
This may seem a little odd but what I am trying to do in transact SQL is reduce the amount of typing needed to for proper system outputs. Let's say that you want to send out the same information about but only change cetain words within the statement it would be something like this
DECLARE @message varchar( 255 )
DECLARE @variable1 varchar( 255 )
DECLARE @variable2 varchar( 255 )
SELECT @variable1 = 'Betty'
SELECT @variable2 = 'chilie'
SELECT @message = 'Hi ' + @variable1 + ', do you like ' @variable2 + ' before 10:00AM'
SELECT @message
Of course the output would be
Hi Betty, do you like chilie before 10:00AM
where as I would like to do something like this
DECLARE @message varchar( 255 )
DECLARE @variable1 varchar( 255 )
DECLARE @variable2 varchar( 255 )
SELECT @variable1 = 'Betty'
SELECT @variable2 = 'chilie'
SELECT @message = 'Hi %ls, do you like %ls before 10:00AM', @variable1, @variable2
SELECT @message
And the out put would still be
Hi Betty, do you like chilie before 10:00AM
BUT all I get is this
'Hi %ls, do you like %ls before 10:00AM' Betty chilie
I know this sounds odd, but think of it this way, you have a table of canned messages, when you come across a problem while processing something you page out the same message. Also, you can leave all of your messages in one place without haveing to split and then re-concatinate the line.
I thought you could do this in SQL Server, at least it seems that's what's being done with the sysmessages table.
Thank you for help in advance
Later jimmY
Work like you don't need the money.
Love like you've never been hurt.
And Dance like no one is watching.
Work like you don't need the money.
Love like you've never been hurt.
And Dance like no one is watching.
August 26, 2003 at 2:54 pm
There are a few ways to accomplish this. First, if what you want is a custom error, then you can use sp_addmessage, and RAISERROR to do this. For example, if your message was as given, then:
EXEC sp_addmessage 55555, 16,
'Hi %s, do you like %s before 10:00AM'
RAISERROR (55555, 16, 1, @variable1, @variable2)
If you are using SQL2K, you could create a UDF to do the replacements. Since a UDF does not have the capability to accept an undefined number of parameters, this would need to have default values. It might look something like this (a very simplistic version):
Create Function fn_UserMessage(@MsgID int, @P1 varchar(50),
@P2 varchar(50) = '', @P3 varchar(50) = '',
@P4 varchar(50) = '', @P5 varchar(50) = '',
@P6 varchar(50) = '', @P7 varchar(50) = '',
@P8 varchar(50) = '', @P9 varchar(50) = '',
@P10 varchar(50) = '')
RETURNS varchar(250)
AS
BEGIN
Declare @Msg varchar(250)
Select @Msg = MessageText FROM Messages WHERE MessageID = @MsgID
Return(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(@Msg, '%1', @P1), '%2', @P2), '%3', @P3), '%4', @P4), '%5', @P5), '%6', @P6), '%7', @P7), '%8', @P8), '%9', @P9), '%0', @P10))
END
Note that in this case, each parameter in the message string was identified by its position number. This actually allows for more possibilities with fewer parameters, e.g.:
ID Message
1 '%1 is not a valid %2. Please enter a value other than %1.'
Select fn_UserMessage(1,'Wombat','Lizard')
Returns:
Wombat is not a valid Lizard. Please enter a value other than Wombat.
August 27, 2003 at 7:13 am
From my point of view you like to get a replacement functionality like in printf from C. This is like a multiple replace.
Try this function to get it done:
----SNIP----
CREATE FUNCTION fn_MutltipleReplace
(@iv_TextString varchar(8000),
@iv_ParameterString varchar(8000),
@iv_TextParameterIdentifier varchar(1) = '%',
@iv_ParameterDelimiter varchar(1) = '|')
RETURNS VARCHAR(8000)
AS
BEGIN
if @iv_TextString is null or
@iv_ParameterString is null or
@iv_TextParameterIdentifier is null or
@iv_ParameterDelimiter is null or
ltrim(rtrim(@iv_ParameterString)) = ''
return (@iv_TextString)
declare @i_ParameterSearchStart int,
@i_SearchResult int,
@i_ParameterCounter int,
@v_ReplacementKey varchar(2),
@v_ReplacementValue varchar(8000),
@v_ReturnValue varchar(8000)
select @i_ParameterSearchStart = 1,
@i_ParameterCounter = 1,
@iv_ParameterString = @iv_ParameterString + @iv_ParameterDelimiter,
@v_ReturnValue = @iv_TextString
select @i_SearchResult = CHARINDEX(@iv_ParameterDelimiter, @iv_ParameterString, @i_ParameterSearchStart)
while (@i_SearchResult > 0)
begin
select @v_ReplacementKey = @iv_TextParameterIdentifier + convert(varchar,@i_ParameterCounter),
@v_ReplacementValue = substring(@iv_ParameterString, @i_ParameterSearchStart, @i_SearchResult - @i_ParameterSearchStart)
select @v_ReturnValue = REPLACE(@v_ReturnValue, @v_ReplacementKey, @v_ReplacementValue)
select @i_ParameterSearchStart = @i_SearchResult + 1,
@i_ParameterCounter = @i_ParameterCounter + 1,
@i_SearchResult = CHARINDEX(@iv_ParameterDelimiter, @iv_ParameterString, @i_ParameterSearchStart)
end
return (@v_ReturnValue)
END
GO
----SNIP----
select [dbo].fn_MutltipleReplace('Hi %1, do you like %2 before 10:00AM','Betty|chilie', default, default)
As you can see from the parameters you can choose the delimiter to separate the replacement values as well as the identifer for the replacements in the text. This will give you a maximum of flexibility.
August 27, 2003 at 8:19 am
If your message can be less than 255 characters, you might want to check out the extended stored procedure xp_sprintf. If you have ever done C programming, you will feel right at home with this stored proc.
Here is an example:
declare @variable1 nvarchar(10)
declare @variable2 nvarchar(10)
declare @message varchar(255)
set @variable1 = 'Betty'
set @variable2 = 'chili'
exec master..xp_sprintf @message output, 'Hi %s, do you like %s before 10:00 AM?', @variable1, @variable2
print @message
set @variable1 = 'Mike'
set @variable2 = 'Eggs'
exec master..xp_sprintf @message output, 'Hi %s, do you like %s before 10:00 AM?', @variable1, @variable2
print @message
August 27, 2003 at 10:29 am
Thanks for all of you help guys, this is exactly what Iwas looking for
Later jimmY
Work like you don't need the money.
Love like you've never been hurt.
And Dance like no one is watching.
Work like you don't need the money.
Love like you've never been hurt.
And Dance like no one is watching.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply