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

You should not use a dyniamic query for the purposes of code reuse.  Use
dynamic SQL reluctantly and only for good reasons.

Here, this won't hurt a bit...

CREATE PROCEDURE  [dbo].[IF_repGetC4GeneralStat]

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

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

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,
dbo.GetC4MissingEnrollments(T1.FILEID,@AFFILIATEID,T3.CONTENT_TYPE_NAME,@CurrentAcademicYear,@CurrentSeesion,case
@ENROLLMENT_TYPR when 1 then 1 else 2 end)
AS Unknown_Active_Members,
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
WHERE (@AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @AFFILIATEID)
GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME


--David

I guess my question is about the where clause.When @AFFILIATEID = 1,I don;t
want to have a where clause ,how dose it work for this purpose?

Thanks
Show quoteHide quote

If it adversely affects the execution plan of your query to have the where
clause there, you have a couple of options without resorting to dynamic SQL.

For a query with ony two real variants, I would just code each one as a
static query.  That way each one has an appropriate cached plan, and the
whole procedure is much more readable than with dynamic SQL.


David

different queries needs to be executed against the database.as these
queries share some parts ,I decided to use a dynamic query, <<

Please stop programming and read any basic Software Engineering book.
Look up coupling and cohesion.  This is far more fundamental than just
SQL programming.  You do not know how to program.

Dynamic SQL is slow, dangerous and an admission that your design is so
weak that a random future user should have more control over it than
you did at design time.  I like to name these errors the "Get
BritneySpearsOrSquid" procedures. 

Put the core query into a VIEW.

I so appreciate your help and **thanks for the nice wording** (Excellent way
of helping the others;-)

Show quoteHide quote

On Fri, 13 May 2005 09:25:55 -0700, Ray wrote:

(an exact copy of a message he also sent 15 minutes earlier)

Hi Ray,

I already replied to your previous message. Please don't multi-post!

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