November 17, 2008 at 10:09 am
When using sp_executesql, is it possible to "evaluate" a function? In other words, is there anyway to produce "@SQL 1"'s result by passing a parameter (@avcs) equaling the calculation ('AVG(clean_sale_price)') and not "hardcoding" the calculation by appending strings (i.e., SET @sql = 'SELECT @r_id ' + @avcs)?
@SQL 1:
SELECT @r_id, @b_id, @ce_id, @dd, @fs_id, @c_id, @erli, AVG(clean_sale_price)
FROM [dbo].[tbl_pa_products] AS p
JOIN [dbo].[tbl_pa_product_details] AS pd ON pd.fk_product_id = p.pk_product_id
JOIN [dbo].[tbl_pa_batch_information] AS b ON p.parser_batch_id = b.pk_batch_id
AND p.fk_retailer_id = b.fk_retailer_id
WHERE b.analytic_date = @dd
AND p.fk_retailer_id = @r_id
@SQL 2:
SELECT @r_id, @b_id, @ce_id, @dd, @fs_id, @c_id, @erli, @avcs
FROM [dbo].[tbl_pa_products] AS p
JOIN [dbo].[tbl_pa_product_details] AS pd ON pd.fk_product_id = p.pk_product_id
JOIN [dbo].[tbl_pa_batch_information] AS b ON p.parser_batch_id = b.pk_batch_id
AND p.fk_retailer_id = b.fk_retailer_id
WHERE b.analytic_date = @dd
AND p.fk_retailer_id = @r_id
EXEC sp_executesql @sql,
N'@r_id INT, @b_id INT, @ce_id INT, @fs_id INT, @erli INT, @c_id INT, @dd DATETIME, @avcs VARCHAR(MAX)',
@r_id = @retailer_id,
@b_id = @brand_id,
@ce_id = @corporate_entity_id,
@fs_id = @fk_statistic_id,
@c_id = @classification_id,
@erli = @exclude_retailer_list_id,
@dd = @data_date,
@avcs = @analytic_value_calculation_string
Result of @SQL 1:
8NULLNULL2008-08-03 00:00:00.00042NULLNULL161.1198
Result of @SQL 2:
8NULLNULL2008-08-03 00:00:00.00042NULLNULLAVG(clean_sale_price)
8NULLNULL2008-08-03 00:00:00.00042NULLNULLAVG(clean_sale_price)
8NULLNULL2008-08-03 00:00:00.00042NULLNULLAVG(clean_sale_price)
8NULLNULL2008-08-03 00:00:00.00042NULLNULLAVG(clean_sale_price)
8NULLNULL2008-08-03 00:00:00.00042NULLNULLAVG(clean_sale_price)
November 17, 2008 at 4:47 pm
You're passing a constant text string to your parm. Try adding the avg string to your dynamic SQL (@SQL) before you execute it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 18, 2008 at 6:40 am
Bob Hovious (11/17/2008)
You're passing a constant text string to your parm. Try adding the avg string to your dynamic SQL (@SQL) before you execute it.
Are you referring to something like this:
SET @sql = N'SELECT @r_id, @b_id, @ce_id, @dd, @fs_id, @c_id, @erli, ' + @analytic_value_calculation_string
If so, I was trying to avoid appending strings (i.e., @sql = 'SELECT @r_id ' + @analytic_value_calculation_string) to prevent SQL injection attacks. However, I'm not sure if there's any other way to do this.
November 19, 2008 at 5:29 pm
I don't know of one, so I'll bow out.
However, I don't understand your concern about insertions if you are building strings under your own control. I am guessing that the AVG(clean_sale_price) function could change. If you were writing it to build strings out of values passed from a column or variable supplied by a UI, I could understand. Is that your intention?
If not, how can the following expose you to an insertion attack?
set @sql = 'select AVG(clean_sale_price) from fooTable'
EXEC sp_executesql @sql
If so, consider passing a numeric parameter and have a case statement to determine which function to use.
Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 20, 2008 at 6:47 am
Bob Hovious (11/19/2008)
However, I don't understand your concern about insertions if you are building strings under your own control. I am guessing that the AVG(clean_sale_price) function could change. If you were writing it to build strings out of values passed from a column or variable supplied by a UI, I could understand. Is that your intention?
Yes, that's my intention. The UI could choose from any set of columns for the AVG.
If so, consider passing a numeric parameter and have a case statement to determine which function to use.
That's a good idea. Thanks for the info.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply