Creating SQL Function to Create Multiple Tables

Question

I am currently moving a SAS process to SQL. Within the SAS process, I leverage macros to create a multitude of tables.

I am trying to leverage the CREATE FUNCTION function within SQL to mimic this process, however I am stuck. I have three arguments, the server name, the name of the new table and the table where it should select from. I'm not sure what I should specify as what I am returning as I'm not looking to return anything, just create tables.

CREATE FUNCTION dbo.functionname (@server VARCHAR(250), @name VARCHAR(250), @table VARCHAR(250))
RETURN (???)
AS BEGIN
SELECT *
INTO @server.dbo.@nm
FROM @table
RETURN
END

This is what I have come up with so far. My SELECT statement wouldn't actually be *, I just put that for simplicity sake for this question.

UPDATE: In this instance, using a stored procedure is not an option as permissions have been limited.

It sounds like you want a PROCEDURE, not a FUNCTION. Details [here](https://stackoverflow.com/q/1179758/2144390). — Gord Thompson, Apr 19 '18 at 14:57
Possible duplicate of [Function vs. Stored Procedure in SQL Server](https://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server) — underscore_d, Apr 19 '18 at 15:01
You will want to use a stored procedure for this. Also, since you're creating a table from a table, you should use a `SELECT INTO` to create your table. — Rick S, Apr 19 '18 at 15:01
Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Apr 19 '18 at 17:04
I have updated my question, to clarify I need to find a solution without using a stored procedure as permissions have not been given to use them. — , Apr 20 '18 at 18:22

score 1 · Answer 1 · answered Apr 19 '18 at 15:08

You can create a dynamic SQL script as follows

declare @newtable sysname = 'T003', 
        @sourcetable sysname = 'sys.tables'

declare @sql nvarchar(max)
set @sql = N'select * into ' + @newtable + ' from ' + @sourcetable + ';'
set @sql = @sql + N'select * from ' + @newtable 
exec sp_executesql @sql

Then you can use it in a stored procedure To return data from new table, the table type must be known before. In this case it is not possible, so developer cannot create the function return type Or create a function just to create the table and insert data into it. But return fail or success, etc Then select from the new table using a dynamic SQL again

Creating SQL Function to Create Multiple Tables

1 Answers1