none
stored procedure

    Question

  • Hi all,

    i was created a simple stored procedure like this..

    CREATE PROCEDURE sp_date @table varchar(50),@column varchar (20),@From int,@To int
    AS
     SELECT * FROM @table
    WHERE @column BETWEEN (DATEDIFF(second, '1970-01-01 00:00:00','@from')) AND (DATEDIFF(second, '1970-01-01 00:00:00','@to'))
    GO

    if i create the above procedure i get the error

    Must declare the table variable "@table"

    Wednesday, September 25, 2013 6:02 AM

Answers

  • you can use dynamic sql instead

    Create PROCEDURE sp_date  
    	@table varchar(50),
    	@column varchar (20),
    	@From varchar(10),
    	@To varchar(10)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        declare @SqlString as nvarchar(max)
    
    	SET @SqlString = N'SELECT * FROM ' + @table + ' WHERE '+@column+' = BETWEEN (DATEDIFF(second, ''1970-01-01 00:00:00'',''' + @from + ''')) AND (DATEDIFF(second, ''1970-01-01 00:00:00'','''+@to+'''))'
    
    	print @SqlString 
    END
    GO
    




    Please remember to 'Mark as Answer' the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    • Edited by Ahmed-Samy Wednesday, September 25, 2013 6:35 AM
    • Marked as answer by Mohan DBA Wednesday, September 25, 2013 6:56 AM
    Wednesday, September 25, 2013 6:31 AM

All replies

  • CREATE PROCEDURE sp_date @table varchar(50),@column varchar (20),@From int,@To int
    AS
     SELECT * FROM @table
    ...

    Must declare the table variable "@table"

    Hello,

    You can't use a variable as object name, here as a table name in the SELECT statement. For this you would have to use dynamic SQL.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 25, 2013 6:13 AM
  • It looks like @table you declared as Varchar datatype, But inside procedure, you treat as Table/TableVariable.

    Could you please let us know @Table is a table variable or varchar variable? Or Are you tryin a Table Value Parameter?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 25, 2013 6:14 AM
  • You need to run Dynamic Sql if you want to pass a Temp table name to the SP.

    Many Thanks & Best Regards, Hua Min

    Wednesday, September 25, 2013 6:14 AM
  • you can use dynamic sql instead

    Create PROCEDURE sp_date  
    	@table varchar(50),
    	@column varchar (20),
    	@From varchar(10),
    	@To varchar(10)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        declare @SqlString as nvarchar(max)
    
    	SET @SqlString = N'SELECT * FROM ' + @table + ' WHERE '+@column+' = BETWEEN (DATEDIFF(second, ''1970-01-01 00:00:00'',''' + @from + ''')) AND (DATEDIFF(second, ''1970-01-01 00:00:00'','''+@to+'''))'
    
    	print @SqlString 
    END
    GO
    




    Please remember to 'Mark as Answer' the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    • Edited by Ahmed-Samy Wednesday, September 25, 2013 6:35 AM
    • Marked as answer by Mohan DBA Wednesday, September 25, 2013 6:56 AM
    Wednesday, September 25, 2013 6:31 AM
  • Hi Mohan

    PFB 2 option

    -- Using Exec
    Create PROCEDURE sp_date @table varchar(50),@column varchar (20),@From int,@To int
    AS
    Declare @sql varchar(400) = ''
    select 'SELECT * FROM '+@table +' WHERE ' + @column + ' BETWEEN (DATEDIFF(second, ''1970-01-01 00:00:00'','''+
    ''','+convert(varchar,@from)+')) AND (DATEDIFF(second, ''1970-01-01 00:00:00'','+convert(varchar,@to)+'))'
    print @sql
    --Exec (@sql)
    -- using sp_executesql 
    Alter PROCEDURE sp_date @table varchar(50),@column varchar (20),@From int,@To int
    AS
    Declare @stringsql varchar(400) = ''
    Declare @paramdef = '@param1 varchar(50),@param2 varchar (20),@param3 int,@param4 int'
    select @stringsql = 'SELECT * FROM @param1  WHERE  @param2  BETWEEN (DATEDIFF(second, ''1970-01-01 00:00:00'',@param3 )) 
    AND (DATEDIFF(second, ''1970-01-01 00:00:00'',param4))'
    print @stringsql
    EXEC sp_executesql @stringsql, @paramdef, @param1 = @table, @param2 = @column, @param3 = @From, @param4 = @To
    --Exec (@sql)

    you can also use table-valued parameter http://msdn.microsoft.com/en-us/library/bb675163.aspx

    but again you need to execute dynamically because you are specifing the column name dynamically.

    Thanks

    Saravana Kumar C

    Wednesday, September 25, 2013 6:48 AM