Transact SQL :: Passing Column Name Into A Value

Jul 30, 2015

I would like to know on how to pass a column name into a particular variable.

Example
Table_name       Column_name
Tablenm             Name
Tablenm             Age
Tablenm             Phone

The output will be:

ColName        Value
Name             Alexander Grahambell
Age                 32
Phone             123456
Name             Sanosuke Sagara
Age                 35
Phone             246790

View 4 Replies


ADVERTISEMENT

Transact SQL :: Passing Column To Table Valued Function And Getting Could Not Be Bound

Nov 10, 2015

I'm running 2014 enterprise and getting an error on this form of a query...it says the multi part identifier "mns.col3" could not be bound.  I'm aware that a cross apply would be more appropriate but i'm just prototyping and probably going to move to a set based approach anyway.The udf returns a table.

select mns.col1,
mns.col2
from table1 mns
left join dbo.udf_udf1(@firstofmonth,@lastofmonth, mns.col3) x
on 1=1

View 3 Replies View Related

Transact SQL :: Passing Parameters Through SP To A Function

Sep 23, 2015

I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working.  at the bottom of the SP is a line of code that calls a function, the function queries a View.  When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function.  This all works but I can't get the data returned by the function query onto a browser.

I am doing it this was as the parameters in my SP are also used when calling the function, works really well.  Is GTotal a return value?

Here is my function:

USE [LOGONs]
GO
/****** Object:  UserDefinedFunction [dbo].[FX_AnnaulBudget1]    Script Date: 23/09/2015 12:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

...and here is my SP:

USE [LOGONs]
GO
/****** Object:  StoredProcedure [dbo].[USP_AnnualBudget]    Script Date: 23/09/2015 12:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AnnualBudget]

[Code] .....

View 2 Replies View Related

Transact SQL :: Passing Several Values In One Parameter

Nov 12, 2015

In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast  and convert values and that does not work. The query that I am trying to use is the following:

DECLARE  @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)

Thus can you show me the t-sql 2012 that I can use to accomplish my goal?

View 12 Replies View Related

Transact SQL :: Passing C++ / C# Array To INSERT Command

Aug 13, 2015

I have to send an array, which are generated in C++ (or C#) to an SQL-INSERT. Something like this:

INSERT INTO Table1
(Col1,Col2)
VALUES(:a,:b)
// a and b are my arrays//:a=A,B,C,D.....//:b=1,2,3,...

Is there any way to do something like my prototype in MS SQL Server? If yes how should be my INSERT? 

View 3 Replies View Related

Transact SQL :: Passing Parameter To Execute Task

Aug 6, 2015

In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything

create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)

[code]...

View 6 Replies View Related

Transact SQL :: Passing Value In Where Condition Loop Wise

Jun 1, 2015

I want to calculate year to month data for month wise . Ihave start range and end range table like below

Start range and end range table

Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015

[Code] ....

Then I want to pass this value one by one in below table for get value month wise

Main table

Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404

[Code] ....

The below query is for november month: (Apr to Nov)

select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)

So I want to pass second row

select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'

Like wise I want to get month wise data of YTM data.

My expected output is

201411
201412
201501
201502
201503
201504
201505

[Code] ....

View 10 Replies View Related

Transact SQL :: Passing Variable In Select Statement

Apr 21, 2015

Everything about this query works except I'm trying to capture the @companyid (which is a variable) into a column in my table via my select statement.

My error is Invalid column name 'A113', etc.  However it is the A113 I'm trying to insert into the first column of the table SAP_GLsummary

-- retrieves a list of gl balances from all companies
truncate table sap_glsummary
declare @companyID char(6)
declare c_company cursor for
select INTERID from dbo.GP_Interid
open c_company fetch next from c_company into @companyID

[Code] .....

View 4 Replies View Related

Transact SQL :: Passing Table Parameter To A Function

Nov 3, 2015

I have a function which accepts table parmeter. i have created a view and i need to use this function.

How can i do it. how can i pass dim table into the @table variable

select * from dim cross apply fnc_user(@table variable)

View 4 Replies View Related

Transact SQL :: Passing (comma Delimited) Parameter To Stored Procedure?

Aug 4, 2015

I am simplifying things here.  Basically I have:

SELECT ......... (my selection criteria)
    where Id in (@Haulers);

@Haulers is supposed to be a comma delimited list.   What do I need to do to make it filter correctly?  

View 13 Replies View Related

Transact SQL :: Passing Multi-valued Data As Single Parameter In Query

Jul 2, 2015

I have a sp where query is as below.

DECLARE @ServerCIName varchar(5000)
SELECT * FROM  dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.

But @ServerName contains multiple values and it is dynamic (not constant).

How do I query the data?

View 6 Replies View Related

Transact SQL :: Passing Multiple String Param Values To Stored Proc

Jul 21, 2015

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Now I have to pass multiple param values to it. I was trying this but didnt get any success

exec
[SP_test]'LOC','LOP'

View 10 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Passing Column Name To A Query

Aug 22, 2001

Hi all,
Can somebody tell me how I can pass column name of a table to a stored proc.
Here's something close to what I'm trying to do....

Declare @colname varchar(30)
set @colname = "TITLE"
select name, id
from contacts
where contacts.@colname = "TITLE"

Regards
Uday

View 2 Replies View Related

Passing Column As Parameter

Aug 1, 2014

I have a table with month names as columns, below is the structure

CREATE TABLE [MonthlyTarget]
(
[Jan] [int] NULL,
[Feb] [int] NULL,
[March] [int] NULL,
[April] [int] NULL,

[Code] ....

I will store some integer values in these columns,

I want to pass these column names as a parameter,and pull the data from the column.

How to do this..?

View 1 Replies View Related

The Passing Away Of The XREF Column

Aug 31, 2007

When I was but a young developer, I saw many databases that made use of XREF columns to manage the revision history of records; if a customer had a change of name, a new customer record was created with the new name and the old record was given a cross reference to the new record. Often the user was also required to enter a reason for the cross reference.

This was a very practical paradigm, easily understood by users as it mirrored business practices that were followed regarding paper records. It presented some issues for reporting however, as following xref chains did not translate easily into set based grammars.

Fast forward to the modern times. The prevalent approach has been the use of effective dating and transactional dating, which -- while possibly superior from an algebraic standpoint -- has its own drawbacks in application.

One weakness is the lack of a paradigm or strong metaphor. This often leads to imperfect implementations where data entry errors cannot be distinguished from more meaningful changes, or historical reporting that presents incorrect information because of the multiplicity of dates.

In addition, of particular interest is that often no facilities exist in modern systems to handle real world situations involving duplicate records: a customer is entered into a system, changes names, and is entered into the system again under the new name. The error is discovered some time later, after invoices or statements may have been sent to both customer records, but no function exists within the system to indicate that both records refer to the same entity. It becomes an issue for DBAs to deal with and resolve.

I would be interested to hear any comments or rebuttals from individuals as to which technique they feel is superior, and additional weaknesses or strengths of the two techniques.

View 4 Replies View Related

Passing Paramenter To SP For Column Name

Jul 23, 2005

I need to select status values form 1 of 4 possible columns, and I need topass the column name to select on as a parameter to the stored procedure.Does anyone have an example of the syntax for the stored procedure?such as:CREATE PROCEDURE dbo.sp_Document_Select_ByStatus(@SelectColumn nVarChar)ASSET NOCOUNT ON;SELECT *FROM DocumentsWHERE (@SelectColumn = 0)The columns to select on are BIT columns.The error message on the above SP is:'Syntax error converting the nvarchar value 'P' to a column of data typeint.'At this point, the passed in parameter is a string "ProducerStatus"ThanksMichael

View 4 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

Passing A Column Into A Stored Proc?

Sep 7, 2006

I'm writing a simple voting script and have columns for each options.  I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement?  There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance 

View 2 Replies View Related

Passing Column Name To Stored Procedure

Jan 25, 2008

HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier    (    @Column  nvarchar(50),    @Expression nvarchar(50)        )    AS            SELECT SupplierID,Name,Address,Country,City,Phone,Fax    FROM Supplier        WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression       RETURN   Thanks

View 2 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

Passing Column Name As A Query Parameter?

Dec 19, 2006

Hi,

Is there a way to pass the column name as a query parameter?? If I use '@Question', like below, I get an error. If I change it to the actual name of the column 'Question1', it works fine. However, I need it to be dynamic so I can pass the column name [Question1, Question2, Question3, etc...] in and not have to define this for each question.


Doesn't Work!!


Code:


SELECT

1.0 * SUM(CASE WHEN @ColumnName > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN @ColumnName = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN @ColumnName < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'


FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID

WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)






Works, but I need to pass in the column name dynamically.


Code:


SELECT

1.0 * SUM(CASE WHEN Question1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN Question1 = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN Question1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'


FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID

WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)

View 1 Replies View Related

Passing Table/column Name As Parameter

Jul 20, 2005

I know passing table/column name as parameter to a stored procedure isnot good practice, but sometimes I need to do that occasionally. Iknow there's a way can do that but forget how. Can someone refresh mymemory?Thanks.Saiyou

View 2 Replies View Related

Passing Second Column Of Table As Index

Aug 1, 2007


We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.

We are trying with IRowsetIndex:: Seek method to access the data from the database.
We are facing problem when we try to access data from the table having composite index other than the first column.

For example:
The table ITEM contains the following columns:
1. ItemCode
2. PcNo
3. SubPcNo
4. BrandNo
5. DescText
6. ProductionKind
7. ProductionState
8. ClearingState
9. ST
10. TS

I€™ve two indexes for this table where the two indexes are
1. ItemKey1 on ITEM (ItemCode).
2. ItemKey2 on ITEM (PcNo, SubPcNo, BrandNo).

If I mention ItemKey1 as the index then the Seek method works perfectly.

But if I mention ItemKey2 as the index, then I€™m getting the error as €œDB_E_BADBINDINFO€?.

When we visted the MSDN for this problem we noted that:
1. When you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.
2. When passing key values to an index rowset, the consumer performs these actions only for the number of key columns specified in the cKeyValues argument in IRowsetIndex:: Seek.

So we had the composite index such that columns of a composite index are physically aligned next to each other as in the table.
When the first column of the index is the first column of the table as in index ItemKey1 we found that the seek method works perfectly.
If we pass the index ItemKey2, we are again getting the error €œDB_E_BADBINDINFO€?.

What else have we missed out while passing the keys in IRowsetIndex:: Seek?

Thanks in advance.

Regards,
Sasi.

View 5 Replies View Related

Transact SQL :: Column Length Restriction When Naming CTE Column?

Jun 22, 2015

My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???

View 2 Replies View Related

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

Jul 17, 2015

I have a SQL Query issue you can find in SQL Fiddle

SQL FIDDLE for Demo

My query was like this

For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1

I supposed to get last row as 

    cc  Zz  bab

Instead I am getting 

  cc  Zz  rrr 

which is wrong

View 8 Replies View Related

Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?

Nov 4, 2015

#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):

View 6 Replies View Related

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 Replies View Related

Transact SQL :: Calculate SUM Of 100 Of EDSUM Column For EDCOST Column

Aug 18, 2015

How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.

Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2))
Insert into #sum values (121, 2000,'HLMO',98,'DT')
Insert into #sum values (122, 2000,'HLMT',2,'DT')
Insert into #sum values (123, 2001,'HLMO',100,'DT')
Insert into #sum values (124, 2002,'HLMD',97,'DT')

[Code] ...

Expeced Output:
ED EDCOST EDName EDSum EDCode
126 2003 HLMR   98 DT
130 2005 HLMR   98 DT

View 7 Replies View Related

Passing Variables And Column Names To EXEC

Oct 9, 2007

Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases.
I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value:
Declare @EVBase varchar(20)Declare @IdPartition INTSet @EVBase=(SELECT EVMbxName from Servers) Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)EXEC('SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] =  ['+@idpartition+'] AND StoreIdentifier IS NULL')
Server: Msg 207, Level 16, State 3, Line 2Invalid column name '0'.
If I change the last line to: WHERE [IdPartition] =  2 AND StoreIdentifier IS NULL')The script runs fine - but I need the value from the table. Any help will be appreciated.
Best regards, Tim 
 
 

View 4 Replies View Related

Passing Column Name As Parameter To Sql Store Procedure

Dec 21, 2007

i am using asp.net 2005 with sql server 2005. in my database table contains
Table Name : Page_Content




Page_Id
101
102

1
Abc
Pqr

2
Lmn
oiuALTER PROCEDURE [dbo].[SELECT_CONTENT]
(@lang_code varchar(max))    
AS
begin declare @a as varchar(max)set @a = @lang_code
 
Select page_id,@a From page_content
end
Here in this above store procedure i want to pass 101 to @lang_code
here is my output, but this is wrong output




Page_Id
Column1

1
101

2
101
 but i want following output




Page_Id
101
102

1
Abc
Pqr

2
Lmn
oiu

View 4 Replies View Related

Passing A Selected Row Column Value To The Stored Procedure

Feb 17, 2006

I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine.  If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP.   Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"                    AutoGenerateColumns="False" DataSourceID="SqlDataSource2">                    <Columns>                        <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"                            ReadOnly="True" SortExpression="member_id" />                        <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />                        <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />                        <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />                        <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />                        <asp:CommandField ShowEditButton="True" />                        <asp:CommandField ShowDeleteButton="True" />                    </Columns>                </asp:GridView>                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"                    SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"                    DeleteCommand="renMemberDelete"  DeleteCommandType="StoredProcedure"                      OldValuesParameterFormatString="original_{0}"                     >                                         <DeleteParameters>                                             <asp:Parameter  Name="member_id" Type="Int32"  />                                              </DeleteParameters>                                            </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id  as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO

View 1 Replies View Related







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