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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy