Using Stored Procedure To Implement Multi-keywords Query
Mar 28, 2000
Hello,
I am greener in using SQL Server 7.0. I created a stored procedure named usp_Test (please see the following codes for reference). It worked fine when passed through a single keyword into it (for example usp_Test 'Satellite'), but it did not return the query results what I wanted when passed through multiple keywords into it (for example usp_Test 'Satellite Remote'). In fact, it returned nothing but the column names specified in SELECT statement. Below is the sample of procedure how it looks like. Where am I wrong? Any idea?
CREATE PROCEDURE usp_Test
@strKeywords varchar(50)
AS
DECLARE @DelimiterPos integer
DECLARE @Delimiter char(1)
DECLARE @strWHERE varchar(1000)
SET @DelimiterPos = 0
SET @Delimiter = CHAR(32)
SET @strWHERE =""
SET @strKeywords = RTRIM(LTRIM(@strKeywords))
SET @DelimiterPos = CHARINDEX(@Delimiter,@strKeywords,1)
IF @DelimiterPos > 0
BEGIN
WHILE (@DelimiterPos > 0)
BEGIN
IF LEN(@strWHERE) > 0
BEGIN
SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + LEFT(@strKeywords,@DelimiterPos - 1)+ "%" + CHAR(13)
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + "%" + LEFT(@strKeywords,@DelimiterPos - 1) + "%" + CHAR(13)
END
SET @strKeywords = LTRIM(RIGHT(@strKeywords, LEN(@strKeywords) - @DelimiterPos))
SET @DelimiterPos = CHARINDEX(@Delimiter, @strKeywords, 1)
IF @DelimiterPos > 0
BEGIN
CONTINUE
END
ELSE
IF LEN(@strKeywords) > 0
BEGIN
SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + @strKeywords + "%" + CHAR(13)
BREAK
END
END
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + "%" + @strKeywords + "%"
END
PRINT 'WHERE tblClips.Title Like ' + @strWHERE
SELECT tblClips.Title,tblTapes.ClassificationNo,tblTapes. Inventory
FROM tblClips INNER JOIN (tblClipTape
INNER JOIN tblTapes
ON tblClipTape.fkTapeID = tblTapes.TapeID)
ON tblClips.ClipID = tblClipTape.fkClipID
WHERE tblClips.Title LIKE @strWHERE
Thank you very much in advance.
Changbaoi
View 1 Replies
ADVERTISEMENT
Jan 10, 2004
Hi,
I'm trying to move an asp site from an Access DB to SQL Server.
The site features a search system which allows multiple keywords to be used in up to three fields.
The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6701):
"So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business."
The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case?
If it isn't possible, how can I go about this?
Cheers all.
__________________________________________________ ___
Code:
ASP:
item=request(item)
'****item = Replace(item, " ","%' AND item like '%")**** - needs attention
rs.Open "Exec getlist "& item &"" objConn, 3
SP:
CREATE PROCEDURE getlist
@item nvarchar(255)
SELECT item FROM publications WHERE item LIKE '%' + @item + '%'
GO
View 14 Replies
View Related
Nov 6, 2003
--keywords table:
CREATE TABLE #KeyWord
(
[QueryId] int IDENTITY (1, 1) NOT NULL ,
[QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND)
[SKey] nvarchar (200) NULL
)
ON [PRIMARY]
--target table:
CREATE TABLE Test
(
[PId] int IDENTITY (1, 1) NOT NULL ,
[PTitle] nvarchar (200) NULL
)
ON [PRIMARY]
================
I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
PS. sqlserver's full text index mode is off.
View 3 Replies
View Related
Jun 8, 2007
Hi,
I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried.
An example:
User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be
SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%')
As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures?
Kind regards,
ThaYoung1!
View 11 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
Aug 7, 2007
I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.
In the dataset , I set command type as Text and typed the following statement.
EXEC spMultiValue " & Parameters!Month.Value &"
its not returning anything.
can anyone tell me how to pass multivalue parameter to stored procedure.
Thanks for your help.
View 2 Replies
View Related
Feb 12, 2008
What is the best way to design this stored procedure for speed?
I want to pass in a list of IDs and have a return value of a table that contains the ID and its associated value. Is there a way to pass in as table and come out as table?
input parameter
ID
0
1
2
return value
ID, CurrentDue, PastDue
0, 100, 0
1, 100, 100
2, 123, 0
View 11 Replies
View Related
Apr 16, 2013
I got issue when passing multiple values to a single parameter. Here is my stored procedure as following:
CREATE PROCEDURE [dbo].[School]
@Grade AS varchar(50),
@Class As varchar(50)
AS
BEGIN
SELECT Name, Grade, Class
FROM School
WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class ))
END
In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter.
View 14 Replies
View Related
Dec 19, 2007
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
View 5 Replies
View Related
May 25, 2008
hi need help
i have this stored procedure
the problem is that
i canot update like this not more than 20 - 30 rows
(i send it from a web page from check box)
it work but it limited rows for update not more than 20 - 30 rows
in one time
Code Snippet
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sto_update_snha]
@id varchar(1000)
as
UPDATE Snha
SET fld5 = 3
WHERE charindex(','+CONVERT(varchar,[id])+',',','+@id+',') > 0 AND (fld5 = 2)
TNX
View 16 Replies
View Related
Feb 5, 2008
spaces problem on multi update delete stored procedure
on the the insert i get it like this
58830803, 55329429, 308962604, 55696314, 309128726
as you see only the first empid=58830803 is without spaces
how to fix this
my code
Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 4 Replies
View Related
Feb 12, 2007
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
View 12 Replies
View Related
Feb 6, 2007
Hi,
I'm trying to do a query to do a search by keywords. For example if the user searches for "Hottest restaurants in Atlanta" i want to write a query that searches that exact phrase first in a database field called keywords and if it shows up there then that record should be displayed first.. and then after do a search for EACH one of the words in a number of fields in the database and see if it shows up.
here is what i have so far but it always returns the same thing. any ideas?
$searchArray=explode(" ",$search);
$query = "SELECT ID,name,description from restaurants WHERE keywords = '$search' OR (";
foreach($searchArray as $key){
$query = $query . " (name LIKE '%$key%' OR subType LIKE '%$key%' OR features LIKE '%$key%' OR neighborhood LIKE '%$key%' OR region LIKE '%$key%') OR";
}
$query = substr($query,0,strlen($query)-3);
$query = $query . ") order by keywords";
any ideas?
View 11 Replies
View Related
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
View 1 Replies
View Related
Dec 28, 2007
Hi Guyz
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
thanks in advance.
View 4 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jan 12, 2007
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.
ddave
View 2 Replies
View Related
Jun 8, 2004
how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
View 9 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Mar 25, 2008
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
View 4 Replies
View Related
Nov 23, 2004
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
RETURN 1
END
GO
View 8 Replies
View Related
Dec 20, 2004
Hi:
In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.
then I try to run a query as follows:
if exists (select rfABC.* A
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L
on A.Address = L.address and
A.metro <> L.metro
begin
print ' ---- Yes metroID <> LAMetro, start job exec.... -----'
insert into tempCatchMetroIDGPRS
select rfABC.*, metro, getdate()
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro
update rfABC A
set A.metro = L.metro
from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro
end
else
begin
print ' ---- no metroID <> LAMetro, skip job exec.... -----'
end
------------------------------
this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...
thanks
David
View 4 Replies
View Related
May 23, 2007
Hello to all,
I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha
My Procedure Codes are here:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )
AS
BEGIN
if ( @Level = 1)
begin
select @Path = convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @IDMember
and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0
end
if (@Level = 2)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0
end
if (@Level = 3)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0
end
if ( @Level = 4)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0
end
if (@Level = 5)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0
end
if (@Level = 6)
begin
select top 1 @Path = '' from wtcomValidRelationships
end
END
View 6 Replies
View Related
Jun 15, 2015
I have a requirement of migrating DTS package which is done in Sql Server 2000 to SSIS 2012.
I started with one package having data driven query task and done with source for which i chose OLE DB Source and given the required select query in ssis 2012
I'm stuck now and i'm unable to choose the relevant tools in ssis 2012 for binding, transformation,queries and lookup tabs used in dts 2000 for this DDQT.
View 4 Replies
View Related
Jun 27, 2007
hey.. say i have a table with 4 columns, Id, col1, col2 & col 3the way it works is the id and one of the col's will have info, the other 2 cols will be emptyim trying to write a proc that returns the value of the column that has info + a number that is stored in a string to represent the column eg: CREATE PROCEDURE proc_Test @Id int, @Answer varchar(100) outputasset nocount on select @Answer = col1 +', 1'from myTablewhere Id= @Idif @@rowcount < 1select @Answer = col2 +', 2'from myTablewhere Id= @Idif @@rowcount < 1
select @Answer = col3 +', 3'
from myTable
where Id= @IdreturnGO but for some reason, it only process's the first select statment and if nothing is in the column, it returns - ', 1' cheers!!!
View 4 Replies
View Related
Apr 1, 2008
Hi,I am weak in writing stored procedure and want to learn it step by step.Now I have written a query and the requirement is such that I need to convert it in stored procedure.query:Select distinct empskill.tcatid,Coalesce(prm,0) as prm,coalesce(secn,0) as secn,a as technology,coalesce(skd,0) as skd,coalesce(knd,0) as knd,coalesce(tnd,0)as tnd,coalesce(dnd,0) as dnd from empskill RIGHT OUTER JOIN (select tcatid,Count(skilltypeid) AS prm from empskill where skilltypeid=1 group by tcatid) prms ON empskill.tcatid=prms.tcatid LEFT OUTER JOIN (select tcatid,Count(skilltypeid) AS secn from empskill where skilltypeid=2 group by tcatid) secs on empskill.tcatid=secs.tcatid RIGHT OUTER JOIN (select technology.category as a,empskill.tcatid from empskill,technology where empskill.tcatid=technology.tcatid group by empskill.tcatid,technology.category ) s ON empskill.tcatid=s.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS skd from empskill where skilllevelid=1 group by tcatid) skds on empskill.tcatid=skds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS knd from empskill where skilllevelid=2 group by tcatid) knds on empskill.tcatid=knds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS tnd from empskill where skilllevelid=3 group by tcatid) tnds on empskill.tcatid=tnds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS dnd from empskill where skilllevelid=4 group by tcatid) dnds on empskill.tcatid=dnds.tcatid union select top 1 500 as tcatid,'' as prm,'' as sec,'more' as technology,'' as skd,'' as knd,'' as tnd,'' as dnd from empskill Can you please explain step by step how to convert this to stored procedure.Thanks a lot
View 3 Replies
View Related
Nov 16, 1999
In access, I can create a function that I can call in a query. It runs that function iteratively. Do I have the same ability with SQL server? Can I call a stored procedure WITHIN a query? I would like to be able to do something like:
Insert into tblOrders(spFirst_Name) Select {Call spUpperLower(tblClients.First_Name)} from tblClients Where ...
So basically, spUpperLower would run on every First Name Row in the result set.
Any help would be much appreciated.
Thanks,
Brad
View 3 Replies
View Related
Oct 2, 2001
I want to run a stored procedure from a query...
can I do this? Thanks!
CREATE VIEW dbo.V_EmploymentTerminationsResignations
AS
--the next 3 lines produce errors...
declare @StartDate datetime
declare @EndDate datetime
exec [hrs2].[dbo].[sp_getquarterinfo] --@StartDate OUTPUT, @EndDate OUTPUT
--they're designed to replace the hard coded stuff in the WHERE statement...
--a snippet of the originl view...
SELECT TOP 100 PERCENT dbo.Employee.OrgID, dbo.Employee.SSN, dbo.Employee.EMPLOYEE_NAME, , dbo.Employee.SPECIAL_STATUS,
WHERE ...
(dbo.Employee.LAST_PERS_ACTN_DATE BETWEEN CONVERT(DATETIME, '2001-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2001-10-01 00:00:00', 102))
ORDER BY dbo.Employee.LAST_PERS_...
View 1 Replies
View Related
Oct 22, 2007
Hi,
I have created an SQL query which returns the expected data. However, when I try to run it as a stored procedure, I get no rows returned.
(I assume I running the procedure correctly e.g. exec getNonDeployed RCN20047)
Code:
CREATE PROCEDURE getNonDeployed
@packageID text
AS
BEGIN
DECLARE @CollectionID VARCHAR(10)
DECLARE @Collections TABLE (CollectionID VARCHAR(10))
set @CollectionID = (SELECT
CollectionID
from ProgramOffers
where OfferID='@packageID');
WITH CollectionFull (SubCollectionID) AS
(
-- Create the anchor query. This establishes the starting
-- point
SELECT
SubCollectionID
from v_CollectToSubCollect a
where a.SubCollectionID=@CollectionID
UNION ALL
-- Create the recursive query. This query will be executed
-- until it returns no more rows
select
a.SubCollectionID
from v_CollectToSubCollect a
inner join CollectionFull b on b.SubCollectionID=a.ParentCollectionID
)
Insert Into @Collections
SELECT * FROM CollectionFull
select a.RuleName AS MACHINENAME, c.IPAddress0 as IPADDRESS
from v_CollectionRuleDirect a
inner join @Collections b on b.CollectionID=a.CollectionID
inner join v_GS_DEVICE_NETWORK c on c.ResourceID=a.ResourceID
where RuleName not in (select distinct a.MachineName as MACHINENAME
from v_StatusMessage a, v_StatMsgAttributes b, v_CollectionRuleDirect c, v_GS_DEVICE_NETWORK d, v_Collection e
where (a.RecordID=b.RecordID AND a.MachineName=c.RuleName and c.ResourceID=d.ResourceID and c.CollectionID=e.CollectionID)
AND b.AttributeValue IN ('@packageID'))
END
View 1 Replies
View Related
Jul 31, 2007
Hi All:
I have a situation where Im calling a stored procedure to insert some information.
When this information is inserted it is given a date/time stamp - say something in Aug.
There will be some corresponding records from the previous month already in the database that have some additional information that was manually added.
I need to query the corresponding records from the previous month and insert that info into the records that were just inserted.
the problem Im having is that i need to grab the most recent corresponding record. could be a day or a month prior to the one I just inserted. the also could be a record for months prior to that.
So how do i get the most recent corresponding record to my inserted record
Any suggestion on how to query this? and then pass the result to an udpate statement
View 8 Replies
View Related
Jul 23, 2005
Is there any way to do the following?select Max(FieldOne) From (spGetSomeData 100,'test' )Or do I need to define a view and have the stored proc use that view?The stored proc does some things with temp tables that would bedifficult to replicate with a view which is why I'm asking.
View 2 Replies
View Related
Jul 20, 2005
I did a join on two tables to get the following results. I saved theresults in a #temptable.idtable2idtable2descripdateinserted================================================== ==13descrip111/3/200224descrip211/2/200233descrip111/4/200143descrip110/5/200354descrip212/8/200165descrip39/10/2002I want to query that #temptable to get the max date for each table2idand only return those record. So I need a query to get the followresults...idtable2idtable2descripdateinserted================================================== ==24descrip211/2/200243descrip110/5/200365descrip39/10/2002Question...What query can I make with #temptable to give me the results?
View 1 Replies
View Related