PLEASE Help with Scalar Functions!!

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

SQL Server 2005 Forums

Transact-SQL (2005)

PLEASE Help with Scalar Functions!!

Author

Topic

jlenon
Starting Member

6 Posts

Posted - 2009-03-20 : 12:33:34

Hello,
I'm in desperate need for help with my issue.
The issue:
I have a SQL Server 2005 table with scalar functions in it.
For example the column may look like this:
fRle_CheckValue(prodID,date)

I need to execute or call this function for every row in a different table with one4 of the parameters changing each time...
Here's an example of what I've done thus far....

--CREATE table varible to store functions
DECLARE @Functions Table (rownum int IDENTITY (1, 1) Primary key NOT NULL, RuleName varchar(75),Function_Name Varchar(100) )
INSERT INTO @Functions
Select 'dbo.'+RuleExpression,RuleName
From Rules.dbo.[rule]
Where GroupID = '37' and RuleExpression like 'frule%'

--The function looks like this in the table fRule_ProdCheck(par1,par2)
CREATE TABLE Function_Results
(
PropID int,
FunctionViolated varchar(50),
FunctionName varchar(150)
)

--CURSOR
DECLARE Function_Cursor CURSOR FOR SELECT Function_Name,RuleName
FROM @Functions

OPEN Function_Cursor FETCH NEXT FROM Function_Cursor
INTO @rFunction,@RuleName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @ExecSql = ('INSERT INTO Function_Results (PropID,FunctionViolated,FunctionName)' + 'SELECT NCREIFPropertyID,' +@rFunction+ ',' +@RuleName+ 'FROM PropertyData WHERE ReportingPeriod = '+@ReportingPeriod

PRINT @ExecSql
EXEC @ExecSql

IF @@RowCount <= 0
PRINT 'NO RESULTS FOR' + @RuleName

FETCH NEXT FROM Function_Cursor
INTO @rFunction,@RuleName
END

--End Cursor
CLOSE Function_Cursor
DEALLOCATE Function_Cursor

SELECT * FROM @Functions
SELECT * FROM Function_Results

-----------------------------------------------------------

My main problem is assigning the parameters within the functions so that they can run...

PLEASE,PLEASE..Somebody help me!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:39:32

what does PRINT @ExecSql return?

jlenon
Starting Member

6 Posts

Posted - 2009-03-20 : 13:07:13

This is the error(s) thats returned:
Conversion failed when converting the nvarchar value 'INSERT INTO Function_Results (NCREIFPropertyID,FunctionViolated,FunctionName)SELECT NCREIFPropertyID,Ending Market Value change over 50%,dbo.fRULE_MarketValue( PropID,ReportingPeriod )FROM PropertyData WHERE ReportingPeriod = 12/31/2008AND NCREIFPropertyID = ' to data type int.

The issue is assigning (PropID & ReportingPeriod) parameters within the function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 13:10:52

whats this Ending Market Value change over 50%? is it field or string value?
also error occurs because you havent casted integer variable having NCREIFPropertyID value to varchar

jlenon
Starting Member

6 Posts

Posted - 2009-03-20 : 13:23:36

Ending Market Value change over 50% is a field assigned to a varible to describe the function.

This is probably a better way of describing my problem:
DECLARE @ReportingPeriod varchar(10)
DECLARE @rFunction varchar(100)

SET @rFunction = 'fRULE_MarketValue( PropID,ReportingPeriod )'
SET @ReportingPeriod = '12/31/2008'

DECLARE Function_Cursor CURSOR FOR
SELECT Function_Name,RuleName
FROM @Functions --This is where the function(s) are stored..

OPEN Function_Cursor
FETCH NEXT FROM Function_Cursor
INTO @rFunction,@RuleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ExecSql = ('INSERT INTO Function_Results (NCREIFPropertyID,FunctionViolated,FunctionName)' +
'SELECT PropID,' +@rFunction+ ',' +@RuleName+
'FROM PropertyData WHERE ReportingPeriod = '+@ReportingPeriod

Since @rFunction = dbo.fRULE_MarketValue( PropID,ReportingPeriod ),
I need to be able to make PropID = @PropID and ReportingPeriod = @ReportingPeriod and assign them and execute them for every row in the table.

I hope this explains it more clearer and I also really appreciate your help..

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 13:27:35

then what you need is to use sp_executesql rather than EXEC

http://msdn.microsoft.com/en-us/library/ms175170(SQL.90).aspx

jlenon
Starting Member

6 Posts

Posted - 2009-03-20 : 13:31:50

Ths looks like it may work...I'll give it a try now and let you know..

MANY THANKS 'visakh16'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 13:37:50

welcome