Variables In The FROM (table Name) Clause

Jul 20, 2005

I have a problem with executing following T-SQL select query.

My select statement looks like this

SELECT * FROM (@TableName_FirstPart + @TableName_secondPart)

**@TableName_FirstPart & @TableName_SecondPart are Local variables**

Im getting Incorrect syntax error for this statement.

Following is the error message:
"Server: Msg 170, Level 15, State 1, Line 55
Line 55: Incorrect syntax near '+'."

Is it possible to construct table names in the FROM clause dynamically ?

Thanks in advance

View 2 Replies


ADVERTISEMENT

Variables In Where Clause

Aug 20, 2013

SQL logic, and I've been working (and researching) this all day with zero success..My goal is to try an pass a variable from an ASP page to a stored procedure, which is utilizing the variable as criteria for a column_name in the where clause.So for example (a simplified version of my query):

@strDept nvarchar(10), @strUser nvarchar(30)
-- the asp page will pass f19 to @strDept
-- the asp page will pass the logged in username to @strUser

select x, y, z from table1 where @strDept in (@strUser)

The stored procedure does execute, but it returns no values. Is that because the where variable has no data at compile time? If not, I can't think of any reason why this is not working. (I have tried a case statement, before the select to set the variable too and that returned the same result.)

View 19 Replies View Related

SQL 2012 :: SSIS And Variables With Where Clause

Jan 15, 2015

I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.

I used a select from a central table to get the connection strings, which is put into [User::Server2]

1) execute SQL task to collect the port

(DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =

'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
)

Which is then put into a variable @Portnumber

2) I then need to insert this into a certain server with a where clause including another variable

so something like
"INSERT INTO DBO.InstanceConfig VALUES ('"+@[User::Portnumber]+"') where Serverinstance = ('"+@[User::Server2]+"')

but it doesnt work like that.. is there an easier way

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

Order By Clause With Variables In A Stored Procedure

Mar 24, 2004

Hi,
I need to include two input variables
in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me
to do so and gives an Error 1008.
How can i solve this problem?

Thanks for your help!!

View 6 Replies View Related

SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?

Oct 11, 2014

I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'

Below is Query 1:

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar

Out put of this query is 'Noname'

BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar

View 4 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Update One Table When Records Inserted In Another Table - Variables In Trigger

May 19, 2014

I am trying to update one table when records are inserted in another table.

I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.

My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:

ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)

[Code] ....

View 4 Replies View Related

Table Variables

Feb 2, 2006

Hello,I am writing a function that uses two table variables. The structures ofboth are shown here:DECLARE @workdates TABLE (conflict CHAR(1),workdate SMALLDATETIME)DECLARE @existing TABLE (workdate SMALLDATETIME)I need to do an update on the first table:UPDATE @workdatesSET conflict = 'X'FROM @existing sWHERE workdate = s.workdateI am concerned that the unqualified 'workdate' in the WHERE clause willgive me an ambiguous column reference. Is this SQL statement valid?Thanks,Andrew

View 3 Replies View Related

TABLE Variables

Jul 20, 2005

Hey everyone,I read in a SQL Server book that you can now create a tablevariable.DECLARE @TMP TABLE (list of fields)Then you can you can use the statementINSERT INTO @TMPSELECT (whatever).I've tried it and it works. The book also says that youshould be able to pass these variables between storedprocedures and functions. Problem is, when I try todeclare the variable at the top of the procedure, thesyntax checker hates it.Anyone else out there try this out?SAM

View 1 Replies View Related

Table Variables

May 9, 2007

Will reporting services allow the use of table variables in the SQL query?

View 1 Replies View Related

Table Variables

Jun 11, 2007

I want to querry a table based on the input of a form, I have tables Monday, Tuesday ect., and want to display the records for Monday if the user selects Monday in a menu. I need to delcare a table name but am not sure how?

DELCARE @myVar char(20)
SET myVar = Request.Form("select2");



"SELECT * FROM @myVar"



View 2 Replies View Related

Table Variables

Apr 28, 2008



I know user defined global table variables are not allowed in sql. I'm trying to avoid using temporty tables for speed reasons. I have a function in which a table variable is defined, and a function within that function that needs to call that table variable. Any ideas?

Thanks

View 1 Replies View Related

Table Variables/Table Reference - Is This Possible?

Feb 28, 2005

Is it possible to do something like this in SQL:

DECLARE @TABLE table

if @GOOD = 1 BEGIN
Set @Table = Table1
END ELSE BEGIN
Set @Table = OtherTable
END

SELECT * FROM @TABLE

View 1 Replies View Related

Table Variables And Calculations

May 4, 2007

 So, I've got a problem with using table variable "fields" and a simple variable in calculations. It ain't workin'. See the bolded code below. When I run the SP, it returns 0 for those values. Anyone got any clues? Is this a table variable limitation?  ALTER PROCEDURE YearlyTotalsInPercentages(@Year int) ASBEGINDECLARE @TotalSum intDECLARE @Totals TABLE
(
CBDCYearlyTotals int, ProductLine varchar(50))INSERT INTO @Totals (CBDCYearlyTotals, ProductLine)SELECT SUM(dbo.Main.Hours), dbo.Project.ProductLineFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, @TotalSum AS TotalSum, ROUND((t.CBDCYearlyTotals/@TotalSum) * 100, 1) AS Percentage FROM @Totals tEND
GO
  Thanks Yall

View 1 Replies View Related

Qualifying Table Variables

Jan 6, 2004

hi all!

I am using table variables instead of creating a temp table because it seems to be faster

But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?

ex: with temp table

create table #tmp.... (F1...)
#tmp.f1

with table variable

declare @temp table(...
@table.f1 - can´t do it

the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name


thank to all and happiness for all 2004

View 2 Replies View Related

Dynamic SQL And Table Variables...?

Aug 23, 2004

Hello, all. I'm attempting to insert data into a table variable using dynamic SQL created at runtime.

However, with a Table variable, SQL server will not allow the EXEC method to be used in an INSERT statement.

How do I go about this?

View 2 Replies View Related

Using Variables In A Temp Table Name

May 4, 2000

I am trying to add a variable to a temporary table name. Throughout a stored procedure, I do a lot with this table. I delete, insert, update, and query this table. Is there a way to do the following without having to set the entire 'select * from ...' line as a variable? Below is what I am trying to accomplish. It all works until the select * line. Is there a way to accomplish what I am trying to do below?

Declare @table varchar(255),
@PassedID integer
set @passedID=5

set @table="test"+CONVERT(varchar(20), @passedID)

select * from @table

Thanks,
Jon

View 3 Replies View Related

Table Names And Variables

Nov 14, 2005

I have a cursor which populates a variable with the name of each user table within my DB.
I'm trying to copy the tables one at a time by using a command like this:
SELECT * INTO @NewTable FROM @OrigTable
Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'.
This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this?
Are there any alternative ways of copying the table structure (preferable without the data)?

BTW both variables are of type char(100)

View 1 Replies View Related

Count Two Variables In Table

Aug 23, 2013

I have a table that has two columns POLICY_NUMBER and POLICY_TYPE

POLICY_NUMBER POLICY-TYPE
123 1
123 1
123 1
567 2
567 2
789 1
789 1
345 1
345 1
345 1

I need to write a script that give me the following result

policy_type_count policy_type

3 1
1 2

View 3 Replies View Related

Trigger And Table Variables

Nov 6, 2014

Can we use tables variables in triggers... if yes, any example...

View 4 Replies View Related

Table Variables And Identities

Feb 16, 2006

Can an identity be created in a table variable?

Can joins be performed between table variables to be inserted into another table variable?

Am I better of using a temporary table?

(I'm working in a stored procedure here)

Thanks in advance

View 10 Replies View Related

How To SET Multiple Variables From One Table Record?

Apr 18, 2005

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.
Currently I'll do something like this:
DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey)
Of course, I'd rather just have to query "table1" just once to assign my variables.
What obvious bit of T-SQL am I missing?
Thank you in advance.
 

View 2 Replies View Related

Table Variables Vs. Temp Tables

Mar 8, 2006

How do I know when to use a table variable, and when to use a temp table in my stored procedures? It seems that in most cases table variables are more efficient (in terms of execution time / CPU usage) but some of my stored procedures perform an order of magnitute better with temp tables instead.
Short of testing the stored proc both ways, how do I know what to do?
declare @Temp table
or
create table #Temp

View 1 Replies View Related

Using Variables For Table Names In Query

Mar 19, 2006

Is it possible to use variable name to dynamically define a query in a stored procedure?  EX:

@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category

SELECT @Column
FROM @Table

View 1 Replies View Related

Table Variables - ANSI Compliant?

Jun 21, 2001

This should be a relatively easy one... Are table variables, the data type, SQL (92 or 99) compliant?

TIA,

R2ro

View 1 Replies View Related

Moving Data From One Table To Another Using Variables

Oct 20, 2013

I need to move data from one table to another using variable percentages and business days.

Here is the basic idea:

Variables: varTable1, varTable2, varPercentage

Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.

View 1 Replies View Related

Use Of Variables In Table Names And Fields

Jun 22, 2006

Hi, I am new to using SQL for anything more in depth than querying and reporting.

I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?

quote:
DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)

set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'

create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);

insert into @a_tmptbl values ( "foobar", 21.76, 1);

select * from @a_tmptbl;

The error I am getting is:

quote:Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.


Any advice would be gratefully accepted

View 3 Replies View Related

Syntax For Updating Table Variables

Jul 23, 2005

What would be the correct syntax, if any, that allows updating a tablevariable in a statement that uses the same table variable in a correlatedsubquery? Here's an example:DECLARE @t table (N1 int NOT NULL, N2 int NOT NULL)UPDATE @t SETN1 = (SELECT COUNT(1)FROM @t AS tWHERE t.N2 < @t.N2)This doesn't compile, complaining about "variable @t" in the WHERE clause.I'm not so interested in a way to rewrite this particular statement to makeit work, but rather in a general way to refer to table variables in thecontexts where correlation names cannot be used.Thank you.--remove a 9 to reply by email

View 7 Replies View Related

Temp Tables Vs Table Variables

Dec 15, 2005

I am running SQL Server Best Practices on a SQL 2000database and it is recommending me to change the temptables inside SPs to table variables.I had read already in other places to use table variablesover temp tables. I also know I can't create indexes asI can on temp tables. Instead I'll have to create eithera primary key and/or a unique index on a table variable.One question I have is let's say I will be putting thousandsof records in a temp table, should i still choose a tablevariable over a temp table for this? Or is there arecommended limit where if I have to store certainnumber of records then it's better to store them ina temp table rather than a table variable? Or numberof records is not the factor to decide whether to usetemp tables or table variables?I would like to know when it's ideal or best to usetemp tables instead of table variables and vice versa.Thank you

View 1 Replies View Related

Syntax Of Updating Table Variables?

Mar 10, 2006

Hi,I have a user-defined function which returns a table (call it '@a'),and has another table defined as a variable (call it '@b'). When I tryto do the following query, I get "Must declare the variable '@b'" and"Must declare the variable '@a'." How do I remedy this?The query:UPDATE @aSETstuff =(SELECT otherStuff From @bWHERE @b.someID = @a.someID)

View 2 Replies View Related

Table Variables And Update Statements

Jul 20, 2005

HiI'm using the SQL 2000 table variable to hold 2 different fact sets.I'm declaring the variable @CurrentTable and inserting into it using aSELECT statement with no problems.I'm leaving certain of the columns null in order to later update themwith the PK.Problem is in the UPDATE syntax I'm usingUPDATE @CurrentTableSET ManagerTitle = (select mgrs.pos_title from mgrs) wheremgrs.pos_num = @CurrentTable.MgrPosNumIt is insisting I declare the @CurrentTable variable when I try to useit in the where clause.Is it simply out-of-scope or am I really doing something foolish?Andrew

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved