Need A Faster Paging In A Wesite Search Result Page

Jan 2, 2007

have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[mainSearch]
@startRowIndex int,
@maximumRows int,
@rowCount int out,
@countedRow int,
@QUERY nvarchar(400)
AS
SELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,*
FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL
ON ADTBL._ID = KEY_TBL.[KEY]
Where (_DELETIONSTATUS=0)

)
AS RANKEDADDS

WHERE ROWRANK > @startRowIndex AND ROWRANK <= @startRowIndex + @maximumRows -1

if(@countedRow < 1)
SET @rowCount =
(
SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL
ON ADTBL._ID = KEY_TBL.[KEY]
Where _DELETIONSTATUS=0
)
else
SET @rowCount = @countedRow

RETURN
 

View 2 Replies


ADVERTISEMENT

Report Viewer In ASP.NET - Subreport Page 2 Goes Back To Main Report Page 2 On Paging

Nov 22, 2007

What we've found is that when you have two reports where one navigates to the other, if you click on the second page of the second report, it takes you to the second page of the original report. If you navigate over to the subreport again and click for the second page, it works appropriately. This is definitely a no-go for production code and this may delay our ship date because I would think this is definitely undesirable.

Conditions:

ASP.NET Report Viewer using Remote Reports
Report that links to another report
Both reports return more than one page
Reporting Services installed
Steps to Reproduce:


Download the source code here.

Follow the directions (near the bottom of the post) to get the project set up.

Run it and click on a link in the first report to go to the second report.

Click the arrow for page 2 of the second report.

Notice how it takes you back to page 2 of the first report (this doesn't happen when you are in preview mode in the Reporting Services project and I don't believe it does this in a smart client application).

Click on a link in the first report to go back to the second report.

Click the arrow for page 2.

Notice how it takes you to page 2 of the second report.


Here is a link with source code and a more detailed explanation: http://geekswithblogs.net/robz/archive/2007/11/21/Reporting-Services-2005-BUG-Report-Linking-and-Paging---ASP.NET.aspx

So what I am looking for is both a workaround for now and if this is determined to be a bug, to be fixed in the next service pack.

View 6 Replies View Related

Indexing SL CE To Make Search Faster. How?

Jan 11, 2006

Dear VB coders!

I have 8.000 or 16.000 products on my list in an sql table. It takes 8 or 16 secs to find the last item.

How could I make the search faster? Indexing? But how?

I created:
createTable1.CommandText = "Create TABLE products (ID int IDENTITY(0,1) PRIMARY KEY, name ntext)"
 
and I use simple WHERE to search.
 
Thank you!

View 3 Replies View Related

Suggestions On A Solution For Faster Search

Jan 30, 2008

Hello.

I have an OLTP database and alot of users that is querying against that.

I am thinking of a way to store a more denormalized version of some tables in another database, and use that for a search function that we have.

The problem is that the base tables are updated all the time with new and new data. And the search cannot have that long delay but a delay of 5-10 minutes for updates/insert is ok. deletes though cannot have any delay.

I was thinking of a SCD-setup and when a delete goes against the OLTP then we also run a delete against the SCD-database. But is it possible to create a SCD-database and have it update every 5-10 minutes?

I have also been thinking of a queue-table (and store updates/inserts) in the OLTP-database and then run a query every 5 min against the queue-table and update the denormalized version of the table. But isnt this a longer way? Why not update the denormalized version of the table directly when the OLAP-tables is updated?

I have also been thinking of to truncate and then fill the denormalized version every 5-10 minute from the OLTP-version. Maybe have two tables and after the fill is complete do a rename of the two tables, so that we wont lock the table that long while filling it up with the new data.

The denormalized version would contain around 200k rows.

Any suggestions is appreciated.

Thanks

View 7 Replies View Related

Paging MSSQL Database Result With PHP..Can Any One Help Me Plz..

Apr 5, 2007

hi guys..

i have got stucked into one programming problems which is called paging.i m using mssql as my database.i m using php for displaying data in to several pages but it is not supporting LIMIT functions, so can any one help me how to create php+mssql paging with accurate coding..? plz help me its urgent

bye..........................

View 12 Replies View Related

How To Design Database To Search Faster From 1 Million Customer's

Jul 30, 2007

I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.

Application serves two functionalities



Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.



Scenario: The above requirement can be complemented by following example

Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.

Now if some new customer want to open a new account in bank, then bank officials want to know whether the

new customer is one of the existing customer or not(without asking to customer itself).

System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface

then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).



Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).

Scenario: The above requirement can be complemented by following example

If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.



I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.

View 5 Replies View Related

One Page Report - One Result Row In SQL = One Full Page Of Data

Feb 27, 2008

Hi - I am pretty new to Reporting Services. I need to create a report where a single result row from the Select Statement populates an entire page of data. The regular grid or Matrix reports don't fit this need. Is there a simple way to do this?

Thanks - Dave

View 4 Replies View Related

Search Result .......need Some Help

Dec 21, 2006

Dear Experts
Here is my code:
SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString);
 protected void Page_Load(object sender, EventArgs e)
{
 SqlCommand cmd = new SqlCommand("SP_SEARCH", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"]));
cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"]));
cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"]));
cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"]));
cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"]));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
 SqlDataAdapter sqlAdapter1 = new SqlDataAdapter();
sqlAdapter1.SelectCommand = cmd;
 DataSet ds = new DataSet();
sqlAdapter1.Fill(ds, "ds");
 
CollectionPager1.DataSource = ds.Tables["ds"].DefaultView;
CollectionPager1.BindToControl = DataList1;
DataList1.DataSource = CollectionPager1.DataSourcePaged;
}
My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here"  instead of empty page.
In FormView  I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner.
Can anybody show me how....Thanks a lots
 

View 1 Replies View Related

Combine Rows In Search Result

Aug 20, 2007

In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections](
    [SectionID] [int] NOT NULL,
    [DocumentNo] [smallint] NULL,
    [SequenceNo] [smallint] NULL,
    [SectionNo] [smallint] NULL,
    [DocumentTypeID] [smallint] NULL,
    [SectionText] [ntext] NULL)
 Each paragraph of text (SectionText) is in its own row
(SectionNo)  Each primary document has a DocumentTypeID of 1 with
three subdocument types (2=Index,  3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo:     SELECT *
    FROM Sections
    WHERE CONTAINS (SectionText, 'exercise')
    ORDER BY DocumentNo
For each row that contains the search term, I would like to
return the full document (all rows as parapraphs within one row of
returned data).  In other words, I want to reconstitute the full
document as it existed prior to being inserted into the database with
paragraph separation. For exampe, if the search term is in row 3
of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of
that document in one block of text that retains paragraph format
(preferablly with a line break and carriage return between
paragraphs). How can this be done?

View 2 Replies View Related

SQL Search Page ??

Feb 2, 2004

Hi :)

Ok i moved to Sql :)

Im having some trouble whith this code ...


Dim Search As String
Search = Request.QueryString("Search")

Dim strConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Myconn"))
Dim strSearch As String
strSearch = "SELECT id,brand,catg,foto,ref,descrabrv FROM products WHERE CONTAINS(ref,@search) ORDER BY ref ASC"
strConn.Open()
Dim cmd As New SqlCommand(strSearch, strConn)
cmd.Parameters.Add("@search", Search)

Dim adatp As SqlDataAdapter = New SqlDataAdapter(cmd)

Dim daSet As New DataSet
adatp.Fill(daSet)
rstDG.DataSource = daSet
rstDG.DataBind()
strConn.Close()



The main thing ..this is a result page
I got this error :

Prepared statement '(@search nvarchar(4000))SELECT id,brand,catg,foto,ref,descrabrv ' expects parameter @search, which was not supplied

Can anyone help me with this ???

Thanks in advance

Cheers

View 1 Replies View Related

Search Page ??

Dec 2, 2005

can someone show me some sample code on how to create a store procedure tha accept mulitple parameters (lname, fname, id, etc)  to seearch records in a database .
 
 
thanks all
 
i have tried the following :
select*from  user_tbl, claim_tbl
where user_tbl.temp_claim_tbl_seq = claim_tbl.temp_claim_seqand user_tbl.lname like ' ' /*@lname*/or user_tbl.fname like 'dan'  /*@fname*/
does not work correctly ...............display duplicates rows .........not sure why

View 2 Replies View Related

Search Page

Jan 5, 2006

I've created a search page which looks at and indexed sqlServer table called tblPage and looks for page_Name. For some reason it shows no results. Do you know of any good links I can look at which address this issue.
 
<%@ Page %><%@ import Namespace="system.Data" %><%@ import Namespace="system.data.SqlClient" %><script runat="server">
    Sub Button_Click( a As Object, e As EventArgs )         Dim conPubs AS SqlConnection         Dim strSearch As String         Dim cmdSearch As SqlCommand         Dim dtrSearch As SqlDataReader             conPubs = New SqlConnection( "Server=*;UID=*;PWD=*; Database=*")             strSearch = "SELECT RANK, Page_Name FROM tblPage, " & _             "FREETEXTTABLE( tblPage, Page_Name, @searchphrase ) searchTable " & _             "WHERE [KEY] = tblPage.Page_ID " & _             "ORDER BY RANK DESC "             cmdSearch = New SqlCommand (strSearch, conPubs)         cmdSearch.Parameters.Add ("@searchphrase", txtSearchphrase.Text)         conPubs.Open             dtrSearch = cmdSearch.ExecuteReader()         lblResults.Text = "<table border=1 cellspacing=0 cellpadding=4>"         While dtrSearch.Read             lblresults.text &= "<tr>"             lblResults.Text &= "<td align=right>&nbsp;"             lblResults.Text &= ShowRank( dtrSearch("RANK"))             lblResults.Text &= "</td>"             lblResults.Text &= "<td>" &dtrSearch("Title") & "</td>"             lblResults.Text &= "</tr>"         End While         lblResults.Text &= "</table>"         conPubs.Close    End Sub        Function ShowRank ( intRank As Integer ) As String         Dim intCounter As Integer         Dim strRank As String             strRank = "<font color=green>"         For intCounter = 1 to intRank / 10             strRank &= "*"         Next         strRank &= "</font>"         Return strRank    End Function
</script><html><head></head><body>    <form runat="server">        <h2>Search Titles:         </h2>        <p>            <asp:TextBox id="txtSearchPhrase" runat="server" Columns="50"></asp:TextBox>        </p>        <p>            <asp:Button id="Button" onclick="Button_Click" runat="server" Text="Search"></asp:Button>        </p>        <p>            <asp:Label id="lblResults" runat="server" EnableViewState="False"></asp:Label>        </p>    </form></body></html>
Many thanks
 
Stuart

View 3 Replies View Related

Is It Possible To Recieve Result Of SQL Stored Procedure To Web Page?

Jul 14, 2004

I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.

View 2 Replies View Related

How To Page SQL Query Result Returned From FOR XML Statement?

Apr 25, 2006

Gurus.I do not know if it is possible, but here is what I want to do.I want to allow user to page the SQL result, so he could decides toreturn from row 10 to row 20, or row 100 to 200, without returns thewhole resultset. Every time he sends another request, I do not mind tohit the database again, (I do not want to cache the result in themiddle tier server, scalability issue), and I know that I could achievethis with CURSOR, but unfortunately the FOR XML is not allowed in aCURSOR statement .(I know that I could achieve what I want to do by writing custom codein the middle tier, but I just want to see if there is a way to do thison the database side.)Any comments & suggestion is greatly appreciated.Thanks in advance.(I am using SQL2005)John

View 2 Replies View Related

NO Records In Result Set When Non-alphanumerics Are Used In Search Criteria

Nov 5, 1999

I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric
or non-printable characters. For instance, if I have a table with a 20 character name column with names with
beginning ranges from A-Z, why doesn't the following return any rows:
select * from table where name < CHAR(126).
In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and
A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?

I'm assuming this has something to do with the default collation sequence being used which somehow does
not include characters outside the alphanumeric range. Any ideas ?

View 4 Replies View Related

Transact SQL :: Combine 3 Tables In One Search Result

Jul 1, 2015

I need a query to publish the front page of a blog.  Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments.  I would like to be able to do this in one sql statement, if possible.

 The table structure is below, you can assume the first image returned from the image table is the primary image.  

CREATE TABLE [dbo].[Blogs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BlogTitle] [nvarchar](200) NULL,
[BlogText] [nvarchar](max) NULL,
[Tags] [nvarchar](200) NULL,

[Code] ....

View 3 Replies View Related

Simple Search Page

Feb 1, 2008

 Hello all, I am trying to build a simple search page using SQL Server 2005, Visual Web Developer in C#.I have enabled Full-Text Search on my DB and created the catalogs and indexes, but I am lost on the next step to build a search form that calls the data based on the users input in a textbox. Any suggestions? Thanks!Travis 

View 3 Replies View Related

My First ASP.NET (simple) Search Page For SQL

Mar 30, 2005

This question probably applies to Visual Web Developer but I don't know if it belongs in this Forum or that, sorry if you have to move it.
Hi there kind person reading this
I've created a stored procedure in SQL2000 that selects records where (name = @name) AND (site = @site). It works as I have tested it with Enterprise Manager.
I have also created a page with VWD that has a drop-down list populated with the site records and a text box. I have put an SQL data source on the page that is configured to select from the stored procedure. However, I can't set the @name and @site variables to collect from the ddl and txtbox through the wizard! I've tried selecting a Control as the source for @site but neither the ddl or txtbox appear in the control dropdown list. So  I want to crack it with handwritten code.
Does anyone know of a post or article that can help me link the ddl and txtbox to a search button, which calls the stored procedure with the chosen variables allowing me to put the returned data into a gridview? I've been through a great walkthrough for VWD (Student Activities) but it doesn't seem to apply to the latest version - or there's something wrong with my (seems fine though) instalation. Thanks if you can help, I owe you a

View 3 Replies View Related

SQL Server 2008 :: Speed Up Text Search In Large Result Set?

Jul 14, 2015

I have a query below which filters detail field in the #TempLogins table. The details field is a text field which contains many types of text strings, some containing urls that have parts like "ResultID=5" which is what is contained in the ResultIDSearch and ResultSetIDSearch fields. The records with entries like "ResultID=5" are the ones I'm trying to filter for.

The problem I have is that the query takes way too long to run. The TempLogin table has around 200 K records and the TempSearch table has around 80 K records.

select * from #TempLogins a where exists
(select 1 from #TempSearch t1 where
a.detail like '%' + t1.ResultIDSearch + '%'
or
a.detail like '%' + t1.ResultSetIDSearch + '%')

View 1 Replies View Related

How To Display The Search Result Of A BookStore With Pictures Of Books, In A Proper Format.

Jan 22, 2007

Hi all,
I am using ASP.NET 2003 with SQL Server as database. 
I have a database of a book store with BookPicture, Author, Title, and Description of the Book.
Now when the user searchers for a  book with a keyword, how can I display the results which should show:
1. The picture of the book, 2. The at it right, Title of Book, 3. The author,4. The descritionThen the image of "Add to cart"
Each search result must be separated by a box like the cell of a table. 
Is it possible to be done?
Thanking you in advance
Tomy
 

View 2 Replies View Related

How To Extract Data From SQL Server With The Index Server Search Result?

Jan 14, 2006

I am developing a Recruitment Agency WebsiteI am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.Is it anyway possible to extract all the CV Details data by matching the path.I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.Thank you very much in Advance
            string strCatalog = "TestCatalog", strsearchstrings="";            string strQuery="";             strsearchstrings = txtsearch.Text;
            strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')";            // TextBox1.Text is the word that you type in the text box to query by using Indexing Service.            string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog;
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring);            conn.Open();                                   System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn);            System.Data.OleDb.OleDbDataReader objRdr;
            objRdr = objcmd.ExecuteReader();            DataGrid1.DataSource=objRdr;            DataGrid1.DataBind();            objRdr.Close();            conn.Close();

View 1 Replies View Related

Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page

Nov 11, 2015

I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.

View 3 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

I'm just wonder if this is a bug in MS Search or am I doing something wrong.

I have a query below

declare @search_clause varchar(255)

set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'

select * from results

where contains(finding,@search_clause)

I don't get the correct result at all.

If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"

then i get the correct result.

It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?

Anyone know?

Thanks

View 3 Replies View Related

How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?

Dec 1, 2006

I have one control table to store all related table name
 Table ID                   TableName
     1                           TableA
     2                           TableB
 
In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3
 
In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
 How can I get the result by select the Table list first and then combine the data in table A and table B?
 
Thank you!

View 1 Replies View Related

I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir

Nov 28, 2006

Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.

When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.


I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)


I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.


Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦

View 2 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Which Would Be Faster ?

Mar 28, 2007

hi all,    if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks! 

View 6 Replies View Related

Which Is Faster LIKE Or IN

Jul 27, 2001

i have a query that i can use either and get the same results. i just need to shave some time off... which is faster the LIKE or IN () ???

thanks

kim

View 2 Replies View Related







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