2019年10月7日 星期一

Creating Stored Procedure with TableName as parameter

ALTER PROCEDURE dbo.TableSelect -- always use schema prefix!
    @TableName NVARCHAR(512) -- this must be a Unicode data type!
AS
BEGIN
    SET NOCOUNT ON;

    -- should at least validate that the @TableName is valid
    -- which can at least somewhat help avoid SQL injection
    IF OBJECT_ID(@TableName) IS NULL
    BEGIN
      RETURN;
    END

    DECLARE @String NVARCHAR(4000);-- should always use Unicode here too
        -- and always use semi-colons

    SET @String = N'Update ' + @TableName + N' Set Internet = 1';
        -- needed a space here ---------------^
        -- which PRINT @String would have shown you.

    EXEC sys.sp_executesql @String;
        -- sp_executesql preferred over EXEC()
END
GO

from : https://dba.stackexchange.com/questions/105705/creating-stored-procedure-with-tablename-as-parameter

沒有留言:

張貼留言