Invalid object name while running stored procedure

gmxoilav 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(175)

I have a stored procedure

alter PROCEDURE pricing.select_from_table @table_name NVARCHAR(255)
AS  
begin
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name);
    EXEC sp_executesql @sql;
END

and I run it through

EXECute [pricing].[select_from_table] 'pricing.pricingprod2023_nom3_copy2'

I get error Invalid object name 'pricing.pricingprod2023_nom3_copy2'.`

Even though table exists and I have permissions

I was trying to select all rows from a given database through sql stored procedure and I am expecting all the rows of the table name supplied

sql-server

来源：https://stackoverflow.com/questions/75521224/invalid-object-name-while-running-stored-procedure

2条答案

按热度按时间

3zwjbxry1#

QUOTENAME is for each part, not the entire path. QUOTENAME(N'MySchema.MyTable') results in N'[MySchema.MyTable]' which when injected into a dynamic statement results in the object named MySchema.MyTable on the USER s default schema, such as dbo : dbo.[MySchema.MyTable] .

If you need to provide both the schema and the object name, use 2 parameters:

ALTER PROCEDURE pricing.select_from_table @SchemaName sysname,
                                          @TableName sysname
AS
BEGIN
    DECLARE @SQL nvarchar(MAX);
    SET @SQL = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';';
    EXEC sys.sp_executesql @SQL;
END;

赞(0）回复(0）举报 2023-02-28

7xzttuei2#

Something like this can be a better version:

create or alter PROCEDURE spSelectStuff @table_name NVARCHAR(776)
AS  
begin
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM ' + CONCAT(QUOTENAME(PARSENAME(@table_name, 3)), '.', QUOTENAME(PARSENAME(@table_name, 2)), '.', QUOTENAME(PARSENAME(@table_name, 1)));
    EXEC sp_executesql @sql;
END

GO
-- Test code
exec spSelectStuff 'sometable'
exec spSelectStuff 'sys.objects'
exec spSelectStuff 'master.sys.objects'

It parses each table part and quotes it separately. I was a bit lazy to add linked server (parsename 4), but i have a feeling you should be able to figure it out if needed.

EDIT: Larnu's version that works from SQL Server 2017:

CREATE OR ALTER PROCEDURE dbo.spSelectStuff2 @table_name nvarchar(776)
AS
BEGIN
    DECLARE @sql nvarchar(MAX);
    SET @sql = N'SELECT * FROM ' + CONCAT_WS(N'.',QUOTENAME(PARSENAME(@table_name, 3)), QUOTENAME(PARSENAME(@table_name, 2)), QUOTENAME(PARSENAME(@table_name, 1)));
    EXEC sp_executesql @sql;

END;

赞(0）回复(0）举报 2023-02-28

我来回答

Invalid object name while running stored procedure

2条答案

相关问题

热门标签

最新问答