Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query from Variable

Yanıt Query from Variable

  • Sunday, January 20, 2013 10:09 PM
     
      Has Code
    Sign In to Vote
    0

    Hi All,

    In SSRS05 I have two datasets;

    --Table;
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables 
where name like '%link%'
order by name

--Data;

declare @table varchar(50)

SELECT *
FROM @table

    @table is declared as string parameter in the report parameters and from available values from query table.

    But when I try running the report it says must declare variable @table.


     

All Replies

  • Sunday, January 20, 2013 10:16 PM
     
      Has Code
    Sign In to Vote
    0

    Hello Cameronh,

    This kind of dynamically table name assignment don't work, neighter in SSRS nor in SQL/SSMS.

    You would have to use dynamic SQL like 

    EXEC sp_executeSQL(N'SELECT * FROM ' + @table);

    Olaf Helper

    Blog Xing


     
  • Sunday, January 20, 2013 10:30 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Try this one:

    declare @table table (schema2 varchar(max));
insert into @table(schema2) SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables 
where name like '%link%'
select * from @table

    Share the results please.

    Cheers

    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

     
  • Sunday, January 20, 2013 10:41 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Try this one:

    declare @table table (schema2 varchar(max));
insert into @table(schema2) SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables 
where name like '%link%'
select * from @table

    Share the results please.

    Cheers

    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP


    Yeah this runs and returns the listing of all the tables where %link%, but I am trying to get all the rows from the table I select where %link%.
     
  • Sunday, January 20, 2013 11:59 PM
     
      Has Code
    Sign In to Vote
    0

    created my first stored procedure.

    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 try to call it in SSRS

    EXEC link_table_sp('SELECT * FROM' + @link)



    But it says incorrect syntax near select.
    • Edited by Cameronh Monday, January 21, 2013 3:06 AM
    • Edited by Cameronh Monday, January 21, 2013 3:07 AM
    •  
     
  • Monday, January 21, 2013 3:52 AM
     
     
    Sign In to Vote
    0

    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.

     
  • Monday, January 21, 2013 10:35 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Hi Cameronh,

    Please try the code below:

    CREATE TYPE TBL AS TABLE 
( SchemaName VARCHAR(50) );
GO
CREATE PROCEDURE RetreiveSchema
@name varchar(50)
AS 
SET NOCOUNT ON
SELECT SCHEMA_NAME(schema_id)+'.'+name
AS SchemaTable
FROM sys.tables 
where name like @name


exec dbo.RetreiveSchema @name = '%link%'
    Cheers

    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP



     
  • Monday, January 21, 2013 10:24 PM
     
     
    Sign In to Vote
    0

    Thanks for the reply SoheilBakhshi

    I also worked out i need a table variable. Can you please tell me why you haven't declared it inside the stored procedure and secondly why the @name variable is not a output variable?

    • Proposed As Answer by SoheilBakhshi Tuesday, January 22, 2013 7:49 AM
    • Unproposed As Answer by SoheilBakhshi Tuesday, January 22, 2013 7:49 AM
    •  
     
  • Tuesday, January 22, 2013 8:02 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi,

    I've copied the first 3 lines mistakenly.

    Sorry for confusing.

    The code is:

    CREATE PROCEDURE RetreiveSchema
@name varchar(50)
AS 
SET NOCOUNT ON
SELECT SCHEMA_NAME(schema_id)+'.'+name
AS SchemaTable
FROM sys.tables 
where name like @name


exec dbo.RetreiveSchema @name = '%link%'

    Cheers

    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP