Eng | Rus
 
Education -> Solutions -> SQLline
 
 
 
Simple Seo Solutions
SEO and Promotion
sites
VoiceNotebook.com
Speech to text for AndroidAndroid speech to text

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.

© prjsoft.ru