Help with sp_excuteSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with sp_excuteSQL

Author

Topic

jlenon
Starting Member

6 Posts

Posted - 2009-03-23 : 10:35:05

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,' +@rFunction+ ',' +''+@RuleName+'' +
'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

jlenon
Starting Member

6 Posts

Posted - 2009-03-23 : 10:38:34

Sorry for the error above in the SET @ExecSQL statement...
@rFunction should be @Function and @RuleName should be @FunctionDESC..

Hope that helps..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources