Dynamic Query

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,@.CurrentSe
esion=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(CA
SE 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,@.Cu
rrentAcademicYear,@.CurrentSeesion,1)
AS Unknown_Active_Members,'
END
ELSE --HISTORY ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.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
GOOn Fri, 13 May 2005 09:10:56 -0700, J-R wrote:

>Hi,
>In my stored procedure I have differnet paramters and based on the paramter
s
>different queries needs to be executed against the database.as these querie
s
>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?
(snip)
> SELECT @.DynamicQuery
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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)