Hi,
is it better to use stored procedures or queries in terms of performance? I'm running application in ASP.NET, now the amount of data in the database is not very high, but I expect it'll grow, so I wonder about speed of queries etc.
Is it better to use SELECT ... FROM ... or to prepare stored procedure for such select? What about insert/update/delete?
thanks
Jiri Matejka
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
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
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 RelatedWhen 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?
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
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
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
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!!!
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 RelatedIn 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
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_...
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
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
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 RelatedI 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 RelatedI need some help with the following query:DECLARE @SRV VARCHAR(20), @date smalldatetimeSET @SRV = (select @@servername)SET @date = '20040901'select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name ='Info_Table', Date_of_Records = @date,count(*) AS 'Actual Total' ,max (SER_NO)- min (SER_NO)+1 AS 'Desired total ',count(*) - (max (SER_NO)- min (SER_NO)+1) AS 'Missing Records',min (SER_NO) AS 'MIN SER_NO',max (SER_NO) AS 'MAX SER_NO'from Info_Tablewhere DateTime >= @date and DateTime < dateadd(DAY, 1, @date)I would like to get records of next 30 days from the @date. I can copypaste this query 30 times with different date and get the desiredresults but would prefer to use one query only.If possible would like a add a variable to get the table name by usingthe following query into the above query:SELECT DISTINCT so.nameFROM sysobjects AS soINNER JOIN syscolumns AS scON so.id=sc.idWHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'So basic idea is to run one simple (or complexed) query to get 30 daysdata of many tables select by the above query.Can someone help please?
View 4 Replies View RelatedHello All
I need help on a query or stored procedure in my C# app what i need to do is make a report to our fisheries service every three months the totals of each species of fish every fishermen catchs.
eg Fishermen1
Species1 100Kg
Species2 200Kg
and so on I can get the results using a select query one at a time using parameters FishermenId,SpeciesId and StartDate and EndDate but with over a 100 species it will take forever to do is there a way i can fill a dataset with the results using the FishermensID and start and end date as the parameters and get the totals for all the species that fishermen has court.I have not got reporting services i want to show the results in a datagridview and i can print it out from there.
My tables are
Fishermen Table1
FishermenID int
FishermenName vchar
Details Table2
DetailsID int
FishermenID int
SpeciesID int
SpeciesName vchar
Quantity nchar
Date smalldatetime
Species Table3
SpeciesID int
SpeciesName vchar
SpeciesCode vchar
Hope someone can help
Thanks Barry
I have created a Stored Procedure:SELECT ID, Productname, Price, Desc, img_urlFROM ProductsWHERE (ID = [ -- Products.aspx?ID=x -- ])Question: I want to view the product details for that ID in QueryString on my ASP.Net page www.myhomepage.com/Product.aspx?ID=2. How do I?...Using:FormView1SqlDataSourceVB/ASP.Net 2005 & SQL Server 2005 Express.
View 3 Replies View RelatedHello, my table is clustered according to the date. Has anyone found an efficient way to page through 16 million rows of data? The query that I have takes waaaay too long. It is really fast when I page through information at the beginning but when someone tries to access the 9,000th page sometimes it has a timeout error. I am using sql server 2005 let me know if you have any ideas. Thanks
I am also thinking about switch datavase software to something that can handle that many rows. Let me know if you have a suggestion on a particular software that can handle paging through 16 million rows of data.
Hi,
I need to create a stored procedure, which needs to accept the column name and table name as input parameter,
and form the select query at the run time with the given column name and table name..
my procedure is,
CREATE PROC spTest
@myColumn varchar(100) ,
@myTable varchar(100)
AS
SELECT @myColumn FROM @myTable
GO
This one showing me the error,
stating that myTable is not declared..
.............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters..
Plese help me?? Is it possible in stored procedure..
Hi,
I have set programmaticaly as follows for sql dataadapter
Commandtext="name of stored proc "
commandtype="stored proc"
now I want the query in the stored proc which i will store in the string .is there any way to get the query from sp progrmmaticaly?
Swati
Hi i am trying to make the "userName" section of the code below dynamic as well, how can i do this, the reason being userName will not always be passed through to it.
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)ASdeclare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like ' + char(39) + '%' + @UserName + '%' + char(39)
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = ' + cast( @subCategoryID as varchar(10))if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = ' + cast( @regionID as varchar(10))
print @StaticStr
exec(@StaticStr)
)
Hi, I have a table with values such as test1, test2, test3, test4, test5.
I need to write a stored procedure with paramater (number TINYINT, number2 TINYINT), the number represents the field that I'm going to select and compare. For example if I pass in (1,5) I will need the fields test1 and test5 and store them in Temp and Temp2. How do I write the following to so it will dynamically select which field to use when passing the parameters?
DECLARE @Temp TINYINT,
DECLARE @Temp2 TINYINT,
SELECT top 1 Temp = test1, Temp2 = test5 from table
Hi all,
I have a problem with a stored procedure that executes properly when running it in Query Analyzer. When I call the SP from an ASP.NET application, it doesn't seem to run properly. I have verified that the parameter values are correct, but there is one update command that does not update any rows when it executes although it should. I tried stepping through the SP from within Visual Studio and it still does not work properly even though all parameters have the correct values.
Why would a SP execute properly when used in QA but not when an application executes it?
I am trying to see if there is anyway to BCP a stored procedure from SQL query analyzer. The statement works fine from the command prompt or from within DTS but not from SQL QA.
The bcp statement is as follows:
master..xp_cmdshell "bcp "exec pubs.dbo.sp_employee" queryout dev01e$emp.txt /c /o dev01e$emp.out /T /SDEV01"
sp_employee has the script:
SELECT * FROM EMPLOYEE
Any help is appreciated. Thanks.
AJ
I have a query that select rows from the employees,salary_head and salary_group tables
this is the query
SELECT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
FROM dbo.employees INNER JOIN
dbo.salary_head INNER JOIN
dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
i also have a table called payrollers1 with the following fields
payroll_id int auto
payperiod_id int
employee_id
level_id
designation_id
banks_id
bankaccount_no
salarygroup_id
Amount
I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table.
As for the payperiod_id i want the Stored procedure to look up the max payperiod value.
I am totally new to stored procedure and do not know how to write this code.
Can somebody help me with this code.
I would like to make a selection of records returned by a stored procedure.
E.g.
SELECT Name FROM EXEC somestoredprocedure @age = 25
This is wrong, but is there a way to do this?
Maarten
Hello All!
I have created the following Distributed Query to maintain the current data between local server and Remote server:
EXEC SP ADDLINKEDSERVER 'SSNTDB2'
go
-----UPATE asp org table
set identity insert tn.asp org on
go
insert into tn.asp orhh ( ao key, ao name, ao pid, ao login link, ao login instr top, ao login instr bot,ao login link nm, ao lms enabled, ao lms prefix )SELECT ao key, ao name, ao pid, ao login link, ao login instr top, ao login instr bot,ao login link nm, ao lms enabled, ao lms prefix from SSNTDB2.icomm live.tn.asp org jung w
WHERE jung.ao key not in ( select ao key from tn.asp orhh )
go
set identity insert tn.asp org off
go
---update themesw table
set identity insert tn.themesw on
go
insert into tn.themews (seq nbr,name,source dir,th desc,update by,update dt,partner id,Inst Payer Nbr,def lang key,t status,def audience key)
SELECT seq nbr,name,source dir,th desc,update by,update dt,partner id,Inst Payer Nbr,def lang key,t status,def audience key FROM SSNTDB2.icomm live.tn.themes jun
WHERE jun.seq nbr not in (select seq nbr from tn.themes)
go
set identity insert tn.themesw off
GO
EXEC droplinkedserver 'SSNTDB2'
I want to create the stored procedure to make this script run automatically and create a scheduled job so that the local server can be updated it's records periodically. What is the best way to do this? Any tips will be appreciated..
Thanks John
ok, i have the following query which runs as a stored proc...
ALTER PROCEDURE [dbo].[sp_SelectDocumentBySearch]
@searchString varchar(255)
AS
BEGIN
SELECT document_id, document_code, document_title, document_category_id,
document_description, document_date_added, document_date_revision,
document_last_revised, document_author, document_version, document_level,
category_id, category_title
FROM tbl_documents
JOIN tbl_categories ON document_category_id = category_id
WHERE document_level = '3' AND document_code LIKE @searchString OR document_title LIKE @searchString
OR document_description LIKE @searchString OR category_title LIKE @searchString
END
in my .net app, its a search function, u make the @searchString parameter to be %string% (as a search feature to find anything with 'string')...now, when there is a string present, it works correctly by displaying results only with document_level = '3'....however, when there is no string, that is just a %% as the @searchString, it returns all results, regardless of what document_level is...
but i want to keep it for a wildcard search, yet still retain document_level = '3' statement. is this possible?
Cheers, Justin
hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
I need to execute a stored procedure with a parameter on a part of the table name like this:
SELECT @TSQL='SELECT dbo.ponte_codice_cassa.nome_cass
, dbo.food03.id_counter
, dbo.food03.data
, dbo.food03.barcode
, dbo.food03.descrizione
, dbo.food03.codice_articolo
, dbo.food03.iva
The table name food03 is the parameter i need to change but i would like this
Because I need to recall different table from last 2 character like
food01-->01 is the parameter
food02-->02 is the parameter
food03-->03 is the parameter
dbo.food@parameter is that possible??
how can i refer to the results of a query inside a stored procedure?
and use them later