2

I have a couple of tables that can only be accessed using sybase. I need to write a query that will extract data from July to a date parameter specified (eg. 201507 - 201512). Once the date parameters has been inputted, I need to access tables on sybase with the names: ff_vv_201507_tt to ff_vv_201512_tt.

I though of perhaps having the yyyymm as a variable. I am not sure how to implement this as I am pretty new to SQL.

I have tried accessing one table from sybase but my code did not seem to work:

DECLARE @Table_Name sysname, @DynamicSQL nvarchar(4000)

SET @Table_Name = 'ff_vv_201507_tt'

SET @DynamicSQL =  'select 
*
FROM OPENQUERY(SADATABASE, 'select 
*
                       from  ' @Table_Name ' 
                                         )''

EXECUTE sp_executesql @DynamicSQL

Your help would be greatly appreciated.

Thank you

Hogan
  • 69,564
  • 10
  • 76
  • 117
Miss R
  • 23
  • 6
  • It would be helpful to know what Product and Version from Sybase you are using, as not all support Dynamic SQL. If you are unsure, use `select @@version` to find that information. – Mike Gardner Oct 29 '15 at 18:53
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) – Miss R Oct 30 '15 at 06:12
  • Then why would you put Sybase in the title and tags? – Mike Gardner Oct 30 '15 at 13:07
  • Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) – Ruud Helderman Oct 30 '15 at 13:17
  • I believe OP is executing on SQL Server, but additionally using OPENQUERY to pull some information from SyBase into SQL Server. – RBarryYoung Nov 06 '15 at 19:25
  • What error are you getting and have you tried the openquery without the dynamic sql using one of the tables you would query to make sure that it works? – HLGEM Nov 06 '15 at 19:27

2 Answers2

0

You can do it in a shell script with inline sql. This way, you will easily generate and pass your parameters to a stored proc, and also create dynamically your tables with the given parameter Example of such a script : 

$PARAM="XXXXX"
$PARAM2="YYYYY"
ret=`$ISQL -U$SQL_USER -P$SQL_PASSWD << __XXX
set nocount on
go
use mydatabase
go

DECLARE @batchId INT
select @batchId = 99
exec myStoreProc @batchId, "${PARAM}", "${PARAM2}"
go
__XXX`
RMS
  • 26
  • 2
0

If you want single quotes in your your strings you need to "escape" them by having two single quotes.

SET @DynamicSQL =  'select * FROM OPENQUERY(SADATABASE,''select * from '+@Table_Name+''')'

Why so complicated this should work fine.

SET @DynamicSQL =  'select * FROM ' +@Table_Name 

EXECUTE @DynamicSQL
Hogan
  • 69,564
  • 10
  • 76
  • 117