Hi,

In my stored procedure I have differnet paramters and based on the paramters
different queries needs to be executed against the database.as these queries
share some parts ,I decided to use a dynamic query,so I declared a variable
(DECLARE @DynamicQuery Varchar(5000)) and based on parameters I append
differnt strings to it (@DynamicQuery=@DynamicQuery+ 'string') .The problem
is that this string limits to 750 characters only and it can not hold my
whole query which is more than 750 characters.Why is that so?

Thanks
Here is my sp:

CREATE PROCEDURE  [dbo].[IF_repGetC4GeneralStat]

@AFFILIATEID NUMERIC (8)  = 1,
@ENROLLMENT_TYPR INT=1   -- 1

WITH RECOMPILE  --Throw out the query plan on every execution of this
storedprocedure due to its dynamic nature

AS

SET NOCOUNT ON



DECLARE @CurrentAcademicYear NUMERIC(5)
DECLARE @CurrentSeesion NUMERIC(5)
DECLARE @DynamicQuery VARCHAR(5000)



SELECT @CurrentAcademicYear=CUR_YEAR,@CurrentSeesion=CUR_SESSION FROM
dbo.CURRENT_SESSION

SET @DynamicQuery = 'SELECT T1.FILEID AS FileID,T1.CURRENT_CGA_REGION_CODE
AS Affiliate_Code,MIN(T1.RECORD_TIMESTAMP)   AS
File_ProcessDate,COUNT(T1.FILEID) AS Total_Records,COUNT(CASE
T1.ERROR_STATUS WHEN 1 THEN T1.FILEID  END) AS
NOfRows_with_Critical_Error,COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID
END) AS NOfRows_with_Biz_Critical_Error,COUNT(CASE T1.ERROR_STATUS WHEN 2
THEN T1.FILEID END) AS NOfRows_with_Warning,COUNT(CASE
T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID
END) AS Updated_Different_20,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN
T1.FILEID END) AS Updated_Different_21,COUNT(CASE T1.BIZPROCESSING_STATUS
WHEN 30 THEN T1.FILEID END) AS Matched_NoAction,'

SELECT LEN(@DynamicQuery )

IF @ENROLLMENT_TYPR =1   --CURRENT/FUTURE ENROLLMENT

  BEGIN
        SET @DynamicQuery = @DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@AFFILIATEID,T3.CONTENT_TYPE_NAME,@CurrentAcademicYear,@CurrentSeesion,1)
AS Unknown_Active_Members,'
  END

ELSE    --HISTORY ENROLLMENT
  BEGIN

       SET @DynamicQuery = @DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@AFFILIATEID,T3.CONTENT_TYPE_NAME,@CurrentAcademicYear,@CurrentSeesion,2)
AS Unknown_Active_Members,'

  END


          SET @DynamicQuery = @DynamicQuery +
     'T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS
File_Type
                 FROM IF_C1TRANSFORM T1 LEFT OUTER JOIN IF_FILE  T2 ON
T1.FILEID=T2.[FILE_ID]
                           LEFT OUTER JOIN IF_CONTENT_TYPE T3 ON
T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID'

         IF @AFFILIATEID <> 1
   BEGIN
         SET @DynamicQuery=@DynamicQuery +
     'WHERE T1.CURRENT_CGA_REGION_CODE = @AFFILIATEID'
  END

         SET @DynamicQuery=@DynamicQuery +
     'GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME'



         SELECT @DynamicQuery
GO

On Fri, 13 May 2005 09:10:56 -0700, J-R wrote:

Hi J-R,

Probably because you are testing this in Query Analyzer, with the
maximum output width (Tools / Options / Results / Maximum characters per
column) set to 750.

Best, Hugo

ntext and update/insert triggers
Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Query Help
FIFO report