FUNCTION TO RETURN TABLE DEPENDING ON PARAMETERS PASSED 1

[foreveryoung]

Please can you help me with a function I am trying to create in TSQL.

I want the results of different queries returned depending on the parameter I pass to the function.

ALTER function [mercerd].[FN_Endoscopy_E01_QuarterlyCensus] (@lineid char(3))
   returns table
   as
   return 
	(
		IF @LINEID = '123' 
			BEGIN
				select * FROM TABLE1
			END
		ELSE
			BEGIN
				SELECT * FROM TABLE2
			END
		END
    )

The query seems to break at the "if" statement so I guess my syntax is wrong.

I am sure its an easy solution but havent been able to resolve.

Thanks
David

 

[ca8msm]

Have a look at the help file.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[foreveryoung]

Thanks for your reply, I cannot see anything wrong with my IF syntax after checking the help file. Is this because its function and not a USP?

 

[nice95gle]

The problem is not with the IF.
You are trying to mix a inline function with a multi statement function.

To get your code to work you should do something like this:

[COLOR=blue]ALTER[/color] [COLOR=#FF00FF]function[/color] [mercerd].[FN_Endoscopy_E01_QuarterlyCensus] (@lineid [COLOR=blue]char[/color](3))
   RETURNS @tbl [COLOR=blue]table[/color] (col1 [COLOR=blue]varchar[/color] (50), col2 [COLOR=blue]int[/color], col3 [COLOR=#FF00FF]datetime[/color], etc...)
   [COLOR=blue]AS[/color]
	[COLOR=blue]BEGIN[/color]
		
     [COLOR=blue]IF[/color] @LINEID = [COLOR=red]'123'[/color] 
	  [COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tbl 
	      [COLOR=blue]select[/color] * [COLOR=blue]FROM[/color] TABLE1
        [COLOR=blue]ELSE[/color]
          [COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tbl 
	   [COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] TABLE2
[COLOR=blue]RETURN[/color]
[COLOR=blue]END[/color]

Do a little reading on creating UDF's to fully understand what I am saying.


Well Done is better than well said
- Ben Franklin