Userdefined function error msg converting vchar

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)

Userdefined function error msg converting vchar

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-05 : 17:01:20

I have this userdefined function, trying to get all taskids with comma seperated. taskid is an int datatype field:

Can you please correct my function, getting message unable to convert varchar.

ALTER FUNCTION [dbo].[ccsFunctionTaskIDs](@orderidinteger)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + [taskid] + char(13)
from Taborders where orderid= @orderid

If @s IS NOT NULL AND @s <> ''
BEGIN
SELECT @s = left(@s, Datalength(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END

Thank you very much for the help.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-05 : 17:08:35

Most likely your taskid is not of varchar type in the Taborders table.

You can try casting it as such:

SELECT @s=@s + CAST([taskid] AS VARCHAR(100)) + char(13)

Some days you're the dog, and some days you're the fire hydrant.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-05 : 17:13:47

Now i am using this it is working,
ALTER FUNCTION [dbo].[ccsFunctionTaskIDs](@orderID integer)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + convert(nvarchar(100), [taskid]) + char(13)
from Tab_orders where orderid = @orderID

If @s IS NOT NULL AND @s <> ''
BEGIN
SELECT @s = left(@s, Datalength(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END

But i need a comma, instead of space.

Thank you very much.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-05 : 17:15:03

That works too

Some days you're the dog, and some days you're the fire hydrant.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-05 : 17:22:06

~~SELECT @s=@s + convert(nvarchar(100), [taskid]) + char(13)~~
SELECT @s=@s + convert(nvarchar(100), [taskid]) + ','

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources