"andy" <andy@discussions.microsoft.com> wrote in message
news:B7116F62-2444-47B2-8D02-CA0FD89B2908@microsoft.com...
>I tried to use parameterized queries to avoid Sql Injection attack but in
> case I have to generate script based on conditional statement .. things
> got
> out of control ...
> The following link wasn't that much helpful...
> http://dotnetjunkies.com/WebLog/chris.taylor/archive/2004/10/13/28370.aspx
>
> I basically tried to create @OptionsValue dynamically but it failed...
> Can somesone show me how to take care of @p_Options value....since the
> other
> parameters are ok but @p_Options is dynamically joined statement so it's
> hard
> to find solution...
>
> the following is a part out of my proc that got messy and generated
> error...specially check @OptionsValue for solution..
>
> If (@inPriceCategoryCode1 IS NOT NULL)
> BEGIN
> SELECT @Options = N'(PriceCategory = @p_inPriceCategoryCode1 '
> SET @OptionsDefinition = N' @p_inPriceCategoryCode1 varchar(4) '
> SET @OptionsValue = N' @p_inPriceCategoryCode1 = @inPriceCategoryCode1 '
> END
>
> If (@inPriceCategoryCode2 IS NOT NULL)
> BEGIN
> SELECT @Options = @Options + N' OR PriceCategory =
> @p_inPriceCategoryCode2 '
> SET @OptionsDefinition = @OptionsDefinition + N', @p_inPriceCategoryCode2
> varchar(4) '
> SET @OptionsValue = @OptionsValue + N', @p_inPriceCategoryCode2 =
> @inPriceCategoryCode2 '
> END
>
>
> EXEC sp_executesql
> N'SELECT StrName , PriceTotal
> FROM defaultTable
> WHERE CompanyCode = @p_inCompanyCode
> AND ClassCode = + @p_inClassCode
> AND LogoCode = + @p_LogoCode
> AND @p_Options,
> N'@p_inCompanyCode varchar(4),
> @p_ClassCode varchar(10),
> @p_LogoCode varchar(5),
> @OptionsDefinition ',
> @p_inCompanyCode = @inCompanyCode,
> @p_inClassCode = @inClassCode,
> @p_LogoCode = @LogoCode,
> @OptionsValue -- <- this is where I struggle with
>
> This is error msg I got..
>
>
> Must pass parameter number 8 and subsequent parameters as '@name = value'.
> After the form '@name = value' has been used, all subsequent parameters
> must
> be passed in the form '@name = value'.