Whats wrong with this SQL - SQL Server Forums

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)

Whats wrong with this SQL

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-21 : 19:27:34

I am trying to run an sql query with a function in SQL SERVER MANAGEMENT STUDIO and am getting the following error.

Msg 207, Level 16, State 1, Line 9
Invalid column name 'UserID'.

here is my Query
-----------------
USE vportal4vsearch
GO
CREATE FUNCTION dbo.ConcatUserGroups(@UserID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @groupname Varchar(2000)
SELECT @groupname = COALESCE(u.group_name + ', ', '') + CONVERT(varchar(20), u.group_name)
FROM dbo.[user] A
JOIN dbo.UserGroup_combo UG
ON UG.ug_userID = a.USER_ID
JOIN dbo.usergroup U
ON UG.ug_groupID = u.group_ID
WHERE A.USER_ID = @UserID
ORDER BY U.GROUP_NAME

RETURN @groupname
END
GO
SELECT
user_ID,
user_Firstname,
user_Lastname,
user_Email,
Username = (Case When user_Username = ' ' or user_username is Null then 'None' else User_Username end),
user_creationdate,
Active = (Case When user_Active = 1 then 'Yes' else 'No' end),
dbo.ConcatUserGroups(UserID),
TheatreCode = (Case When User_TheaterCode = ' ' or User_TheaterCode is Null then 'N/A' else User_TheaterCode end),
JobTitle = (Case When user_JobTitle = ' ' or User_Jobtitle is Null then 'N/A' else User_Jobtitle end)
FROM [user]
GO
DROP FUNCTION dbo.ConcatUserGroups
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 19:33:42

In your SELECT list, you've got user_ID, but then you are passing UserID to the function. So which one is correct?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-21 : 19:42:14

both are correct, i ran the function explicitly and "command successfully parsed" but when i ran the below select statement i got that error.. am not sure where it's going wrong..

user_id is from user table and UserID is an int am passing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 20:42:10

You aren't doing that according to your code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

reachnar
Starting Member

1 Post

Posted - 2008-03-22 : 00:40:18

dbo.ConcatUserGroups(UserID),

replace the above line of code with

dbo.ConcatUserGroups(User_ID). It should do.

Nana

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-23 : 22:03:45

Agreed... from the looks of things, the "User" table has a column named "User_ID" and not "UserID".

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-24 : 20:10:40

Thank you all.. yes it worked for me when i replaced with User_ID.

thanks once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources