January 20, 2013 at 7:33 pm
I have written my first stored procedure to try to pass a table name in a variable to SSRS.
USE XXXX
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure link_table_sp
@link varchar(50) output
AS
Begin
set
@link = (SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables
where name like '%link%')
END
Then in SSRS
EXEC link_table_sp('SELECT * FROM' + @link)
then try to call it in SSRS
EXEC link_table_sp('SELECT * FROM' + @link)
But it says incorrect syntax near select.
January 20, 2013 at 8:53 pm
When I try execute the SP I get an error message.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Yeah I do want more than one value, there are many tables with '%link%'. Then I want to be able to see the rows, for that table.
January 21, 2013 at 1:53 am
have you tried putting name in your derived table?
@link = (SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables
where name like '%link%') as "table_name"
January 21, 2013 at 4:45 am
@link is a varchar(50) variable and you are trying to populate it with multiple rows from your result set. If you only want one result row in the variable you could use SELECT TOP 1 . . . or use a table variable or inline table value function instead of the varchar if you want it to hold several records.
March 25, 2013 at 12:45 pm
When you use 'like' you are opening the door to multiple rows being fetched. Why could you not pass the exact table name? Your issue may be more upstream.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply