Store Procedure to copy table using dynamic query in SQL server-CodePudding

I want to create store procedure for coping table using dynamic query. I followed this step for creating store proceduce, link:-https://stackoverflow.com/questions/8698231/sql-server-stored-procedures-to-copy-tables

but I got an error:- Could not find stored procedure 'Select * into tblDetail from salesDetail'.

Here is my code:-

alter procedure sp_copyOneTableToAnother 
@newtable nvarchar(50),
@oldtable nvarchar(50)
As
Begin
Declare @sql nvarchar(1000)
    set @sql=   'Select * into ' @newtable 
                ' from ' @oldtable
    
    exec @sql
End

exec sp_copyOneTableToAnother @newtable='tblDetail',@oldtable='salesDetail'

Store procedure was created from above syntax but while calling sp_copyOneTableToAnother store procedure it gave me an roor. Please help me solve it.

CodePudding user response：

There are several problems here, first, your procedure name starts with sp_, which is reserved by Microsoft for Special / System Procedures. That should go.

Next, your parameter types are wrong; the correct data type for an object is a sysname, a synonym of nvarchar(128) NOT NULL, not varchar.

Next, the injection issue; you blindly inject the values of your parameters into your statement and hope that said values aren't malicious. Validate the value of @oldtable and properly quote both parameters.

Finally, the execution should be done by sp_executesql; not using it promotes further injection issues as you can't parametrise EXEC (@SQL) statements.

You also don't define your schemas, which you really should be. I add them as NULLable parameters here, and get the USER's default schema

This results in something like this:

CREATE OR ALTER PROCEDURE dbo.CopyOneTableToAnother @NewTable sysname,
                                                    @OldTable sysname,
                                                    @NewSchema sysname = NULL,
                                                    @OldSchema sysname = NULL AS
BEGIN

    SET NOCOUNT ON;

    SELECT @NewSchema = ISNULL(@NewSchema,default_schema_name),
           @OldSchema = ISNULL(@OldSchema,default_schema_name)
    FROM sys.database_principals
    WHERE name = USER_NAME();

    DECLARE @SQL nvarchar(MAX);

    SELECT @SQL = N'SELECT * INTO '   QUOTENAME(@NewSchema)   N'.'   QUOTENAME(@NewTable)   N' FROM '   QUOTENAME(s.[name])   N'.'   QUOTENAME(t.[name])   N';'
    FROM sys.schemas s
         JOIN sys.tables t ON s.schema_id = t.schema_id
    WHERE s.name = @OldSchema
      AND t.[name] = @OldTable;

    EXEC sys.sp_executesql @SQL;
END;

db<>fiddle