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.
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.