Cast and variables in function

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

Transact-SQL (2000)

Cast and variables in function

Author

Topic

herbjorn
Starting Member

8 Posts

Posted - 2005-02-28 : 09:35:55

Hi,

I'm trying to do the following:

select cast(12345 as nvarchar)

The result is
(No column name)
12345

So far so good. If I use variables like this:

declare @int int
declare @var nvarchar(100)
select @int = 12345
select @var= cast(@int as nvarchar)
select @var

The result is
(No column name)
12345

However, if I use this code inside a function the result is
(No column name)
1

Why?

What can I do to achieve the desired result inside a function?

Thanx

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 09:41:47

Well...I would suggest that you always define the length of your char datatypes...but I don't have the problem you mention


CREATE FUNCTION myUDF99 (@int int)
RETURNS nvarchar(100)
AS
  BEGIN
	DECLARE @var nvarchar(100)
	SET @var= cast(@int as nvarchar)
	RETURN @var
  END
GO

SELECT dbo.myUDF99(12345)
GO

DROP FUNCTION myUDF99
GO

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 09:42:12

you need the bold part:
select @var= cast(@int as nvarchar(100))

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 09:44:27

If you change the RETURNS part to be RETURNS nvarchar, then you get the results you mention.

Always supply the length

Brett

8-)

herbjorn
Starting Member

8 Posts

Posted - 2005-02-28 : 09:51:37

Hi!

The problem was that I hadn't specified the length of the RETURNS clause. When I had problems it was like this:

RETURNS nvarchar

Now that it is working it is

RETURNS nvarchar(100)

THANX!

quote:
Originally posted by X002548

If you change the RETURNS part to be RETURNS nvarchar, then you get the results you mention.

Always supply the length

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources