PLEASE Help with sp_ExecuteSQL!!!!

Question

PLEASE Help with sp_ExecuteSQL!!!!

jlenon-862863

SSC Rookie

Points: 27
More actions
March 23, 2009 at 9:37 am

#132170

Hello SQL Guru's;
I need help with using sp_excuteSQL to call a function from a table along with assigning two parameters that's within the function.
Here's what the function looks like in the table:
dbo.fRULE_CheckMV(PropID,Date).
I need to call this function for each row in another table. Here's where I'm at with this:
DECLARE @Function varchar(20)
DECLARE @FunctionDesc varchar(50)
DECLARE @ExecSql varchar(200)
CREATE TABLE Function_Results
(
PropID int,
FunctionViolated bit,
FunctionDesc varchar(50)
)
-- HERE's THE CURSOR
DECLARE Function_Cursor
CURSOR FOR SELECT Function_Name,FunctionDesc
FROM Functions --(This is the table w/ the functions in it)
OPEN Function_Cursor
FETCH NEXT FROM Function_Cursor INTO @FunctionDesc,@Function
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ExecSql = ('INSERT INTO Function_Results (PropID,FunctionViolated,FunctionDESC)' + 'SELECT PropID,' +@Function+ ',' +''+@FunctionDesc +'' +
'FROM PropData WHERE ReportingPeriod = '+ ''+@Date+''
PRINT @ExecSql
EXEC @ExecSql
FETCH NEXT FROM Function_Cursor INTO @FunctionDesc,@Function
I was told to use sp_executeSQL to do this.....but I can't figure out how to get sp_executeSQL to pass the values to the parameters of the function. Any help would be greatly appreciated..
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is there a reason to do this with a cursor instead of with Cross Apply?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jlenon-862863 SSC Rookie Points: 27 More actions · Answer 2

Thanks for your reply gSquared...

I've never used 'CROSS APPLY' because I thought it was used to join a table to a table-valued-function.

How would I use it in my instance?

Thanks in advance

GSquared SSC Guru Points: 260824 More actions · Answer 3

What are your functions returning?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jlenon-862863 SSC Rookie Points: 27 More actions · Answer 4

The functions are returning a bit (1 or 0)

The desired results :

PropID FunctionViolated FunctionName

1234 1 MarketValue is to low

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 5

It looks like you want to apply each function to every row in PropData.

I guess there is a column in PropData to pass as a parameter??

If you only have a few functions you could roll them into a CASE statement, like this:

INSERT Function_Results (PropId, FunctionViolated, FunctionDesc)

SELECT pd.PropId,

CASE

WHEN f.Function_Name = 'fn1' THEN fn1(pd.PropValue)

WHEN f.Function_Name = 'fn2' THEN fn2(pd.PropValue) END,

f.Function_Desc

FROMFunctions f

CROSS JOIN PropData pd

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

jlenon-862863 SSC Rookie Points: 27 More actions · Answer 6

THANKS!!!!!

I'll give that a try...

But the functions will be selected from a specific group of functions that may vary at runtime.

This means that I can't hardcode the functions right?

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

[font="Verdana"]What is your system doing that it needs to provide the ability to run dynamic functions?

I can think of worse things, because there's certainly a limit to what functions can do (so would limit the issues with SQL injection attacks, for example). But all the same, is this really a facility you need to provide?

[/font]