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
ADVERTISEMENT
Dec 13, 2006
I want to create a web page where a user can select from 1-100 fields to include in output, and then query a table to only return the fields the user has selected.
I do not want to construct a dynamic SQL SELECT statement. I would rather use a stored procedure, but am not certain how to only return the fields that the user selected.
View 1 Replies
View Related
May 22, 2008
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
Simply I want to below statement in stored procedures and call from program. Can any one help me plz.
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da.Fill(ds,"Dep");
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con);
da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da1.Fill(ds,"Exp");
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con);
da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da2.Fill(ds,"Inc");
string deposits = Convert.ToString(ds.Tables["Dep"].Rows[0].ItemArray[0]);
string expenses = Convert.ToString(ds.Tables["Exp"].Rows[0].ItemArray[0]);
string income = Convert.ToString(ds.Tables["Inc"].Rows[0].ItemArray[0]);
GridView1.DataSource = ds;
GridView1.DataBind();
GridView1.Rows[0].Cells[0].Text = "Total";
GridView1.Rows[0].Cells[1].Text = deposits
GridView1.Rows[0].Cells[2].Text = expenses;
GridView1.Rows[0].Cells[3].Text = income;
//The above program was success.
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u.
SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
ASPX Code for Grid View
<h2>Account Summary</h2><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" >
<Columns>
<asp:TemplateField >
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text="Total"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ><%--HeaderText="Deposits"--%>
<ItemTemplate>
</ItemTemplate>
<HeaderTemplate>
<a href="Deposits.aspx" >Deposits</a>
</HeaderTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField >
<HeaderTemplate>
<a href="Expenses.aspx">Expenses</a>
</HeaderTemplate>
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField >
<HeaderTemplate>
<a href="Income.aspx">Income</a>
</HeaderTemplate>
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate>
<%-- <%# Eval("Course") %>--%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Plz solve this.
View 4 Replies
View Related
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
May 26, 2005
SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
View 1 Replies
View Related
Nov 26, 2005
Hi, I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.e.g Say TableContainQuery(PKID,QueryField) Now above table have 10000 of records, I need to retrieve data from by executing queries contain in above table. I am using SQL Server.
Regards,Dheeraj
View 3 Replies
View Related
Aug 25, 2015
I had an existing table with lots of indexes.
As a test (fro speed) - I added a non clustered column-store index.
When I run test queries it always ignores my new column-store index. Why?
Should I remove the old indexes, leaving just the column store?
View 2 Replies
View Related
May 25, 2008
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
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 29, 2007
Code Block
ALTER TABLE @OrderPrice ADD(description varchar(50) );
What's wrong?
View 3 Replies
View Related
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
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
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
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
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
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
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
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
