Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Urgent help regarding dynamic sql
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

atalpur
Starting Member

1 Posts

Posted - 08/14/2006 :  11:09:16  Show Profile  Reply with Quote
I am having this error after adding the case statement in order by clause. I have tried my best to figure it out but i can't. Can anybody help me?

Error: An explicit value for the identity column in table '@Temp' can only be specified when a column list is used and IDENTITY_INSERT is ON.


INSERT INTO @Temp
SELECT dbo.CASE_Case.CaseID, dbo.CASE_Case.CaseSequence, dbo.CASE_Case.CaseYear, dbo.CASE_Case.CaseRevision,
dbo.CASE_lk_CaseModes.CaseModeName, dbo.CASE_Case.CreatedDate, dbo.CASE_Case.WorkDescription,
dbo.CASE_Address_StreetNames.STREET_NAME, dbo.CASE_Address_StreetNames.STREET_SUFFIX_TYPE,
dbo.CASE_Application.CLOSE_FINAL_DATE, dbo.AT_ZIP_CODES.CITY, dbo.CASE_Addresses.OwnerName,
dbo.CASE_Addresses.CouncilDistrict,
dbo.CASE_Addresses.StreetNumber, dbo.CASE_Addresses.UnitNumber
FROM dbo.CASE_lk_CaseTypes INNER JOIN
dbo.CASE_Case ON dbo.CASE_lk_CaseTypes.CaseTypeID = dbo.CASE_Case.CaseTypeID LEFT OUTER JOIN
dbo.CASE_Application ON dbo.CASE_Case.CaseID = dbo.CASE_Application.CASE_ID LEFT OUTER JOIN
dbo.AT_ZIP_CODES RIGHT OUTER JOIN
dbo.CASE_Addresses LEFT OUTER JOIN
dbo.CASE_Address_StreetNames ON dbo.CASE_Addresses.Street_Name_ID = dbo.CASE_Address_StreetNames.STREET_NAME_ID ON
dbo.AT_ZIP_CODES.ZIP5 = dbo.CASE_Address_StreetNames.ZIP_CODE_5 AND
dbo.AT_ZIP_CODES.ZIP4 = dbo.CASE_Address_StreetNames.ZIP_CODE_4 ON
dbo.CASE_Case.CaseID = dbo.CASE_Addresses.CASEID LEFT OUTER JOIN
dbo.CASE_lk_CaseModes ON dbo.CASE_Case.CaseModeID = dbo.CASE_lk_CaseModes.CaseModeID
WHERE (NOT (dbo.CASE_lk_CaseModes.CaseModeName = @CaseMode)) AND (dbo.CASE_lk_CaseTypes.CaseTypeName =@PPDComplaint)
order by
CASE
WHEN @ColumnName= '[CaseNumber]' THEN CaseSequence
WHEN @ColumnName = '[OwnerName]' THEN OwnerName
WHEN @ColumnName = '[Street]' THEN StreetNumber
WHEN @ColumnName= '[WorkDescription]' THEN WorkDescription
END

Thanks
Asif

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/14/2006 :  11:27:16  Show Profile  Visit nr's Homepage  Reply with Quote
What's the definition of @temp?
Name the columns you are using
insert @temp (col1, col2, ...)

It won't necessarily honour the order by clause in the insert anyway.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 08/14/2006 :  11:38:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also you need to omit entering values to the identity column

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000