Executing Dynamic Sql in function
-
Hi i am migrating a sybase function to sql server and have come up against a biggie. In sybase, the developers have coded a function which accepts three parameters one of these is a test prefix and a program_id. The code then finds a string which has a particular test which relates to the test_prefix. It then executes this test which also injects certain parmeters based on the test at execution time, in a if stament inside a select clause and returns the result of the if.
Set @cd_execute = 'Select if ' + @tx_test + ' then 1 else 0 endif into @nr_valid';
execute (@cd_execute);
return(@nr_valid)
As you are unable to execute a dynamic sql ina function i have recoded this as a stored proceedure. I have now been told that the function is used everywhere in the application being called in a where clause which calling stored proceedures is not allowed. Due to this use of the function i need to find a way to recreate the result in the function as per sybase.
Test Table
Select
cd_details,
fl_parent_test
Into
@tx_test,
@fl_parent_test
From
test_ifo
Where
cd_test = @cd_test
;
If @@error <> 0 or @@rowcount <> 1 Then
Return(-1)
End If;
/* Parent Test */
If @fl_parent_test = 'Y' Then
Select
id_parent_ifo, nm_ifo /* 10/05/2005 */
Into
@id_parent_ifo, @nm_ifo /* 10/05/2005 */
From
ifo
Where
id_ifo = @id_ifo;
If @@error <> 0 or @@rowcount <> 1 Then
Return(-1)
End If;
End If;
/* Execute Test */
Set @cd_execute = 'Select if ' + @tx_test + ' then 1 else 0 endif into @nr_valid';
execute (@cd_execute);
return(@nr_valid)So i have two options:
- Recreate in CLR function
- Hard code the tests in the function.
- ??
Any Thoughts
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
It's a little difficult to know whether this would work form the limited sample shown, but could you create a table with the test name and one line per selection criterion? For example, cd_test would have three rows with a column named id_program and separate values of 1, 5 and 6. You could then write an in-line table-valued function to join to the table on the test name and id_program value. If present, the function would return an appropriate value. I appreciate you might have more complex rules but the above could be adapted suitably
-
Are all of the checks in this test_table based on variables only? If so - you might be able to generate a function based on the values in the table and hard-code each test and return value.
The generated function would look something like:
Create Or Alter Function dbo.get_nr_valid (
@tx_test varchar(100)
, @id_program int
, @id_ifo int
)
Returns smallint
As
Begin
Declare @nr_valid smallint = 0;
Select @nr_valid = Case When @tx_test = 'IS_UC' And @id_program In (1,5,6) Then 1
When @tx_test = 'IS_NOT_UC' And @id_program In (2,3,7) Then 1
...
When @tx_test = 'IS_CP_SPECIAL_ACCESS' And @id_ifo In (49,55,59,70,76,80) And @id_program = 2 Then 1
...
Else 0
End
Return @nr_valid;
EndThe code to generate the function would be something like this (remove the table variable as that is just to show how the generation works):
Declare @test_table Table (id_test_ifo int Identity(1,1) Primary Key Clustered, cd_test varchar(100), cd_details varchar(255));
Insert Into @test_table (cd_test, cd_details)
Values ('IS_UC', '@id_program in (1,5,6)')
, ('IS_NOT_UC', '@id_program in (2,3,7)')
, ('IS_CP', '@id_program = 2')
, ('IS_BA', '@id_program = 3')
, ('IS_CP_SPECIAL_ACCESS', '@id_ifo in (49,55,59,70,76,80) and @id_program = 2')
, ('IS_CP_NAAP4', '@id_ifo = 50 and @id_program = 2');
Select *
From @test_table tt;
Declare @sqlString varchar(max) = '
Create Or Alter Function dbo.get_nr_valid (
@tx_test varchar(100)
, @id_program int
, @id_ifo int
)
Returns smallint
As
Begin
Declare @nr_valid smallint = 0;
Select @nr_valid = Case ';
Select @sqlString += concat(iif(tt.id_test_ifo > 1, space(25), ''), 'When @tx_test = ', quotename(tt.cd_test, char(39)), ' And ', tt.cd_details, ' Then 1
')
From @test_table tt
Order By
tt.id_test_ifo;
Set @sqlString += ' Else 0
End
Return @nr_valid
End';
Print @sqlString;
--Execute @sqlString;This code would be setup as a stored procedure - and could be placed in a trigger on the test_table so that any changes made to the test table would rebuild/regenerate the function.
The final part would be to change your function to this:
/* Execute Test */
Set @nr_valid = dbo.get_nr_valid(@tx_test, @id_program, @id_ifo);The original function remains the same - gathering the necessary variables to be passed to the generated function. The generated function will need to be able to accept all required variables...but this should work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Yes the all the variable passed to the function all are used to identify and or used in the test. Hard coding was suggested but due to the churn of chnages and the level of maintennce it was been pushed to last in the list if optioons.
The issue would still be that you cant call a stored proc or another function from a function on built in or extended so
Set @nr_valid = dbo.get_nr_valid(@tx_test, @id_program, @id_ifo); would fail as you are calling a stored proc from the function.
-
Also this function is called 1000's of times a day and hard coding was identified early but the developers have recommended that another option be found as the business makes test changes regualrly, increasing the maintenance required.
-
You missed the point. The stored procedure would be called from a trigger on the table.
The stored procedure would generate the function and the function would be hard-coded based on the values in the table.
This would not require any code maintenance because the function is generated every time the table is updated.
You do need to consider how the table is updated to prevent SQL infection, but that is dependent on how the table is managed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hmmm Makes Sense. Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply