Urgent help regarding dynamic sql

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

Site Related Forums

Article Discussion

Urgent help regarding dynamic sql

Author

Topic

atalpur
Starting Member

1 Post

Posted - 2006-08-14 : 11:09:16

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

12543 Posts

Posted - 2006-08-14 : 11:27:16

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-14 : 11:38:07

Also you need to omit entering values to the identity column

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources