Link to home
Start Free TrialLog in
Avatar of woyler
woyler

asked on

function to return table name

From a stored procedure, can I call a custom function that will return the table name I want to use in my SQL syntax?

thanks in advance,
Bill
ALTER FUNCTION [GetTableName](@Criteria VARCHAR(1))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Output varchar(50)
SET @Output = ''
 
SELECT @Output = 
CASE WHEN @Criteria = 'A' THEN 'TABLE_A'
WHEN @Criteria = 'B' THEN 'TABLE_B'
WHEN @Criteria = 'C' THEN 'TABLE_C'
END
 
RETURN @Output
END
 
 
 
Create PROCEDURE [dbo].[sp_TestStoreProc]
 SELECT TOP 1 * FROM + GetTableName('B') 
END

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

have to change your proc around bit:

Create PROCEDURE [dbo].[sp_TestStoreProc]
BEGIN
declare @x nvarchar(3000)
set @x = ' SELECT TOP 1 * FROM '+ GetTableName('B')
exec sp_executesql @x
END
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of woyler
woyler

ASKER

Perfect.  Thank you for the quick response.