Can You Filter SELECT Results?

Jul 23, 2005

I want to create a stored procedure that returns a list of records from
a table. But depending on a userID value given only certain records
will be returned that they have access to.

I think this might be hard to do in a single SELECT statement because
the user might also belong to a group that might have permission, etc.

Can you do something like this pseudo code in a T-SQL procedure?

DECLARE cur CURSOR FOR SELECT * FROM myTable
OPEN cur

FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
if( accessGranted(curRecord.id) ){ addRecordToResultSet() }
else { ommitRecordFromResultSet() }
END

View 11 Replies


ADVERTISEMENT

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Automatic Select Filter (something Like Global Table Filter)

Apr 15, 2008

Hello,

Here is my problem:


I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?


Thanks in advance.

Best regards,
Beroetz

View 5 Replies View Related

Filter Query Results

Nov 22, 2007

Hi,

I have an interface that allows the user to filter the data to be returned.

e.g. if the user selects @SeniorManager then the query should return all data related to this.

So if the user selects @SeniorManager and @Auditor then the query should return all data matching both @SeniorManager AND @Auditor.

FundID and ClientID are the ID's that link each table.

Am I correct in using UNION ALL below?? thanks.





Code Block
SELECT SeniorMgr, null AS Auditor, null AS Legal
FROM FundClient f
INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID
INNER JOIN Staff s
ON (s.ClientID = p.ClientID) AND (s.FundID = p.PortfolioID) AND (s.ClientID = f.ClientID)
WHERE s.ClientID = @ClientID AND s.FundID = @FundID
AND (@SeniorManager = null Or SeniorMgr = @SeniorManager)
UNION ALL

SELECT null AS SeniorMgr, fa.Auditor, null AS Legal
FROM FundClient f
INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID
INNER JOIN (Auditor a
INNER JOIN FundAuditor fa
ON fa.AuditorID = a.Auditor)
ON (a.ClientID = p.ClientID) AND (a.FundID = p.PortfolioID) AND (a.ClientID = f.ClientID)
WHERE a.Auditor = @Auditor
AND a.ClientID = @ClientID AND a.FundID = @FundID

UNION ALL

SELECT null AS SeniorMgr, null AS Auditor, fl.Legal
FROM FundClient f
INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID
INNER JOIN (Legal l
INNER JOIN LegalCounsel fl
ON fl.LegalID = l.Legal)
ON (l.ClientID = p.ClientID) AND (l.FundID = p.PortfolioID) AND (l.ClientID = f.ClientID)
WHERE l.LegalC = @Legal
AND l.ClientID = @ClientID AND l.FundID = @FundID





View 8 Replies View Related

Using DropDownList And TextBox To Filter Results...

Aug 5, 2006

Hey all,
I've been searching for a while and haven't really found an answer for what I'm trying to do. Here it all is and someone can tell me if I'm going in the wrong direction to solve this.
I have a textbox set to a parameter of "SearchString" and a DropDownList with the SelectedValue paramter of "FilterValue". The parameters work, I've tested that. So here is the problem.
I am trying to use the "FilterValue" to pick the field that the "SearchString" searches with a  LIKE statement (e.g. SELECT * FROM dbo.Units WHERE (@FilterValue LIKE '%'+@SearchString+'%'))
This, I've found out, doesn't work. I have this setup with a DataLayer.vb in my App_Code directory handling the SQL calls, while the normal CodeBehind is in the typical "Default.vb" page.
I will post code later as I don't have access to it right now, but if anyone can explain how a filter like this would work I would appreciate it!
Thanks,
 
-Matthew

View 3 Replies View Related

Filter Double Results From Query

Nov 14, 2007

We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example:
http://84.244.146.27/Zoekresultaten.aspx?SearchQuery=Amsterdam
As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results.
I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done?
Any help is greatly appreciated!
 

View 5 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

SELECT-Using Correlated Subqueries: Just Name In Results && 0 Row Affected In One Of MSDN2 SELECT Examples

Jan 11, 2008

Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product p

WHERE EXISTS

(SELECT *

FROM Production.ProductModel pm

WHERE p.ProductModelID = pm.ProductModelID

AND pm.Name = 'Long-sleeve logo jersey') ;

GO

-- OR

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product

WHERE ProductModelID IN

(SELECT ProductModelID

FROM Production.ProductModel

WHERE Name = 'Long-sleeve logo jersey') ;

GO

=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Implement SELECT Starement On A Results Of Prev SELECT

Dec 22, 2002

Hi,
im getting from my first select a list of pairs of codes (let say the codes r of products.)
so i have something like:

FirstCode SecondCode
1 1
2 5
4 2
... ...
now i want to get the name of each product so it whould be like:

FirstCode,FirstName,SecondCode,SeconeNam

the names stored in other table.
how can i do it?
thanks

Dovalle

View 1 Replies View Related

SqlDataSource Select Is Ignoring My Filter

Sep 25, 2007

I am using the SqlDataSource to access the dB from my page. Basically this is what I do with it ds.SelectParameters.Clear();
ds.SelectParameters("Id", TypeCode.Int32, id.ToString());

DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.MaximumRows = 1;
dssa.AddSupportedCapabilities(DataSourceCapabilities.Page);

DataView dv = (DataView)ds.Select(dssa);
if (dv.Count > 0)
{
// collect the information
string title = (string)dv[index].Row.ItemArray[0];
}
And the SelectCommand attribute of the SqlDataSource is set in design mode to "SELECT * from vw_Items ORDER BY Category".
So, since I am trying to retrieve just the item with the given Id I was expecting just one record but when I step through I see that the data view has a count of 9 (all records in the table) !!!
What am I doing wrong here? why can't it return just one? as per the select statement which after adding the parameter should be something like "SELECT * FROM vw_Items WHERE ID = 5 ORDER BY Category 

View 1 Replies View Related

Select Statement - Filter Condition

Nov 5, 2013

How to display in select statment starting from the word 'basic'

table name:version

Table values:

versionidVersiondescription
53666445SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
53666446SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
53666447SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
53666448SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY

Output should be:

versiondescription
Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY

View 3 Replies View Related

Help, MDX Help, Multi-select Properties Filter

Nov 23, 2007

Dear experts,

I would like to ask for a help regarding MDX query.

I am developing reports in SSRS 2005 that use some multi-valued parameters.
I need to filter out my data according to the dimension's properties' value

For example, if I have only single value parameters, I would do:

SET [SelectedDestination] AS FILTER( [Country].[Destination].Members, [Country].CurrentMember.Properties("KEY") = Parameters!Country.Value)


However, I have no idea how to handle it if Parameters!Country is a multi-valued parameter.

Is there a better method to do this? Would anybody please kindly give a direction? it will be greatly appreciated!! any help will do..


Thank you very much

View 11 Replies View Related

Setting A Filter In The SELECT-statement

Jan 10, 2007

Is it possible to set a filter in the SELECT-statementpart?
Normaly you the filter is set in the WHERE-statementpart, but that is too late for me in my statement. Right after the SELECTing the field I want to set a filter to the query? Is it possible?

My statement looks like this now:
SELECT [DatabaseName$Item].No_,
[DatabaseName$Item].Description,
[DatabaseName$Sales Price HAG].[Unit Price],
[DatabaseName$Sales Price HAG].[Starting Date],
[DatabaseName$Sales Price HAG].[Starting Time],
[DatabaseName$Sales Price HAG].[Sales Code] (<== here on this item I want to set a filter)

FROM [DatabaseName$Item] INNER JOIN
[DatabaseName$Sales Price HAG] ON
[DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
INNER JOIN (SELECT [Item No_], MAX([Starting Date]) AS MaxStartingDate, MAX([Starting Time]) AS MaxStartingTime
FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
GROUP BY [Item No_]) AS SubQuery1
ON [DatabaseName$Sales Price HAG].[Item No_] = SubQuery1.[Item No_] AND
[DatabaseName$Sales Price HAG].[Starting Date] = SubQuery1.MaxStartingDate AND
[DatabaseName$Sales Price HAG].[Starting Time] = SubQuery1.MaxStartingTime

Thx for helping!

View 8 Replies View Related

Select Command That Filter Repeated Value

Jun 14, 2006

Hi


Ihave a table With A Culomn That Is NOT Unique And Repeat Many Tims BUT I want To Have a Select Command That Filter Repeated Value And Show Only One of rows Whith Same Data

Thank u

View 1 Replies View Related

Dynamically Adding Select Parameters (Filter)

Jul 24, 2007

how do i add parameters like this dynamically? do i need to change the select command? to add the @ID part? 

View 4 Replies View Related

Transact SQL :: Pass Results From Select To Another Select

Oct 13, 2015

I've got a select as follows:

select computer, count(*) as MissedCount  from WInUpdates_Neededreq
WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE())
group by computer

I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.

How can I join to another table and still keep the count form the original table.  I want ot join to tblogons.workstationname and return computer from the original query...

View 16 Replies View Related

Filter SqlDataSource Select Based On Membership User Name

Jul 11, 2006

I am running into an issue trying to declaratively set up a SqlDataSource.  I want to be able to filter some of my queries based on the user that is currently logged into the web site.  I want to do it Declaratively as that's one of my favorite 2.0 features.Is there any way to do this with the Membership information?  I know I can use the code behind to set the parameter, or store the User Name in Session Variable, and use a <asp:SessionParameter> but I think there should be a way to bind directly to the Membership user...Am I missing another option, or is there no built in way to do this?  Any other suggestions...Thanks

View 1 Replies View Related

HOW TO USE DISTINCT IN SELECT STATEMENT TO FILTER OUT DUPLICATED RECORD??

Jan 5, 2001

I HAVE A SELECT STATEMENT WITH TEACHERS AND STUDENTS AND SOMETHING ELSE TOO.
FOR EACH TEACHER I ONLY NEED ONE(FIRST ONE) STUDENT.
HOW THE STATEMENT SHOULD BE?

SELECT DISTINCT .... TID, SID, SOMETHING ???????

View 3 Replies View Related

SQL Server 2012 :: Filter MS Objects Out Of Select Statement

Jul 27, 2015

I am writing a script to get all of the user objects out of a database. What I am having a hard time doing is filtering out the MS replication system objects, such as:

dbo.MSsnapshotdeliveryprogress
dbo.MSreplication_objects
dbo.MSreplication_subscriptions
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties
dbo.MSsavedforeignkeys
dbo.MSsnapshotdeliveryprogress
dbo.MSsubscription_agents

View 2 Replies View Related

SELECT WHERE RowID Is Not From Results Of Another SELECT

Nov 16, 2007

I have one query which uses a join query to gather all the projects that should show up in someone's list over a period of time (returns and id (int) and name (varchar) paired dataset). I want to do a separate query that takes that list and selects all projects (same paired set ... id and name) EXCEPT where it matches an id on a row of the given result set. The one query looks like this ..DECLARE @startDate datetimeDECLARE @endDate datetimeDECLARE @userId UNIQUEIDENTIFIERSELECT @startDate = ppStartDate FROM ppTablewhere payPeriodID = @payPeriodIDSELECT @endDate = ppEndDate FROM ppTable WHERE payPeriodID = @payPeriodIDSELECT @userId = userID FROM usersTable WHERE userName = @userNameSELECT DISTINCT p.projectID, p.projectNameFROM projectsTable pLEFT JOIN projectMemberhsip m ON m.ProjectId = p.ProjectIdLEFT JOIN timeEntryTable t ON t.ProjectID = p.ProjectIdWHERE t.TimeEntryUserId = @userID AND t.TimeEntryDate >= @startDate AND t.TimeEntryDate <= @endDateORm.UserId = @userID I want to get the same selection from projectsTable WHERE it's not anything from this result set.Haven't been able to get it by modifying the WHERE logic.  Is there a way to select all WHERE id != (resultSet from this SELECT)? TIA! 

View 5 Replies View Related

Rounding Down In SQL Select Results

Aug 23, 2006

I am trying to round this data, but can't find a  good resource on this.  If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.ROUND(DATEDIFF(mm, employee.emp_begin_accrual, GETDATE())
* employee.emp_accrual_rate - SUM(ISNULL(request_1.request_duration, '0')), .5) Any help would be greatly appreciated.  A link to a good reference on rounding would help too.Thanks in advance!!!

View 8 Replies View Related

How To Use Results Of Select As SP Input

Oct 17, 2012

I need to insert in TableB the results of a select over TableA so i used:

Insert into TableB (col1, col2, col3) select c1, c2, c3 from TableA

and works OK.

But now i need to use a Stored Procedure "Insert_in_TableB" that handles all the process of checking values, inserting data, logging steps, etc etc. That receives via its parameters the values for validate, etc and then insert them in TableB.

So, how can i execute that procedure (and define its parameters) in a easy way? Something like

Exec Insert_in_TableB (select c1, c2, c3 from TableA)
or
Select c1, c2,c3 into Insert_in_TableB...

Don't want to use cursors.

View 1 Replies View Related

Select 4 Most Recent Results

Mar 29, 2013

I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:

SiteDate Data
A01/01/2013 ...
B02/01/2013 ...
C03/01/2013 ...
A04/01/2013 ...
B05/01/2013 ...
C06/01/2013 ...
And so on...

In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.

View 3 Replies View Related

Select Like And Order Results

Apr 25, 2008

Hello there,

I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table:
1- the exact match for the search string come first from the two table.
2- and the partial match comes last after the exact match.

this is my DDL for the two tables :


USE [Northwind]
GO
/****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


second table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
[MemberID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF


and this my search query I have it in a stored Proc.


select *

from

(





SELECT PersonID, Type, Name, email


FROM Person
WHERE (Name LIKE '%'@Name'%')
union all

SELECT PersonID, Type, Name, email


From Members
WHERE (Name Like '%'@Name'%' )

) Y



Order by

Case[Name] when @Name Then 1 Else 2 End,

Case[Name] when 'm' Then 1 Else 2 End

Thank you for your time
Sms

View 5 Replies View Related

Concatinating Select Results

Jan 18, 2006

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrdersSET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

View 2 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

And/or Filter Field Not Enabled In The Group Filter Tab

Jan 26, 2006

Howdy,

I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.

PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?

The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)

View 14 Replies View Related

Displaying Select Results On The Page

Aug 28, 2006

I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page.  I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.

View 1 Replies View Related

What All I Can Bind To My Select Query Results?

Nov 7, 2007

my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader();  can we have something likeHashtable = cmd.ExecuteReader();

View 5 Replies View Related

Select And Then Inserting Results Into Two Different Tables

Dec 2, 2003

Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],

(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]

from a_ProspectPipeline where A_ProspectPipeline.ID = @ID

What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?

View 2 Replies View Related

Stored Procedures (SELECT Then Use Results)

Jun 17, 2004

Hi,

I need to keep track of the number of hits on a particular page. Im using a stored Procedure

What I want to do is get the number of hits and increment it by one :)

ie: Sub Procedure should be like below

SELECT noOfHits WHERE pageName = 'bla bla'

noOfHits = noOfHits + 1 etc.

Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'


What I need to do in essence is:

1. Check that a particular row exists. if it doesnt create it.
2. Increment a value (by one) to a column in this particular row.

Phew. Hope you got that. Any ideas much appreciated,

Thanks,

Pete

View 4 Replies View Related

Select Top N Results In A Group; Count(*)&<=n

Jan 18, 2003

Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.

I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.

I tried the follwoing, but get incorrect results.

select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price

However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.

select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price

I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?

View 2 Replies View Related







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