Stored Procedure, SQL Query And Email
May 25, 2007
I have a situation that I need to accomodate as follows:
Customer account table as a bit value as to whether to send an email
For each customer with a true value set I need to run a query against their products table to check whether the reorder value is less than current stock.
if the recordcount for this query is > 0 then I need to send the customer an email using the address in the customer file.
This process needs to be run once per week.
Can anybody offer advice, samples as to resolve this problem
Thanks
View 3 Replies
ADVERTISEMENT
Jan 4, 2001
Does anyone know how i can send an email from within a stored procedure ? Can i simply use xp_sendmail ?
thanks
View 3 Replies
View Related
Aug 3, 2007
i want to know how can i send emails from SQL stored procedure? is it possible?
View 3 Replies
View Related
Jul 2, 2006
I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
View 6 Replies
View Related
Mar 17, 2004
Hi,
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
Thanks a lot
View 3 Replies
View Related
Jan 30, 2014
I have an store procedure and I want to build an email with this store procedure to email me How can I use the email command to incorporate into my sql SP?
View 4 Replies
View Related
Aug 9, 2007
Hello,I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."Does anyone know what the error means or can point me to a resource on the web? Many thanksRitao CREATE PROCEDURE dbo.SendRenewalEmail @ID INT AS BEGIN exec dbo.sp_send_cdosysmail_htm @From = 'yosemite.sam@acme.com', @To = 'road.runner@acme.com', @Cc = null, @BCC = null, @Subject = 'Order ' + @ID + 'Renewal Reminder', @Body = 'Hello roadrunner....' ENDGO
View 4 Replies
View Related
Oct 24, 2007
hi there,
the subject line says it all. is there a way to send nicely formatted html email from within a stored procedure that returns a dataset?
thanks!
chris
View 2 Replies
View Related
Nov 3, 2007
Everyday morning I email the sql query/stored procedure output results to the users, I was wondering if I can use some kind of t-sql code or DTS packages so that I can automate this process where I want to send the sql/stored proc results in the body of the email.
View 7 Replies
View Related
Sep 20, 2007
Hi Everybody,
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
WHERE
(((r.datDateToSend)<20/12/09) AND
((r.txtSent)='False'))
-- Send the Emails
WHILE(LEN(@To) > 0)
BEGIN
EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg
END
-- Mark the records as sent
IF @Result = 'sp_OAGetErrorInfo'
BEGIN
SELECT @Sent = 'Error'
END
ELSE
BEGIN
SELECT @Sent = 'True'
END
UPDATE auto_reminders
SET
auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today
WHERE
intReminderID = @ReminderID
END
GO
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Thanks in advance
Ben
View 1 Replies
View Related
Jul 20, 2005
Hi,I'm not sure if this is possible as i've googled everywhere, but i have aselect query that returns a customer record with their associated salesorders. I would like to automate a process which sends an email reminder toeach customer in the database, that has outstanding orders. This emailreminder should have the results of the query regarding their account.The table structure are as follows.---------------------Customer_tbl---------------------CustomerIDAccountNoNameEmailAddress---------------------Order_tbl---------------------OrderIDCustomerIDReferenceAmountDateOutstanding_flgCan anyone help?Sen.
View 2 Replies
View Related
Jul 23, 2005
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 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
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 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
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 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
Jul 20, 2005
I 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 Related
May 20, 2008
Hello 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
View 3 Replies
View Related
May 8, 2007
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
View 27 Replies
View Related
Sep 11, 2006
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 Related
Dec 1, 2006
Hello, 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.
View 1 Replies
View Related
