Sser defined function select query error

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 2005 Forums

Transact-SQL (2005)

Sser defined function select query error

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-13 : 20:29:31

I am getting te following error, trying to pass the iDateformat 101 or 103.

Msg 8116, Level 16, State 1, Procedure getRecipInfo, Line 18
Argument data type varchar is invalid for argument 3 of convert function.

ALTER FUNCTION [dbo].[getRecipInfo](@ModuleID integer, @ModuleName VARCHAR(10), @Locale VARCHAR(20))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
DECLARE @iDateFormat varchar(8)

IF @Locale='English'
BEGIN
SET @iDateFormat = '101'
END
ELSE
BEGIN
SET @iDateFormat = '103'
END

SET @s=''
SELECT @s=@s + r.[Name] + ' - ' + CONVERT(varchar(10),r.sentdate,' + @iDateFormat + ') + ' - ' + rp.filename +char(13)
from TAB_Recipients r
join TAB_DocRepository rp
on r.AttachedDocid = rp.docid
where r.ModuleRecordID = @ModuleID and r.ModuleName = @ModuleName and r.RecipientType = 'DH'
If @s >''
BEGIN
SELECT @s = left(@s, len(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END

Thank you very much for the helpful info.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-13 : 20:34:42

change to


SELECT @s = @s 
	  + r.[Name] + ' - ' 
	  + case  when @Locale = 'English' 
		  then CONVERT(varchar(10),r.sentdate, 101) 
		  else CONVERT(varchar(10),r.sentdate, 103) 
		  end
  	  + ' - ' + rp.filename 
	  + char(13)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources