Pass table name as a parameter to sql server
inline function
(emulation of the dynamic sql)
You can not do it simply:
CREATE FUNCTION dbo.wrong_function (@par1 varchar(80))
RETURNS TABLE AS RETURN (SELECT * FROM @par1 )
But you can emulate this behavior by using table value
function instead of the table in your inline function:
CREATE FUNCTION dbo.EmulationOfTheTables
(@par1 smallint)
RETURNS @table_variable TABLE
(firstcolumn sometype,
secondcolumn anothertype)
AS
BEGIN
if @par1=1
begin
INSERT INTO @table_variable
SELECT somefield,anotherfield FROM firstTable
end
else
begin
INSERT INTO @table_variable
somefield,anotherfield FROM secondTable
end
RETURN
END
CREATE FUNCTION dbo.good_function (@par1 smallint)
RETURNS TABLE AS RETURN (SELECT * FROM EmulationOfTheTables(@par1) )
Another dynamic sql emulation technique in the inline
function - using the case statement:
CREATE FUNCTION dbo.usingcase
(@par1 smallint )
RETURNS TABLE
AS
RETURN (
SELECT CASE @par1
WHEN 1 THEN first
WHEN 2 THEN second
END as chosenfield
FROM sometable_or_function )
Combining both of these technic you can emulate various
dynamic sql queries.
|