Transact SQL :: Store Date Permanently In Stored Proc
Aug 24, 2015
I have stored procedure which runs on some period, what i want is when it first run i want to store that date permanently or till the next time it runs again, and then i need to take my data from that store proc , where hist_date between (date stored when it ran first time,example, 08/21/2015) and today date.(exampple, 08/24/2015)
Now next time when it runs , date stored should be updated in this case it should be (08/24/2015) .
How can i do this in stored proc, I tried to use temp table but it didn't work .
Hello i have this store proc with the syntax below. The getdate get the current date but i need to change the date this one time to 3/20/2005. I was wondering is there a way to do that an not modify my sp. I tried to harcode 3/20/2005 as asofdate and i get all 0 in my table.
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
SELECT * FROMtbl_Feedback WHERE tbl_Feedback.DateIn >= @startDate GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead ' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command") set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput) set cmd.ActiveConnection=DBConnection cmd.CommandText="sp_009_SiteLead" cmd.Parameters.Append peDate cmd.Parameters.Append psdate psDate.Value = "10/01/2004" Set rsObj = CreateObject("ADODB.Recordset") rsObj.ActiveConnection = DBConnection rsObj.CursorLocation = adUseClient rsObj.PageSize= 20 rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it. Can anyone help?
What is the recommended method of returning a formatted date from a stored procedure?
The date is held in a date time field. I wish to return the date formatted as:
dd/mm/yyyy hh:mm
for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:
dd/mm/yyyy hh:mm:ss
I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.
I was looking at:
select jobHeaders.DateTimeJobTaken AS [Job Taken], CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))
from jobHeaders
but this gives : 8 /3 /2004 with spaces.
Before looking further I thought one of you guys may have the answer.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :
Execute statement is like Exec dbo.storedProc1 select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24' .
Somehow passing the date as parameter is giving me empty result set.
I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd I also tried SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.
'****************************************************************** '****************************************************************** Public Function Add_Cl_Contact(ByVal strContactName As String, _ ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _ ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _ ByVal strPhone3 As String, ByVal strTitle As String, ByVal iContactTypeId As Integer, _ ByRef iResult As Integer, ByRef strError As String) As Boolean '****************************************************************** Dim bSuccess As Boolean = True Dim connect As New SqlConnection(strConnection) Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect) Dim paramReturnValue As SqlParameter
If cmdSelect.Parameters("ReturnValue").Value = 0 Then strError = "Contact has been added" Else strError = strContactName & " already exist!" bSuccess = False End If
Catch ex As Exception bSuccess = False strError = ex.Message Finally If connect.State = ConnectionState.Open Then connect.Close() End If End Try Return bSuccess End Function
For a reason that I dont see my store proc is always returning 0 records but if I use a commandType.text instead of StoredProcedure then I get my results. So I must have some kind of error somewhere. Store Proc ALTER PROCEDURE dbo.Get_Cl_IssuesBySystemID
LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2
ON t1.IssueId = t2.IssueId
Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%@SystemId%'
order by t1.DateCreated
SET NOCOUNT OFF
Function Public Function Get_Cl_IssuesBySystemID(ByVal strSystemId As String) As DataView Dim objDs As New DataSet Dim objDv As New DataView Dim connect As New SqlConnection(strConnection)
'***For CommanType.Text*** Dim strSelect As String
" LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2" & _ " ON t1.IssueId = t2.IssueId" & _
" Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%" & strSystemId & "%'" & _ " order by t1.DateCreated" Dim cmdSelect As New SqlCommand(strSelect, connect) cmdSelect.CommandType = CommandType.Text '***End For CommanType.Text***
'***For CommanType.StoredProcedure*** 'Dim cmdSelect As New SqlCommand("Get_Cl_IssuesBySystemID", connect) 'cmdSelect.CommandType = CommandType.StoredProcedure 'PARAM 'cmdSelect.Parameters.Add("@SystemId", strSystemId) '***End For CommanType.StoredProcedure***
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
Can someone help me with debugging store proc. I am usig VS 2003 and SQL Server 2005 and I have no clue as how to do that if it is possible. I would like to place a break point in my store proc if that is possible. I know if I was in VS 2005 it would be a charm but it's not my case. Thanks
I am trying to do an insert with a SPand I get the following error: "The name 'A2LA Website' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." When iParentID=30 and strTexte="A2LA Website"
What an I doing wrong?
Table: ID int(identity) ParentID int Text nvarchar(50) Valeur nvarchar(50) Allow Null Ordre int Allow Nulls
Public Sub addItemToDdSelection(ByVal strTable As String, ByVal iParentID As Int16, ByVal strTexte As String) Dim connect As New SqlConnection(strConnection) Dim cmdSelect As New SqlCommand("AddDropDownContent", connect) Dim paramReturnValue As SqlParameter Dim strError As String
cmdSelect.CommandType = CommandType.StoredProcedure 'PARAM cmdSelect.Parameters.Add("@intParentID", iParentID) cmdSelect.Parameters.Add("@strTexte", strTexte) Try connect.Open() cmdSelect.ExecuteNonQuery() connect.Close() Catch ex As Exception
strError = ex.Message Finally If connect.State = ConnectionState.Open Then connect.Close() End If End Try End Sub
ALTER PROCEDURE dbo.AddDropDownContent ( @intParentID int, @strTexte varchar(50) ) AS EXEC ('INSERT INTO DropDownMenus (ParentID, Texte) VALUES(' + @intParentID + ',"' + @strTexte + '")')
I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it. Thanks set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn] ( @strSearchTermColumnNamenvarchar (50), @strSearchTermSearchTermnvarchar (200) )
as
if (@strSearchTermColumnName = 'Monitor') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2 FROM Cl_Systems WHERE contains(Monitor1,@strSearchTerm) or contains(Monitor2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'MonitorSerial') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial FROM Cl_Systems WHERE contains(Monitor1Serial,@strSearchTerm) or contains(Monitor2Serial,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'Microscope') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2 FROM Cl_Systems WHERE contains(Microscope1,@strSearchTerm) or contains(Microscope2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'SerialMicroscope') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2 FROM Cl_Systems WHERE contains(SerialMicroscope1,@strSearchTerm) or contains(SerialMicroscope2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'Controller') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2 FROM Cl_Systems WHERE contains(Controller1,@strSearchTerm) or contains(Controller2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'ControllerFirmware') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2Firmware FROM Cl_Systems WHERE contains(Cont1Firmware,@strSearchTerm) or contains(Cont2Firmware,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'SerialController') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2 FROM Cl_Systems WHERE contains(SerialController1,@strSearchTerm) or contains(SerialController2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'Joystick') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2 FROM Cl_Systems WHERE contains(Joystick1,@strSearchTerm) or contains(Joystick2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'JoystickFirmware') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2Firmware FROM Cl_Systems WHERE contains(Joy1Firmware,@strSearchTerm) or contains(Joy2Firmware,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'SerialJoystick') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2 FROM Cl_Systems WHERE contains(SerialJoystick1,@strSearchTerm) or contains(SerialJoystick2,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'Camera') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4 FROM Cl_Systems WHERE contains(Camera1,@strSearchTerm) or contains(Camera2,@strSearchTerm) or contains(Camera3,@strSearchTerm) or contains(Camera4,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'CameraSerial') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4Serial FROM Cl_Systems WHERE contains(Camera1Serial,@strSearchTerm) or contains(Camera2Serial,@strSearchTerm) or contains(Camera3Serial,@strSearchTerm) or contains(Camera4Serial,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'ZMotor') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3 FROM Cl_Systems WHERE contains(ZMotor1,@strSearchTerm) or contains(ZMotor2,@strSearchTerm) or contains(ZMotor3,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'Stage') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3 FROM Cl_Systems WHERE contains(Stage1,@strSearchTerm) or contains(Stage2,@strSearchTerm) or contains(Stage3,@strSearchTerm) return 0 end
if (@strSearchTermColumnName = 'Lens') begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3 FROM Cl_Systems WHERE contains(Lens1,@strSearchTerm) or contains(Lens2,@strSearchTerm) or contains(Lens3,@strSearchTerm) return 0 end
I am trying to code a store procedure using nested cursor and I don't know where I am doing wrong. I am getting message:
Server: Msg 170, Level 15, State 1, Procedure sp_alicare_99, Line 212 Line 212: Incorrect syntax near 'first_cursor'.
Can someone please help me with this, it's very urgent and I have to use it for production purposes.
Here is the store proc:
-- 05/24/2000 V1.0 -- populate Los Table
print 'sp_alicare_99' go if exists(select name from sysobjects where name='sp_alicare_99' and type='P') drop procedure sp_alicare_99 go CREATE PROC sp_alicare_99 As Begin /********** * sp_alicare_99 (V603.132) ***********/
Hey, How do I put anything there for an INT type??? It is in a stored procedure and I have six parameters that I am passing in, some are blank and I want to put a default all variable in the where statement.
Select tbl_EventDate.EventDate , tbl_EventDate.EventDateID_p FROM tbl_EventDate WHERE EventDateID_p = %
This does not work.... I tried * too..... hmmmm....
@usrcode as varchar(5) = '%', @disch_dt_start as varchar (12) = '%', @disch_dt_end as varchar (12) = '%', @client_id char(2) = '%' as
select distinct t.patient_id,p.first_name,p.last_name,convert(char (12),p.birthdate,101) Birthdate,sex,t.auth_id,t.place_of_service,convert (char(12),t.discharge_date,101) Discharge_date, i.service_id from patient_transaction t inner join inpatient_service i on t.patient_id = i.patient_id and t.tran_id = i.tran_id and CONVERT(VARCHAR,ISNULL(i.service_id ,0)) LIKE @usrcode and t. place_of_service = '1' and t.decision in ('1', '2') and t.discharge_date between convert(char(12),@disch_dt_start,101) and convert(char(12),@disch_dt_end,101) and CONVERT(VARCHAR,ISNULL(substring(t.patient_id,1,2) ,0)) LIKE @client_id inner join patient p on t.patient_id = p.patient_id order by 1
I want to get all clients if I don't specify the parameter @client_id but when I do that I get 0 records. What am I doing wrong??
i m new in the world of database so please help me to create a perticular store proc
i want to get data from 3 tables and 2 field of a table is match with the same field of another table and it cant work i write that code so u can understand easily but it dont work so please modify that and make it workable
select a.a_dis_id,b.name as fromname,b.name as toname,a.a_dis_km,c.a_all_name,c.a_all_rate from a_distance a,levels b,a_allowance c where a.a_dis_from=b.cid and a.a_dis_to=b.cid and c.a_all_id=a.a_all_id
---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot. select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else ---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan) ---but Shaggy has sponsored 2 others select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy ---I also want to get their information ID,fname,lname ---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain: ---Let's assume that we have an OAK Tree that has 4 main branches ---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible --the way I have it now taking way too long --because in within so many (do while loop)
TIA Please pardon me, I could not find better layout to explain this.
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I am looking for a store procedure which validates certain keywords like delete,truncate,update,insert etc. and restricts them to be used by users in all of my store procs which takes strings as inputs.
Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??
I have so many created allready and it will be to long to go throught each one of them to modify my text inside.
what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!
One of the Sql server crashed and when the server restart, the Store Procs have locked icon. Can't modify or do anything with it, but the proc still run. What could have cause this, and how can I remove the lock icon?
