Define a function with a sproc

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 2000 Forums

Transact-SQL (2000)

Define a function with a sproc

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-07-26 : 09:44:14

Hi Reader,

I have an asp page which builds an sql query. I want to try and move this across to a sproc as I want to make use of temporary tables. I have lots of simple functions on my asp page. Can you define functions within a sproc. Below is one I have copied from asp.

Function FunctionCompareTo (CompareTo)
	
	If CompareTo = 1 Then 'Compare to target
		If Report_GraphYAxis = 0 Then CreateSelect ("AVG (TargetRating) as [Target]") '%Target
		If Report_GraphYAxis = 1 Then CreateSelect ("AVG (TargetScore) as [Target]") 'ActualTarget			
	End If
	
	If CompareTo = 5 Then 'High Low
		If Report_GraphYAxis = 0 Then CreateSelect ("MIN (ActualRating) as [Min]")
		If Report_GraphYAxis = 0 Then CreateSelect ("MAX (ActualRating) as [Max]")
		If Report_GraphYAxis = 1 Then CreateSelect ("MIN (ActualScore) as [Min]")
		If Report_GraphYAxis = 1 Then CreateSelect ("MAX (ActualScore) as [Max]")
	End If
End Function

Call FunctionCompareTo(5)

Thanks

Leah

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-28 : 01:43:35

????
A function is a permanent object like a stored procedure. You create it before the sp and call it from the sp. Don't try to change them (or tables/views etc.) at run time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources