hmmm,

You cannot use BEGIN TRY.... END TRY OR EXECUTE inside function.

MartinaJ

>OK, can I use EXECUTE (without the TRY)? if not, how do I build a select statement from the input variables?
>
>>Hi Rollin,
>>
>>You cannot use BEGIN TRY.... END TRY and EXECUTE inside function.
>>
>>MartinaJ
>>
>>>I am trying to pass 4 variables into a table valued function, perform a select statement and have it return the results. the variables are the
>>>column name, table name, index name, and index value. I cant seem to get the syntax right.
>>>
>>>

>>>CREATE FUNCTION LOOKUP_SDL
>>>(
>>>   @COLUMN_NAME   VARCHAR(500),
>>>   @TABLE_NAME    VARCHAR(500),
>>>   @IND_NAME    VARCHAR(500),
>>>   @IND_VALUE    VARCHAR(500)
>>>)
>>>RETURNS TABLE
>>>AS
>>>RETURN 
>>>(
>>>   DECLARE @Stmt  VARCHAR(500);
>>>   SET @Stmt = ('SELECT '+@COLUMN_NAME+' FROM '+@TABLE_NAME+' WHERE '+@IND_NAME+' = '''+@IND_VALUE+'''')
>>>   BEGIN TRY
>>>      EXECUTE (@Stmt);
>>>   END TRY
>>>   BEGIN CATCH
>>>      SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessge;
>>>   END CATCH
>>>);
>>>GO
>>>

>>>
>>>The DECLARE statement seems to be the beginning of the problem, but Im not sure how else to write it. ... All help is appreciated.