Select * from @TableName
-
Declare @idOrder as nvarchar(10) set @idOrder = '144' Declare @sInvoice as nvarchar(50) set @sInvoice = 'UK_B_IN000051' Declare @DateRange as nvarchar(100) set @DateRange = '28Jul2010' Declare @Country as nvarchar(10) set @Country = substring(@sInvoice, 1, 2) Declare @TableName as nvarchar(500) set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?
-
-
Declare @idOrder as nvarchar(10) set @idOrder = '144' Declare @sInvoice as nvarchar(50) set @sInvoice = 'UK_B_IN000051' Declare @DateRange as nvarchar(100) set @DateRange = '28Jul2010' Declare @Country as nvarchar(10) set @Country = substring(@sInvoice, 1, 2) Declare @TableName as nvarchar(500) set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?
It look to me that your database is a total mess. Why don't you have separate columns for region and date in one and the same table. Then you could make the query as simple as
Select * from Comersus where sInvoice = 'B_IN000051' and Country = 'UK' and Date = '28Jul2010'
where Date naturally shouldn't be a string but an actual Date
"When did ignorance become a point of view" - Dilbert
-
It look to me that your database is a total mess. Why don't you have separate columns for region and date in one and the same table. Then you could make the query as simple as
Select * from Comersus where sInvoice = 'B_IN000051' and Country = 'UK' and Date = '28Jul2010'
where Date naturally shouldn't be a string but an actual Date
"When did ignorance become a point of view" - Dilbert
-
Declare @idOrder as nvarchar(10) set @idOrder = '144' Declare @sInvoice as nvarchar(50) set @sInvoice = 'UK_B_IN000051' Declare @DateRange as nvarchar(100) set @DateRange = '28Jul2010' Declare @Country as nvarchar(10) set @Country = substring(@sInvoice, 1, 2) Declare @TableName as nvarchar(500) set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?
No matter how you work it you are not going to get what you want.
Select * from @TableName
The sames as this will not work
Select @ColumnName from Table
You are going to have to construct a string and execute it, it is called dynamic SQL and this is one of the few reason it is a valid solution. BTW I agree that your db looks ugly (I presume you are aware of that). Have you looked into partitioning, as I assume you have split the orders by country/date for performance reasons.
Never underestimate the power of human stupidity RAH
-
Mate, first of all, thanks for your reply, but if i can go the way that you mentioned, i wouldn't jump on internet, coz be realistic, a perfect database is a db that has no table. Hope you got my drift.
Well, I am curious why you need to have your database modeled that way. The best reason I can think of at the moment is: "It's always been like that and now it's to much work to change"
"When did ignorance become a point of view" - Dilbert
-
Declare @idOrder as nvarchar(10) set @idOrder = '144' Declare @sInvoice as nvarchar(50) set @sInvoice = 'UK_B_IN000051' Declare @DateRange as nvarchar(100) set @DateRange = '28Jul2010' Declare @Country as nvarchar(10) set @Country = substring(@sInvoice, 1, 2) Declare @TableName as nvarchar(500) set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?
-
hello you can use the exec function. like this: exec('select * from ' + @TableName) that execute a query and return the result set good luck
-
your alternatives are limited 1. create seperate SQL queries for all possibilities 2. Create an external application to overcome it 3. use dynamic SQL, Erlands Dynamic SQL article others might chip in other alternatives for you
As barmey as a sack of badgers
-
Declare @idOrder as nvarchar(10) set @idOrder = '144' Declare @sInvoice as nvarchar(50) set @sInvoice = 'UK_B_IN000051' Declare @DateRange as nvarchar(100) set @DateRange = '28Jul2010' Declare @Country as nvarchar(10) set @Country = substring(@sInvoice, 1, 2) Declare @TableName as nvarchar(500) set @TableName = 'splashdb..Comersus_' + @Country + '_Orders_' + @DateRange what i want is: select * from @TableName, but sql server returns me error. If someone in here can shed me a light would be really appreciated, or there is an alternative way to achieve the same goal?