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 customers
WHERE @Field = @Value
OR
SELECT *
FROM customers
WHERE
CASE WHEN @Field = 'Fname' THEN Fname = @Value END,
CASE WHEN @Field = 'Lname' THEN Lname = @Value END,
CASE WHEN @Field = 'Phone' THEN Phone = @Value END;
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE ( TransactionHeaderID [int] NOT NULL , PatientID [int] NOT NULL , TransactionAllocationAmount [money] NOT NULL , ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader set TransactionAllocationAmount = (SELECT isnull(Sum(mtblTransactionAllocation.Amount),0) FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader set ApplyAmt = (SELECT mtblTransactionAllocation.Amount FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount, mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt FROM @THeader, mtblTransactionHeader WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Hi, I'm try to get the query second column value when it is assinged to a varchar variable. Ex:In SP below is statement I wrote SET @Values = (SELECT COL1,COL2 FROM TABLE) Question 1: How to access the COL2 value from @Value2? Question 2: How to access the other row values if above returns more than a row? Please send me the solution as soon as possible. Thanks-Vikash/Bala
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
-- 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)
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
Hi, I want to use a variable and set it to column name but it just doesnt' workwhen I execute it. I got an error message "Invalid column name 'column1'. can you please help. ALTER PROCEDURE [dbo].[procedure_name] @var1 INT = NULLAS BEGIN IF(@var1 = '-1') BEGIN --SET @var1 = column1 END SELECT * FROM table1 WHERE column1 = @var1 Thank you
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.
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.
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
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 !
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.
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?
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)
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.
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)
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:
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...
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
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.
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?
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.
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.
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
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.
