0

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.

Improve this question
5
  • 3
    It sounds like you want a PROCEDURE, not a FUNCTION. Details here. Commented Apr 19, 2018 at 14:57
  • Possible duplicate of Function vs. Stored Procedure in SQL Server Commented Apr 19, 2018 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. Commented Apr 19, 2018 at 15:01
  • Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From. Commented Apr 19, 2018 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. Commented Apr 20, 2018 at 18:22

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.