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
GO"Ray" <RayAll@.microsft.com> wrote in message
news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> 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
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,@.CurrentSe
esion=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,@.Cur
rentAcademicYear,@.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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u9krc39VFHA.1152@.tk2msftngp13.phx.gbl...
> "Ray" <RayAll@.microsft.com> wrote in message
> news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> 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,@.CurrentSe
esion=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,@.C
urrentAcademicYear,@.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
>
>|||"Ray" <RayAll@.microsft.com> wrote in message
news:%23xjDS%239VFHA.584@.TK2MSFTNGP15.phx.gbl...
>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?
>
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|||>> In my stored procedure I have different 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, <<
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;-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116018663.230642.241190@.g14g2000cwa.googlegroups.com...
> paramters
> 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.
>|||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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)