|
arehmanbtc
Starting Member
7 Posts |
 Posted - 2007-12-13 : 06:33:01
|
| Hi Guys,I am declaring a Varchar(MAX) variable .What i know about this enhanced variable is it will allow more then 8000 chars.But in my case it is not..I am placing my Sp below Plz check..."SP BEGINS HERE" ALTER PROC [dbo].[ReqRec_AddHireApplicantApproverADMIN] ( @intRequisitionID INT, @strRequisitionCode VARCHAR(35)='', @strORGLevel1 VARCHAR(3)='', @strORGLevel2 VARCHAR(3)='', @strORGLevel3 VARCHAR(3)='', @strORGLevel4 VARCHAR(3)='', @strORGLevel5 VARCHAR(3)='', @strORGLevel6 VARCHAR(3)='', @strORGLevel7 VARCHAR(3)='', @strORGLevel8 VARCHAR(3)='', -----------------------------------EMPLOYEE DETAILS-------------- @intAppID INT, @strUserID VARCHAR(50)='', @strDOJ VARCHAR(50)='' --@intFlag VARCHAR(20) ) -- EXEC ReqRec_AddHireApplicantApproverADMIN 22,'JKOLKATA000001','001','001','002','001','002','023','014','003','14823','sysadmin','2007/10/1' AS BEGIN DECLARE @strSQLQuery AS VARCHAR(MAX) DECLARE @strSQLQuery1 AS VARCHAR(MAX) SET @strSQLQuery=' BEGIN TRAN DECLARE @strempcode1 as varchar(500) , @intMax AS INT, @strEmpCode AS Varchar(10), @intResult AS INT IF dbo.Req_GetApplicantCurrentStatus('+CAST(@intAppID AS VARCHAR(8))+','+CAST(@intRequisitionID AS VARCHAR(8))+')=23 INSERT INTO ReqRec_ApplicantStatusDetails ( AD_AppID, ASD_AD_ReqID, ASD_SM_StatusID, ASD_Auth_UD_UserID ) VALUES(' +CAST(@intAppID AS VARCHAR(8))+','+ CAST(@intRequisitionID AS VARCHAR(8))+', 25, '''+ @strUserID+''' ) Select @strempcode1='''+dbo.ReqRec_GetEmployeeNumberADMIN()+''' DELETE FROM REQREC_APPIDEMPCODEDETAILS WHERE AECD_AD_APPID='+CAST(@intAppID AS VARCHAR(8))+' AND AECD_RTD_ReqID='+CAST(@intRequisitionID AS VARCHAR(8))+' INSERT INTO REQREC_APPIDEMPCODEDETAILS VALUES('+CAST(@intAppID AS VARCHAR(8))+','+CAST(@intRequisitionID AS VARCHAR(8))+','''+dbo.ReqRec_GetEmployeeNumberADMIN()+''') INSERT INTO ReqRec_EmployeeDetails ( ED_EMPCode, ED_Salutation, ED_FirstName, ED_MiddleName, ED_LastName, ED_Sex, ED_DOB, ED_BirthPlace, ED_MaritalStatus, ED_PresentTelephone, ED_PermanentTelephone, ED_Mobile, ED_Passport, ED_Fax, ED_Email, ED_PresentAddress, ED_PresentCity, ED_PresentPin, ED_PermanentAddress, ED_PermanentCity, ED_PermanentPin, ED_FatherName, ED_Status, ED_HomePage, ED_Remarks, ED_SpouseName, ED_WorkingAddress ) VALUES ('''+dbo.ReqRec_GetEmployeeNumberADMIN()+''','+ dbo.ReqRec_AddApplicantToEmployeeMasterApprover(@intRequisitionID,@intAppID) + ','+ dbo.ReqRec_AddApplicantToEmployeeMasterApprover1(@intRequisitionID) + +') SELECT @intMax = ED_EMPID FROM ReqRec_EmployeeDetails where ED_EMPCode ='''+dbo.ReqRec_GetEmployeeNumberADMIN()+''' SELECT @strEmpCode=ED_EMPCode FROM ReqRec_EmployeeDetails WHERE ED_EMPID =@intMax UPDATE REQREC_EMPLOYEEDETAILS SET ED_DOJ='''+ @strDOJ +''' WHERE ED_EMPCODE=@strEmpCode INSERT INTO Setup_ORGEmployeeMaster ( ORGEM_EmpID, ORGEM_EmpCode , ORGEM_OrgLevel1ID, ORGEM_OrgLevel2ID, ORGEM_OrgLevel3ID, ORGEM_OrgLevel4ID, ORGEM_OrgLevel5ID, ORGEM_OrgLevel6ID, ORGEM_OrgLevel7ID, ORGEM_OrgLevel8ID ) VALUES ( @intMax, @strEmpCode,'''+ @strORGLevel1+''','''+ @strORGLevel2+''','''+ @strORGLevel3+''','''+ @strORGLevel4+''','''+ @strORGLevel5+''','''+ @strORGLevel6+''','''+ @strORGLevel7+''','''+ @strORGLevel8+''' )' -- Exec ReqRec_GetNoticePeriod @strORGLevel1 , @strORGLevel2 ,@strORGLevel3, @strORGLevel4 , @strORGLevel5 , @strORGLevel6 ,@strORGLevel7 , @strORGLevel8 ------------------------------------------------EXPERIENCE--------------------------------------------- DECLARE @CatID AS VARCHAR(8), @ExpID AS VARCHAR(8), @Years AS VARCHAR(8), @Months AS VARCHAR(8) DECLARE ExpCursor CURSOR FOR SELECT ISNULL(AE_ECM_ExpCategoryID,0), ISNULL(AE_EM_ExpID,0), ISNULL(AE_Years,0), ISNULL(AE_Months,0) FROM ReqRec_ApplicantExperience WHERE AE_AD_AppID=@intAppID OPEN ExpCursor FETCH NEXT FROM ExpCursor INTO @CatID, @ExpID, @Years, @Months WHILE @@FETCH_STATUS = 0 BEGIN SET @strSQLQuery = @strSQLQuery +' INSERT INTO ReqRec_EmployeeExperience( EE_ED_EMPID, EE_ECM_ExpCategoryID, EE_EM_ExpID, EE_Years, EE_Months) VALUES(@intMax,'+@CatID+','+ @ExpID+','''+ @Years+''','''+ @Months+''') ' FETCH NEXT FROM ExpCursor INTO @CatID, @ExpID, @Years, @Months END CLOSE ExpCursor DEALLOCATE ExpCursor ------------------------------------------------SKILL--------------------------------------------- DECLARE @SkillID AS VARCHAR(8), @LevelID AS VARCHAR(8) DECLARE SkillCursor CURSOR FOR SELECT ISNULL(AS_SM_SkillID,0), ISNULL(AS_SkillLevel,0) FROM ReqRec_ApplicantSkill WHERE AS_AD_AppID=@intAppID OPEN SkillCursor FETCH NEXT FROM SkillCursor INTO @SkillID,@LevelID WHILE @@FETCH_STATUS = 0 BEGIN SET @strSQLQuery = @strSQLQuery +'INSERT INTO ReqRec_EmployeeSkill(ES_ED_EMPID,ES_SM_SkillID,ES_SkillLevel) VALUES(@intMax,'+@SkillID+','+ @LevelID+') ' FETCH NEXT FROM SkillCursor INTO @SkillID,@LevelID END CLOSE SkillCursor DEALLOCATE SkillCursor SET @strSQLQuery=@strSQLQuery+ 'IF @@ERROR <> 0 BEGIN ROLLBACK SET @intResult =0 END ELSE BEGIN COMMIT SET @intResult =1 END SELECT @intResult ' --PRINT (@strSQLQuery) --EXEC (@strSQLQuery) print (len(@strSQLQuery)) END --rollback Thanks,sohails |
|
Problem with VARCHAR(MAX)