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.