How To Use A Variable As A Column Name?

May 25, 2008

 Hi, I want to use a variable and set it to column name but it just doesnt' work
when I execute it. I got an error message "Invalid column name 'column1'.
 
can you please help.
 

ALTER PROCEDURE [dbo].[procedure_name]
    @var1            INT = NULL
AS
    BEGIN
        IF(@var1 = '-1')
            BEGIN
                --SET @var1 = column1
            END
    SELECT *
    FROM table1
    WHERE column1 = @var1 
 
 Thank you

View 4 Replies


ADVERTISEMENT

Transact SQL :: Calculate Third Column Using Second Column And Variable

Nov 23, 2015

create table #t
(
id int,
col1 decimal(18,2)
)
go

[Code] ...

-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.

-- OUTPUT:

-- id col1 col2
--@X at starting point is 15000
-- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1)
-- 2 1000.00 0 --@X IS 9000 = 10000 - 1000
-- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000
-- 4 12000.00 12000.00
-- 5 300.00 300.00
-- 6 35000.00 35000.00

--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order.
-- in 3 row value is 1000 becouse @X is that big (1000 left from col1)

View 13 Replies View Related

Column Name As Variable

Feb 25, 2001

Using a stored Procedure, I want to select a record from a Table if a certain string appears in a specified column, so I use

CREATE PROCEDURE spSearcher
@String varChar(20)
as
Select Column1, Column2, Column3 from Table1
where Charindex(@String1,Column1) > 0

which works fine.
But the following doesn't work:

CREATE PROCEDURE spSearcher
@String varChar(20)
@ColumnName VarChar(20)
as
Select Column1, Column2, Column3 from Table1
where Charindex(@String,@ColumnName) > 0


How can I substitute a variable for Column1, so I can pass the name Column1 or Column2, or Column3, as desired?

Thanks in anticipation,

Peter Caspersonn.

View 1 Replies View Related

Column As Variable

Jun 2, 2004

I have a problem that I'm sure is very simple to answer for anyone that knows a bit of T-SQL. In a stored procedure, I simply want to concatenate a string variable containing a column name into a Select statement.

For example:
I want to execute the following statement but using a variable for the column name:

Select * from tblmet1araw where JulianDay = 1


JulianDay is an integer
This is how I have my code set up:

declare @xxx as varchar(20)
set @theday = 'JulianDay'

select * from tblmet1araw where @theday = 1

I get the following error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'JulianDay' to a column of data type int.

View 2 Replies View Related

Variable Column Name

Aug 11, 2006

Hi,

I have this query where I select data from the last 12 months (in 12 different columns).
What I would like to know is if it's possible to change the name of the columns to the month they refer to.

Thank's in advance,
AR

View 7 Replies View Related

Set Variable To Value In Column

Jul 26, 2007

Is it possible to set a variable in a stored procedure equal to avalue from a column when that column's respective ID is equal tomax(id)-1ID A B1 24 242 53 293 76 474 32 32What I am trying to do is update A in the last column to be equal to Bfrom the 4th row, plus A from the 3rd row. If I could set a variableequal to A in row 3 (in this case 76) I could easily add the variableto the existing value.I've tried something like this before. I'm sure some of you will sayright away, "of coarse that doesn't work". I'm new to sql.Set A = A + select( B from mytable where ID = (select (max(ID)-1) frommytable))where ID = (select (max(ID)) from mytable)ThanksMatt

View 1 Replies View Related

Get First Row First Column Value Into A Variable

Sep 28, 2006

It is my second time on this forum

Nice people managed to help me on my first thread. I am encouraged to proceed with you !

Well   I  have an Excel source that returns One row , two columns: what I want is get the value returned in  the first row - first colum into a variable so I can then execute sql tasks based on this filter parameter !

Can you guide me ? 

Regards Jacques

 

View 5 Replies View Related

Column Name As Variable

Oct 1, 2006

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

View 4 Replies View Related

USING A VARIABLE AS A COLUMN NAME

May 28, 2008

I have a Windows programme using VS STudio 2005 / C# /Windows XP and

Microsoft.Practices.EnterpriseLibrary.Data

I am trying to pass an 'in parameter' to a stored procedure which will be used in the sp as a column name . The column names in the database are numbers (in this case, 0 to 45). I wish to pass the column name from my Windows programme tot he sp a and use it in the sp as the variable @BallColumnValue. Can anyone please advise me of the correct way to do this?

Thanking you in anticipation.

lpbcorp

Windows programme along the lines of:

else if (subsetList == false)

{

db.AddInParameter(dbUpdateBallSuccess, "BallColumnValue", DbType.Int16, i);

db.AddInParameter(dbUpdateBallSuccess, "BallNo", DbType.Int32, 1);

}

db.ExecuteNonQuery(dbUpdateBallSuccess);

Stored procedure is as follows:


USE [MyDatabaseName]

GO

/****** Object: StoredProcedure [dbo].[MyTableNameInsertMainDrawBallRecency;] Script Date: 05/22/2008 22:12:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[MyTableNameInsertMainDrawBallRecency;]

(

@BallColumnValue nvarchar(10),

@Recency int,

@date DateTime,

@weeksSinceDrawn int,

@priorWeeksSinceDrawn int

)

AS

DECLARE lastrow_cursor CURSOR SCROLL FOR

SELECT CAST(@BallColumnValue AS COLUMN) FROM MyTableNameRecencyMainDrawBall

OPEN lastrow_cursor

FETCH LAST FROM lastrow_cursor INTO @weeksSinceDrawn

FETCH PRIOR FROM lastrow_cursor INTO @priorWeeksSinceDrawn

SELECT @priorWeeksSinceDrawn

IF @Recency = 0

BEGIN UPDATE AuMondayLottoRecencyMainDrawBall

SET [@BallColumnValue] = @Recency

WHERE Date = @date

END

ELSE

UPDATE MyTableNameRecencyMainDrawBall

SET [@BallColumnValue] = @Recency + @priorWeeksSinceDrawn

WHERE Date = @date



CLOSE lastrow_cursor

DEALLOCATE lastrow_cursor

View 10 Replies View Related

Using A Variable For A Column Name In A Query

Sep 22, 2006

Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE      CASE WHEN @Field = 'Fname' THEN Fname = @Value END,     CASE WHEN @Field  = 'Lname' THEN Lname = @Value END,     CASE WHEN @Field  = 'Phone' THEN Phone = @Value END;

View 1 Replies View Related

Column Name As Variable In Trigger

Jun 7, 2001

I am writing an audit trail trigger that needs to query columns, but the specific column names are not known until the trigger runs and determines what columns have changed. Once I know the changed column(s) I fetch the column name. No sweat.
The problem is using a variable in place of a column name I can not get the data to return, only the column name.

-- Here is how I get the column name. It works great, @CName
-- reflects the column name as I use it elsewhere fine.
Select @CName = col_name(OBJECT_ID('TABContract'), @CNum)

-- This simple syntax usually works, it does not using a
-- variable as the column name. @Prev is populated with
-- @CName literally. I have tried various and asundry ()'s and []'s
Select @Prev = @CName From Deleted

-- Here I have hard coded the Column name and ran a test modifying that column "Comment" and it works! @Prev is populated with the columns data not the Column name.
Select @Prev = Comment From Deleted

-- Tried these syntaxes, no luck
Select @Prev = (Select @CName From Deleted)
Select @Prev = (Select col_name(OBJECT_ID('TABContract'), @CNum) From Deleted)


Any help appreciated,
Ray
rwood@semcorp.com

View 2 Replies View Related

Variable ORDER BY Column

Jan 20, 2000

I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.

View 1 Replies View Related

Variable Column Queries

Jul 30, 1999

I have assigned the name of columns to a variable (@colum_name):


set @col_name = (select name from syscolumns
where id = object_id('table_name') and colid = @counter


The value of @col_name changes as the counter iterates through the different columns. The PROBLEM IS RUNNING THE FOLLOWING QUERY:


select @col_name from table_name where 'primary key = xyz'

The query returns the value of the variable @col_name. I want the value of the column in the table with name = @col_name.



I am new to T-SQL so please feel free to point out any mistakes.

Any suggestions would be greatly appreciated.

Thanks in advance.
Shaleen

View 1 Replies View Related

Temp Variable As Column Name ??

Jul 21, 2004

Hi ,
Can anyone guide me to resolve my problem . I need to write a procedure which first looks for the Worker names from WORKER table who satisfies certain criterias , and then Find from another table how many jobs each one has done on each day of a month . I have written a function which will return all days of a particular month, which can be used for the above procedure .If the wrokers are John , Alex and Martin ,( which may vary according to the branch parameter) The report should look like

Day John Alex Martin
1/5/2004 4 8 NULL
2/5/2004 5 9 12
------------------------
etc

Thanks in advance
Regards
Praveen ( praveenvc@rediffmail.com)

View 2 Replies View Related

How To Eval() A Variable To Get A Column Name?

Jan 20, 2004

OK.. I've got a stored procedure I'm writing, which accepts an argument called @statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

select * from table1 where @statfield = @value

I want to do basically an eval(@statfield) so if @statfield is "key_id", then the select statement comes out:

select * from table1 where key_id = @value

How can I do this?

Thanks!

View 5 Replies View Related

Variable Column Names

Mar 15, 2004

I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...

Is there any way to accomplish this???

I thought about placing these values into a temp table...

This is what I have so far...

Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)

Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin

Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End

View 1 Replies View Related

How To Get Multiple Column Value To Variable In PDW

Mar 5, 2014

how to get multiple column value to variable in PDW/DSQL?such as below

declare @a table(col1 int, col2 int)
insert into @a values (1,2)
declare @c int, @d int
select @c = col1, @d = col2
from @a
select @c, @d

View 3 Replies View Related

How To Insert Variable In One Column

Jan 9, 2008

Hi there

I am migrating all my DTSs to SSISs. One of them is a very basic DTS, that copies data from a text file and places it in a table with one extra column. That extra column is populated with the value of a global variable.

In DTS mode I was creating a custom ActiveX transformation stating
DTSDestination("FieldName")=DTSGlobalVariables("VariableName").Value

Can any one advise me on the best way to accomplish this in the SSIS?
I tried several approaches but all failing.

Many thanks
ds9

View 3 Replies View Related

How To Add The Value Of A Variable To Column In A DataSet

Sep 8, 2006

Hi,

i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".

What i need is a way to add a column to my transformed data which contains a value from a User-Variable.

My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.

So anybody know of an existing Data Flow component, which can do this?

Regards, Martin

View 3 Replies View Related

ADD COLUMN To Variable Table?

Sep 29, 2007

Code Block

ALTER TABLE @OrderPrice ADD(description varchar(50) );


What's wrong?

View 3 Replies View Related

Mapping Of An Xml Column To Variable

Aug 23, 2006

I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:

Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.

I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?

View 3 Replies View Related

Assigning Value Of A Variable To A Column Name

Mar 25, 2008



Hi,

I would like to assign the value of a variable to a column name of a table. For eg:

declare @sam varchar(100)
set @sam = 'Insert'
create table #temp(Sample varchar(100) not null)
insert into #temp(Sample) Values(@sam)
drop table #temp

I would like to generate a table whose column name should be generated dynamically. The value of @sam that is "Insert "should be set to the column name. Is there a way to assign like this. Can anyone please help me in achieving this.

Thanks,
Sana.

View 1 Replies View Related

Store Column Value In A Variable

Oct 1, 2007

Hi,

I am transferring data from msaccess to sql2005 using foreach loop. I have a table called Students and a column in it called State.

I want to store the value of the State in a variable in the foreach loop. I am using Dataflowtask to transfer rows from access to sql. what component can i use inside dft to store the value of State in a variable.

Plz its urgent

thanku.

View 5 Replies View Related

Can You Set A Column Alais To Be A Variable?

Oct 1, 2007

is this possible??

DECLARE @fr varchar(50)
SET @fr = (Select ActivityText FROM Activity WHERE ActivityID =1)


SELECT ContactRef AS @fr FROM Contact WHERE ContactID =1

etc

Cheers,
Craig

View 2 Replies View Related

Column Alias As Variable

Jun 28, 2006

Is there a way to select a column as an alias using a variable for the alias? something like this:

SELECT Column1 as @myVariable FROM Table1

View 4 Replies View Related

Using A Variable To Specify Table Column In Sql Statement??

Feb 6, 2008

Hello everyone,
I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases).  I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement?  For example:
SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE
This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions.
Thanks
Scott

View 15 Replies View Related

Store FOR AXL AUTO In A Column Or Variable

Jun 22, 2004

Here is my objective:

As part of auditing of our application, I want to write the values of all column in one row out to an audit table both before and after an update to see what the changes were.

I know I can SELECT * FROM tableName FOR XML AUTO to generate a single XML 'column', but I cannot figure out how to capture that output and store it.

According to MS, you cannot use FOR XML in a subselect, or fill a TEXT variable with the results of such (which, IMHO really diminishes the usefulness).

I want to stream out the results of one entire row and store that in ONE column of an audit table. XML seems like the easiest way if not for this limitation.

A better way?

Thanks.

View 5 Replies View Related

Passing A Column/field Name As A Variable?

Aug 24, 2005

Is it possible to use a table's fieldname as an SQL variable?

i.e. can the below be made to somehow work:


Code:

SELECT Firstname, Surname, myVariable
FROM ContactDetails
WHERE myVariable = [user input];



- or simply -


Code:

SELECT Firstname, Surname, [user input]
FROM ContactDetails;



---
The "user input" being any other chosen column/fieldname from the ContactDetails table (e.g. Street, City, Postcode, etc.).

i'm using Access and ASP - in case that makes a difference.

any help would be greatly appreciated.

View 1 Replies View Related

Variable As Column In Cursor Select

Jun 7, 2004

I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

declare @col varchar(50)

set @col = 'Temperature'

declare notifycurs cursor scroll for
select @col from Table

Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.

Is there a way to pass in a variable for a column name for a curor select statement????

View 7 Replies View Related

How Do I Use A Variable To Specify The Column Name In A Select Statement?

Nov 10, 2006

How do I use a variable to specify the column name in a select statement?

declare @columnName <type>

set @columnName='ID'

select @columnName from Table1

View 8 Replies View Related

How To Assign Previos Column Value To A Variable

Dec 18, 2007



Greeting.
I have a dataset (ORDER BY Column1) like the following in the data flow task pipeline




Column1 Column2

1 A
1 B
2 C
2 F
3 T
3 R
3 X
4 M



I only need data of 1, A;


2, C;

3, T
4, M
That is when Column1 value changes. I think we can store the column1 value in a variable as a previous row value. Then I can compare the variable value with the current row value and see if the column1 value changes. Can anyone please help me how to implement this? Or if there is other way to implement it

.
Thanks.

View 1 Replies View Related

Using A Variable In PIVOT Value List As Column Heading

Sep 20, 2007

Hi All,
 I have never used PIVOT before but looks exactly what I want for this scenario:
I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date.....  I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like:
DECLARE @todayDate varchar(255),
DECLARE @todayPlusOne varchar(255),
DECLARE @todayPlusTwo  varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express.
Thanks in advance.
Lee
 

View 5 Replies View Related

Restarting The Identity Column On Table Variable

Jun 2, 2006

In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table.  The purpose is to get a list that looks like this after the insert:
RowID     valueID1               348972               345223               94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable  DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable  variable?
 
 

View 4 Replies View Related







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