CONCAT is not a recognized built in function name

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

General SQL Server Forums

New to SQL Server Programming

CONCAT is not a recognized built in function name

Author

Topic

SecretiveLoon
Starting Member

3 Posts

Posted - 2008-08-17 : 21:49:19

The title of this thread is the error I get. Here's the code:


CREATE PROCEDURE Membership_GetNameOrUsername
(@UserId UNIQUEIDENTIFIER)
AS
DECLARE @GivenName NVARCHAR(64)
DECLARE @FamilyName NVARCHAR(64)
SELECT @GivenName = GivenName, @FamilyName = FamilyName
FROM UserProfiles
WHERE UserId = @UserId

IF @GivenName = NULL OR @FamilyName = NULL
	RETURN (SELECT UserName FROM aspnet_Users WHERE UserId = @UserId)
ELSE
	RETURN (CONCAT(@GivenName, " ", @FamilyName))
END IF

The word CONCAT is blue, as it should be, so it does recognise it as a function, but obviously it doesn't :S Any ideas?

Also, if there's any way of improving that code, let me know and I'll change it. I'm quite new to SQL so a lot of the stuff I did in the query is the first time I've ever done it (things like DECLARE, IF, NULL and RETURN).

All replies are appreciated :)

EDIT:
Hmm, I decided to just return the GivenName just for the moment until I figure out the error, so I can continue coding. To my surprise, another error occured: Incorrect syntax near the keyword 'IF'.

I thought it might be because I don't have brackets so I changed that part of the code to this:
IF ((@GivenName = NULL) OR (@FamilyName = NULL))
RETURN (SELECT UserName FROM aspnet_Users WHERE UserId = @UserId)
ELSE
/*RETURN (CONCAT(@GivenName, " ", @FamilyName))*/
RETURN @GivenName
END IF

but I still get the same error. Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-17 : 21:54:24

use '+' to concatenate the string

RETURN (@GivenName + " " + @FamilyName)

EDIT : typo

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-17 : 22:03:20

[code]
IF ((@GivenName = NULL) OR (@FamilyName = NULL))
RETURN (SELECT UserName FROM aspnet_Users WHERE UserId = @UserId)
ELSE
/*RETURN (CONCAT(@GivenName, " ", @FamilyName))*/
RETURN @GivenName
~~END IF~~
[/code]

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

SecretiveLoon
Starting Member

3 Posts

Posted - 2008-08-17 : 22:03:44

Brilliant. Thanks :) That stopped that error. But another came up :S The IF is giving trouble: Incorrect syntax near the keyword 'IF'.

I thought it might be because I don't have brackets so I changed that part of the code to this:
IF ((@GivenName = NULL) OR (@FamilyName = NULL))
RETURN (SELECT UserName FROM aspnet_Users WHERE UserId = @UserId)
ELSE
RETURN (@FamilyName + " " + @GivenName)
END IF
[/quote]

Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-17 : 22:05:48

quote:
I'm quite new to SQL

Please refer to Books Online for the proper syntax and usage of keyword if you are not sure rather than just assumed it should behave as what you expect.

If you don't have the Books Online, you can refer to the online version http://msdn.microsoft.com/en-us/library/ms130214.aspx

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

SecretiveLoon
Starting Member

3 Posts

Posted - 2008-08-17 : 22:12:02

Wow. You were replying faster than I could post :D Thanks for the help. I finally got all errors to stop when I changed the " to '. I was getting a weird invalid column error. All fixed now.

Thanks again, KH :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-18 : 04:14:17

IF ((@GivenName = NULL) OR (@FamilyName = NULL))

should be

IF ((@GivenName IS NULL) OR (@FamilyName IS NULL))

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 04:18:04

quote:
Originally posted by madhivanan

IF ((@GivenName = NULL) OR (@FamilyName = NULL))

should be

IF ((@GivenName IS NULL) OR (@FamilyName IS NULL))

Madhivanan

Failing to plan is Planning to fail

Unless you've SET ANSI NULLS setting to OFF

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-18 : 05:20:19

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

IF ((@GivenName = NULL) OR (@FamilyName = NULL))

should be

IF ((@GivenName IS NULL) OR (@FamilyName IS NULL))

Madhivanan

Failing to plan is Planning to fail

Unless you've SET ANSI NULLS setting to OFF

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources