Problem using CASE in an insert statement

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)

Problem using CASE in an insert statement

Author

Topic

madrak
Starting Member

20 Posts

Posted - 2006-06-13 : 18:19:35

Background information:

I have a database (of course) where I have one table storing the information about an object and then another table which contains the information linking that object with its parent. In this case a single object can have different types of parents, so the assignment table looks like this in the case of a calendar entry

EntryID
ClientID
RegionID
DepartmentID
...

so if calendar entry 1 is assigned to client 1 the table will look like this...

EntryID ClientID RegionID DepartmentID
1 1 <NULL> <NULL>

and if Entry 2 is assigned to region 5 it would look like this

EntryID ClientID RegionID DepartmentID
2 <NULL> 5 <NULL>

now what i sm trying to do is use a select case to detect the type of parent for the object and then insert the value into the appropriate column, the statement would look like

insert into calentryassignments (EntryID, case @ParentType WHEN 1 THEN ClientID WHEN 2 THEN RegionID WHEN 3 THEN DepartmentID) values (@EntryID, @ParentID)

but this is throwing up a syntax error on the word case, is it not possible to use a case statement in this way? am i missing something? do i need to use 3 separate if statements to do the same thing?

any help on this will be greatly appreciated

-Madrak

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-13 : 18:55:27

You can't use case where you have it. Only the column list can go there. But you can use a SELECT with CASE to insert the data. Something like this:

insert into calentryassignments(yourColumnsGoHere)
SELECT @EntryID, CASE WHEN @ParentType = 1 THEN ClientID WHEN @ParentType = 2 THEN RegionID WHEN @ParentType = 3 THEN DepartmentID END

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-13 : 19:35:47

>>is it not possible to use a case statement in this way?

Always remember: CASE is a *function*, not a statement. It returns a single value based on an expression. It cannot be used to control flow or anything like that. As Tara said, you can still use it, but not the way you are trying.

I think what you want is this:


insert into CalentryAssignments (EntryID, ClientID, RegionID, DepartmentID)
values (@EntryID, case when @parentType=1 then @ParentID else null end,
                  case when @parentType=2 then @ParentID else null end,
                  case when @parentType=3 then @ParentID else null end)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources