First Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » First Stored Procedure

First Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

ringovski

Posted Sunday, January 20, 2013 7:33 PM

Valued Member

Group: General Forum Members
Last Login: Monday, March 11, 2013 8:29 PM
Points: 55, Visits: 229

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.

Post #1409335

ringovski

Posted Sunday, January 20, 2013 8:53 PM

Valued Member

Group: General Forum Members
Last Login: Monday, March 11, 2013 8:29 PM
Points: 55, Visits: 229

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.

Post #1409342

eklavu

Posted Monday, January 21, 2013 1:53 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:38 AM
Points: 53, Visits: 150

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"

Post #1409420

Andre Ranieri

Posted Monday, January 21, 2013 4:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 4:56 PM
Points: 277, Visits: 201

@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.

Post #1409480

mmartin1

Posted Monday, March 25, 2013 12:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 194, Visits: 643

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.

Post #1435094

« Prev Topic | Next Topic »

Permissions