Stored Procedure - Date Function
Hi, I ran into some problem here. The case scenerio is supposed to be like this:
- Each member can only make one appointment at any one time and only
make another appointment after the existing appointment expired.
- Each member is allowed to make an appointment at any time and must be at least 5 days in advance.
I managed to do the reservation for at least 5 days in advance but I
can't allow the member to make only one appointment. The member can
keep making appointments even though the existing appointment has not
expired. Can someone pls help? Thanks!
ALTER PROCEDURE spReserveAppt(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS(SELECT MemNRIC FROM DasMember WHERE MemNRIC = @MemNRIC)
RETURN -300
BEGIN
IF EXISTS
(SELECT COUNT(@MemNRIC)
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 5)
GROUP BY MemNRIC
HAVING COUNT(@MemNRIC) <> 0)
RETURN -301
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, @AppDate ,GETDATE()) > GETDATE()))
RETURN -302
END
END
INSERT INTO DasAppointment(AppDate, AppTime, MemNRIC) VALUES (@AppDate, @AppTime, @MemNRIC)
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spReserveAppt '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
View Complete Forum Thread with Replies
Related Forum Messages:
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View Replies !
SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 Stored procedure: Alter PROCEDURE [dbo].[sp_GetIdeaByCategory] @CatId <span class="kwd">int</span> = 0 AS BEGIN SET NOCOUNT ON; Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc End oDataSource.ConnectionString = constr; oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span> oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId); gdvCategories.DataSourceID = oDataSource.ID; gdvCategories.DataBind(); <<--- Error occured here
View Replies !
Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; } As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help! Thanks Nickdel68
View Replies !
SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View Replies !
Calling A Stored Procedure Or Function From Another Stored Procedure
Hello people, When I am trying to call a function I made from a stored procedure of my creation as well I am getting: Running [dbo].[DeleteSetByTime]. Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous. No rows affected. (0 row(s) returned) @RETURN_VALUE = Finished running [dbo].[DeleteSetByTime]. This is my function: ALTER FUNCTION dbo.TTLValue ( ) RETURNS TABLE AS RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true' This is my stored procedure: ALTER PROCEDURE dbo.DeleteSetByTime AS BEGIN SET NOCOUNT ON DECLARE @TTL int SET @TTL = dbo.TTLValue() DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime) END CreatedTime is a datetime column and TTL is an integer column. I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it. Your help is much appreciated.
View Replies !
Function Vs Stored Procedure
I know this is a stupid question (actually, maybe its not..?) They seem to be identical in some ways, but not available to the outside world. what are some differences?
View Replies !
When To Use A Function And When To Use A Stored Procedure.
Hi, I thought that procedures do not return a value: well they only return a 1 or 0 depending on whether the procedure executed correctly. Functions are what you're supposed to use when you want to get a result set of some sort: ie a table or a scalar value. Apparently that is not the case becuase you can return values from procedures. I've attempted to find out what the differences are between the two and when it is appropriate to use a procedure and when it is appropriate to use a function but I'm still unsure. Can someone please tell me the difference. Thanks. Przemek
View Replies !
Function From Stored Procedure
Hi Everyone, i want to create a function in Stored procedure and then call this function that returns true or false from a stored procedure . is this possible? please help me if you know something that can help me. thanks Tvin
View Replies !
Calling A Function From A Stored Procedure
Hello all, I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error. I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs. Example of tool SELECT statement:SELECT tool_number, tool_description FROM tool WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string) This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name. Function code (removed some wrapping code for simplicity):SELECT manufacturer_id FROM manufacturer WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT * FROM UDFmanufacturer_SearchName('mol') SELECT * FROM tool WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT * FROM ato_tool WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol')) I'm stuck. I haven't been able to find anything that shows me where I'm going wrong. Any thoughts or suggestions are appreciated. Thanks,Jay
View Replies !
Call Function From Stored Procedure
Hi All, I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions: 1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject. 2) Can someone provide any links to online tutorials on t-sql? Thanks in advance. Curtis
View Replies !
Use Function From Asp File In Stored Procedure
Hello everybody,I have a file with several asp functions where some depend on others.Some of the functions I will need to transform a string value.How can I include these functions in a stored procedure? (in SqlServer 2000)Do I have to add all the functions as userdefined functions?Or is possible to reference the external file like include files inasp?Thank you for your help!Regards,Michael
View Replies !
ASP/SQL Stored Procedure/ASP Function Error
Hey I have the following Stored Procedure CREATE PROCEDURE spGetOrderCount ( @search varchar(1000) = default ) AS SET NOCOUNT ON /* Setup search string */ IF (@search <> '') BEGIN SET @search = 'WHERE' + @search END /* Create a temporary table */ CREATE TABLE #TempTable ( row int IDENTITY, totalCount int ) /* Insert the search results into query */ EXEC ( 'INSERT INTO #TempTable([totalCount])' + 'SELECT COUNT(*) AS totalCount ' + 'FROM tblOrders' + @search ) /* Extract the wanted records from the temporary table */ SELECT[totalCount], RecordsLeft = ( SELECT COUNT(*) FROM #TempTable TI ) FROM#TempTable SET NOCOUNT OFF RETURN ; GO And then the following function which specifies the where clause of the statement function getOrderCount(strDate, strStatusList) getOrderCount = 0 dim objRS, objSP, strWhereClause if isDate(strDate) and len(strStatusList) > 0 then '# Filter on order status '# Filter on date clause strWhereClause = "(tblOrders.orderDate >= " & sqlServerDate(strDate) & ")" '#GET order count Set objSP = SQLGetProcedure("spGetOrderCount") SQLSetProcedureParam objSP, "search", strWhereClause Set objRS = SQLExecuteProcedure(objSP) if not objRS.eof then getOrderCount = objRS("totalCount") end if '# Free resources deleteRecordset(objRS) deleteObject(objSP) end if end function When I do this together I get the following error on my ASP Page Microsoft OLE DB Provider for SQL Server error '80040e14' Line 1: Incorrect syntax near '.'. /sigma_eircommobdispatch/server/database.asp, line 235 And the print out of the resulting string from the function is (tblOrders.orderDate >= CONVERT(DATETIME, '2007-7-13', 102)) If i remove the Where clause the statement works fine.. Any ideas
View Replies !
RESOLVED: Stored Procedure Of Function?
I have a select statement like this. Notice that I am doing the same calculation on different fields. Is it possible I can make this, in coding terms, a fucntion; so I can call it when I like (this query) and just provide the field. Example: select field1, test_field = secs_to_hhmmss(TALK_TIME) from ... SELECT dbo.date_table.real_date, dbo.time_table.hh, COUNT(dbo.CALLDETAIL.id) as NumOfCalls, TalkTime = CASE WHEN CAST(SUM(TALK_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END + RTRIM(CAST(SUM(TALK_TIME) AS INTEGER)/3600) + ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) / 60),2) + ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) % 60),2), HoldTime = CASE WHEN CAST(SUM(HOLD_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END + RTRIM(CAST(SUM(HOLD_TIME) AS INTEGER)/3600) + ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) / 60),2) + ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) % 60),2),
View Replies !
Using A Function To Pass A Parameter To A Stored Procedure
In the snippet below, ExecuteSqlString is a stored procedure that accepts one parameter. SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass. I can't seem to find the correct syntax to get it to work. The way it is show below, the error comes back about incorrect syntax near ')' . Is this doable? <asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>" SelectCommandType=StoredProcedure SelectCommand="ExecuteSqlString"> <selectparameters> <asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/> </selectparameters> </asp:SqlDataSource>
View Replies !
Stored Procedure - User Defined Function.
Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn
View Replies !
User-Defined-Function With-in Stored-Procedure??
Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help. Code: CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2)) RETURNS VARCHAR(2) AS BEGIN IF (LEN(@sValue) < 2) SET @sValue = '0' + @sValue RETURN @sValue END Thanks...
View Replies !
How To Use User Defined Function In Stored Procedure?
Hello friends, I want to use my user defined function in a stored procedure. I have used it like , select statement where id = dbo.getid(1,1,'abc') //dbo.getid is a user defined function. procedure is created successfully but when i run it by exec procedurename parameter I get error that says "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getid", or the name is ambiguous." Can any body help me? Rgds, Kiran.
View Replies !
Maximum Stored Procedure, Function, Trigger, Or Vi
HI ALL, I AM USING SQL SERVER 2005. I HAVE RETURN A RECURSIVE FUNCTION TO FIND OUT WHETHER THE NEXT DATE DOES NOT FALL WITHIN HOLIDAYS BUT I AM GETING THIS ERROR Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). THE CODE I USED IS alter FUNCTION [dbo].[GetNextDay](@dt datetime , @empcode varchar(50) ) RETURNS datetime AS BEGIN DECLARE @zoneid VARCHAR(50) declare @lvlflag varchar(50) declare @utdt DATETIME DECLARE @RETDT DATETIME DECLARE @COMPDT DATETIME Select @lvlflag= b.ulm_user_field_flag from bbraun_emis.dbo.emp_reference a join bbraun_emis.dbo.user_lvl_master b on b.ulm_user_lvl_id = a.ER_USER_LVL and a.er_emp_code = @empcode SELECT @zoneid = ZONECODE FROM bbraun_emis.dbo.VWREGIONLINK WHERE CITYCODE IN (SELECT DISTINCT HM_CITY_CODE FROM bbraun_emis.dbo.HOSP_MASTER WHERE HM_HOSP_CODE IN (SELECT HER_HOSP_CODE FROM bbraun_emis.dbo.HOSP_EMP_REL WHERE HER_EMP_CODE in(@EMPCODE))) select @compdt = holiday_date from oriffconnect.dbo.holiday_master where zone_code = @zoneid and field_staff = @lvlflag and holiday_date = @dt if(@@ROWCOUNT = 0) begin Select @utdt = DATEADD(dd,1,@utdt) SeT @utdt = ([dbo].[GetNextDay](@utdt , @empcode)) end IF(@@ROWCOUNT <> 0) begin set @utdt = @dt end Select @RETDT = @utdt RETURN @RETDT END PLEASE HELP
View Replies !
Extended Stored Procedure Problem (srv_describe Function)...
Hello all,I have spend quite some time now but don't manage tofind out how to have the srv_describe function workingproperly for target NUMERIC(x,x) and NUMERIC types.Already tried several alternatives like e.g.// ...PVOID pvdata1;strcpy((char*)pvdata1, "12345.6789");wsprintf(colname, "Score");srv_describe(srvproc, 1 /* column #1 */, colname /* column name */, SRV_NULLTERM /* column name ending */, SRVNUMERIC, (DBINT)sizeof(DBNUMERIC), SRVNUMERIC, (DBINT)sizeof(DBNUMERIC), pvdata1);srv_setcoldata(srvproc, 1, pvdata1);srv_setcollen (srvproc, 1, strlen((char*)pvdata1));// ...but obviously does not work, I get an empty resultset.I could only manage by converting the numeric valueto string from C and then sending only strings but theSQL side expects actually a number(x,x) and is a customer, in which case, I would never propose to be casting thething from TSQL.Any ideas how to do it? The documentation and examples isquite limited in this area.Thanks in advance,Best Regards,Giovanni
View Replies !
Stored Procedure And Calling User Defined Function
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-) Short list of process flow: 1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER 2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient 3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient 4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page 5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny. <FLOW WITH CODE AND FUNCTIONS :> This is not the correct syntax but is showing what I understand sort of how this is to be done so far. This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process. This is code in Form.asp 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check Postback Type 'We need to load up vendors associated with the current client. '--------- CHECK ACCESS HERE via function ab_HasAccessToClient -------- 'If the call returns 1, then the employee has access. 'Otherwise, just write out "Access to this client is denied." 'CALL SP - Not sure what parameters need to go with it or its syntax Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1) 'When it returns can check it here........ if ab_HasAccessToClient result is a 1 then 'boolean would be 1 so show panel Else 'boolean would be 0 so show access denied 'allow them to go back to the original page. end if 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > ON SQL SERVER: Stored Procedure ---------------------------------------------------------- -------------------------------- rx_sp_HasAccessToClient CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] @EmployeeID INT, @ClientSSN varchar(50), @ReturnBitValue = OUTPUT /* ' Parameters here passed via call from Form.asp - not sure what is passed yet. */ AS set nocount on /* Written by Mike Belcher 9/27/2007 for Form.asp 'Calls ab_HasAccessToClient function - not sure of the syntax as of yet, just making flow. 'Gets return bit and passes that back to the call from Form.asp */ GO ---------------------------------------------------------- -------------------------------- ON SQL SERVER: User-Defined Function ---------------------------------------------------------- -------------------------------- ab_HasAccessToClient CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN VARCHAR(50)) @ClientSSN varchar(50), @EmployeeID, @ReturnBitValue = OUTPUT AS SELECT 1 FROM tblEmployeesClients ec INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName WHERE e.EmployeeID= @EmployeeID AND c.InActiveClient=0 AND c.ClientSSN = @ClientSSN 'Some Code here to save result bit .. RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient ---------------------------------------------------------- -------------------------------- </FLOW WITH CODE AND FUNCTIONS :>
View Replies !
Stored Procedure And Calling User Defined Function
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-) Short list of process flow: 1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER 2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient 3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient 4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page 5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny. <FLOW WITH CODE AND FUNCTIONS :> This is not the correct syntax but is showing what I understand sort of how this is to be done so far. This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process. This is code in Form.asp 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check Postback Type 'We need to load up vendors associated with the current client. '--------- CHECK ACCESS HERE via function ab_HasAccessToClient -------- 'If the call returns 1, then the employee has access. 'Otherwise, just write out "Access to this client is denied." 'CALL SP - Not sure what parameters need to go with it or its syntax Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1) 'When it returns can check it here........ if ab_HasAccessToClient result is a 1 then 'boolean would be 1 so show panel Else 'boolean would be 0 so show access denied 'allow them to go back to the original page. end if 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > ON SQL SERVER: Stored Procedure ---------------------------------------------------------- -------------------------------- rx_sp_HasAccessToClient CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] @EmployeeID INT, @ClientSSN varchar(50), @ReturnBitValue = OUTPUT /* ' Parameters here passed via call from Form.asp - not sure what is passed yet. */ AS set nocount on /* Written by Mike Belcher 9/27/2007 for Form.asp 'Calls ab_HasAccessToClient function - not sure of the syntax as of yet, just making flow. 'Gets return bit and passes that back to the call from Form.asp */ GO ---------------------------------------------------------- -------------------------------- ON SQL SERVER: User-Defined Function ---------------------------------------------------------- -------------------------------- ab_HasAccessToClient CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN VARCHAR(50)) @ClientSSN varchar(50), @EmployeeID, @ReturnBitValue = OUTPUT AS SELECT 1 FROM tblEmployeesClients ec INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName WHERE e.EmployeeID= @EmployeeID AND c.InActiveClient=0 AND c.ClientSSN = @ClientSSN 'Some Code here to save result bit .. RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient ---------------------------------------------------------- -------------------------------- </FLOW WITH CODE AND FUNCTIONS :>
View Replies !
Strange Problew With User Defined Function Or Stored Procedure
I am trying to add a simple case statement to a stored procedure oruser defined function. However when I try and save thefunction/procedure I get 2 syntax errors. Running the query in queryanalyser works fine and a result is given with no syntax errors. Ibelieve its something to do with the spaces in the field names. Not mychoice as its an existing system I have to work around. Any helpgreatly appreciatedSQL QueryDECLARE @pfid VARCHAR(100)SET @pfid = '000101'SELECTCaseWHEN GetDate()BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfidUser Defined Function (Errors Line 11 & 15)CREATE FUNCTION dbo.get_Margin(@pfid VARCHAR(100), @dtNow DATETIME)RETURNS DECIMAL ASBEGINDECLARE @Return as DECIMALSET @Return = (SELECTCaseWHEN @dtNowBETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfid)RETURN @ReturnEND
View Replies !
Insert Stored Procedure With Error Check And Transaction Function
Hi, guys I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table. could you correct my code, if you know what is the problem? thanks My contract table DDL: ************************************************** *** create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) ); My insert stored procedure is: ************************************************** ***** create proc sp_insert_new_contract ( @contractDate[smalldatetime], @tuition [money], @studentId[char](4), @contactId[int]) as if not exists (select studentid from student where studentid = @studentId) begin print 'studentid is not a valid id' return -1 end if not exists (select contactId from contact where contactId = @contactId) begin print 'contactid is not a valid id' return -1 end begin transaction insert into contract ([contractDate], [tuition], [studentId], [contactId]) values (@contractDate, @tuition, @studentId, @contactId) /*Error Check */ if @@error !=0 or @@rowcount !=1 begin rollback transaction print ‘Insert is failed’ return -1 end print ’New contract has been added’ commit transaction return 0 go
View Replies !
Convert Access Query Dateserial Function Into Stored Procedure
Hi, Below is a access query which is using dateserial function. can you please convert the below query into equivalent TSQL using function and calling that function into the stored procedure. "select DateSerial([cy],IIf([mois_activity_plan]="Janvier",1,Null,1) from Table" datatype for "cy" and "mois_activity_plan" are "text" in the table. Dateserial function has the format:Dateserial(year,month,day) please help. Many thanks, rakesh
View Replies !
Date Stored Procedure
Hi all,I am trying to write a stored procedure that will delete records in atable that are older then 30 days. I am checking against a field thatis called PositionDate. Can someone point me in the right direction?TIA
View Replies !
Stored Procedure With Date
There is a stored procedure to run each Sunday. I would like to get the order date from previous Sunday through Saturday. Right now we code as "OrderDate Between convert(datetime, '05/02/99') AND convert(datetime, '05/08/99') " Each week the dates have to be manually changed. How to use the date function to make it automated?
View Replies !
Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table. Code Snippet [SqlFunction ( FillRowMethodName = "FillRow", TableDefinition = "CustomerID nvarchar(MAX)", SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read, IsDeterministic=false)] public static IEnumerable GetWishlist () { using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) { List<string> myList = new List<string> (); conn.Open (); SqlCommand command = conn.CreateCommand (); command.CommandText = "GetObject"; command.Parameters.AddWithValue ( "@map", "Item" ); command.CommandType = System.Data.CommandType.StoredProcedure; using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) { if (reader.Read ()) { myList.Add ( reader[0] as string ); } } return (IEnumerable)myList; } } When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine. Code SnippetEXEC GetObject 'Item' Is there some sorf of trick I am missing? Thank you!
View Replies !
Group By Date In Stored Procedure
I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this. Select count(recordid),transactiondatefrom sometable group by transactiondate Thanks in advance
View Replies !
Stored Procedure - Sql Date Select
Having a little trouble getting this one to work. I have a stored procedure that selects items from the table... SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews I need to change it so it selects only the year needed. For instance, only items from 2004. SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews WHERE fldDate = @myParameter -Say myParameter = 2004 or whatever year is needed The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure. How can I be sure to only get a certain year? Thanks, Zath
View Replies !
Stored Procedure And Date For Today
Hi there, I am trying to ascertain how many users have registered with my site today. I am using the following stored procedure: CREATE Procedure spGetUserCountToday As Return ( SELECT Count(*) FROM tblUserList WHERE role= "User" AND registerDate >= GETDATE() ) GO However the issue is the GetDate() function will only return those that have resgistered at the exact moment the query is run. How can I change the GetDate to return only those users who have registered in?? Thanks in advance, TCM
View Replies !
Stored Procedure Date Question
The following is part of my stored procedure where I could use some help:If I remark out the two lines regarding dates, it runs fine. The dateparameter is passed from a VB app as a text value in the format mm/dd/yy.Thanks for any help.CharlieSELECT * FROM dbo.TWQRHISTEARDOWN_HEADER where(WQR_TDH_MODELYEAR_T=@Model_Year OR @Model_Year IS NULL)AND (WQR_TDH_OFFLINEDT_Y>=@Start_Date OR @Start_Date IS NULL)AND (WQR_TDH_OFFLINEDT_Y<=@Stop_Date OR @Stop_Date IS NULL)
View Replies !
Date Range In Stored Procedure
I have a stored procedure (below) to which I pass several input parameters, 2 of which are dates (@in_ToDate and @in_FromDate) to allow me to calculate a date range. When I use the date range in the query I don't get a record set back. The data within the specified date range exists. I know that because the same query works fine and returns the right results when run by it self in the query analyser. It also works ok if I hard code the dates in the stored procedure. I can not work out what I am doing wrong. I would greatly appreciate any help on this The code in question is identified by the /* FIXME */ comment below. Thank you /************************************************** ************** * Procedure: SP_ReportStoreBenchmark * * Author: Jerry Vanicky * * Date: 20/11/2000 * * Description: * * * Table: * * ************************************************** **************/ PRINT 'CREATE PROCEDURE SP_ReportStoreBenchmark' -- section for deleting all version of existing procedure if exists (select * from sysobjects where id = object_id('SP_ReportStoreBenchmark') and sysstat & 0xf = 4) drop procedure SP_ReportStoreBenchmark PRINT 'Existing Procedure SP_ReportStoreBenchmark Dropped!' GO -- create the procedure CREATE PROCEDURE SP_ReportStoreBenchmark ( @in_ObjectIDint, @in_ToDatedatetime, @in_FromDatedatetime, @in_Industryint, @in_MediaFormatTypes int, @in_Genresint, @out_error_codeintOUT,-- error code, 0 if no error found @out_error_messagenvarchar(200)OUT-- error description relating to above error code ) AS DECLARE @l_proc_namenvarchar(100) DECLARE @l_yourStorereal DECLARE @l_yourStoreTempreal DECLARE @l_yourStatereal DECLARE @l_nationalreal DECLARE @l_thisStateIDint DECLARE @l_stateAverageSales int DECLARE @l_nationalAverageSales int DECLARE @l_statePercentreal DECLARE @l_nationalPercentreal DECLARE @l_stateStoreCountint DECLARE @l_nationalStoreCountint DECLARE @l_ToDatedatetime DECLARE @l_FromDatedatetime -- person and store details that are requesting this report DECLARE @l_lastnamenvarchar(50) DECLARE @l_firstnamenvarchar(50) -- the details posted into the report DECLARE @l_industrynvarchar(50) DECLARE @l_genrenvarchar(50) DECLARE @l_categorynvarchar(50) DECLARE @l_totalnvarchar(50) BEGIN -- initialise status parameters SET@out_error_code = 0 SET@out_error_message = 'OK' SET@l_proc_name = 'SP_ReportStoreBenchmark' PRINT '--- SP_ReportStoreBenchmark starts here --- ' PRINT 'Input Parameters Passed Into ' + @l_proc_name + ' Are:' PRINT '@in_ObjectID :' + CONVERT(nvarchar(10), @in_ObjectID) PRINT '@in_ToDate :' + CONVERT(nvarchar(12), @in_ToDate) PRINT '@in_FromDate :' + CONVERT(nvarchar(12), @in_FromDate) PRINT '@in_Industry : ' + CONVERT(nvarchar(10), @in_Industry) PRINT '@in_MediaFormatTypes : ' + CONVERT(nvarchar(10), @in_MediaFormatTypes) PRINT '@in_Genres : ' + CONVERT(nvarchar(10), @in_Genres) -- set the dates to the dd mon yyyy format eg.(1 Jan 2000) SET @l_ToDate = CONVERT(datetime, @in_ToDate, 113) SET @l_FromDate = CONVERT(datetime, @in_FromDate, 113) -- get the person and store details requesting this report PRINT 'Getting the person and store details that are requesting this report.' SELECT @l_firstname = tblPerson.FirstName, @l_lastname = tblPerson.LastName FROM tblObject INNER JOIN tblPerson ON tblObject.PersonID = tblPerson.PersonID WHERE (tblObject.ObjectID = @in_ObjectID) -- get the parameter names used by this report to filter on PRINT 'Get the parameter names used by this report to filter on.' -- the industry name SELECT @l_industry = Name FROM tblLookup WHERE Type = 20 AND id = @in_Industry -- the genre name SELECT @l_genre = Name FROM tblLookup WHERE type = 133 AND id = @in_Genres -- the category name SELECT @l_category = Name FROM tblLookup WHERE type = 25 AND id = @in_MediaFormatTypes -- Task 1 PRINT '<task 1 - get the stateID of this state>' -- retrieve this state id SELECT @l_thisStateID = tblAddress.StateID FROM tblObject INNER JOIN tblContactAddress ON tblObject.ContactAddressID = tblContactAddress.ContactAddressID INNER JOIN tblAddress ON tblContactAddress.AddressBillingID = tblAddress.AddressID WHERE (tblObject.ObjectID = @in_ObjectID) PRINT '@l_thisStateID : ' + CONVERT(nvarchar(10), @l_thisStateID) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to retrieve the storeID for storeID (' + @in_ObjectID + ').' PRINT 'Unable to retrieve the stateID for storeID (' + @in_ObjectID + ').' GOTO error_lbl END PRINT '<task 2 - sum the sales for your store>' PRINT '@in_FromDate : ' + CONVERT(nvarchar(20), @in_FromDate) PRINT '@in_ToDate : ' + CONVERT(nvarchar(20),@in_ToDate ) PRINT '@in_Industry : ' + CONVERT(nvarchar(20),@in_Industry ) PRINT '@in_MediaFormatTypes : ' + CONVERT(nvarchar(20),@in_MediaFormatTypes) PRINT '@in_Genres : ' + CONVERT(nvarchar(20),@in_Genres ) PRINT '@in_ObjectID ' + CONVERT(nvarchar(20),@in_ObjectID) /* FIXME */ SELECT @l_yourStore = SUM(SaleAmt) FROM tblReportStoreBenchmark WHERE Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres AND StoreID = @in_ObjectID AND PurchaseDate BETWEEN convert(datetime, @in_FromDate, 113) AND convert(datetime, @in_ToDate, 113) PRINT 'The following line should display the store count of the sales.' PRINT '@l_yourStore : ' + CONVERT(nvarchar(10), @l_yourStore) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum store sales for storeID (' + @in_ObjectID + ').' PRINT 'Unable to sum store sales for storeID (' + @in_ObjectID + ').' GOTO error_lbl END PRINT '<task 3 sum the sales for your state>' -- get the data for the state SELECT @l_yourState = SUM(SaleAmt) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres AND State = @l_thisStateID PRINT '@l_yourState : ' + CONVERT(nvarchar(10), @l_yourState) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum state sales for stateID (' + @l_thisStateID + ').' PRINT 'Unable to sum state sales for stateID (' + @l_thisStateID + ').' GOTO error_lbl END PRINT '<task 4 sum the sales for the nation>' -- get the national data SELECT @l_national = SUM(SaleAmt) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres PRINT '@l_national : ' + CONVERT(nvarchar(10), @l_national) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum national sales.' PRINT 'Unable to sum national sales.' GOTO error_lbl END PRINT '<task 5 get the count of the stores for the state>' -- get the state wide count of all stores SELECT @l_stateStoreCount = COUNT(DISTINCT StoreID) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres AND State = @l_thisStateID PRINT '@l_stateStoreCount : ' + CONVERT(nvarchar(10), @l_stateStoreCount) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to count stores for the stateID (' + @l_thisStateID + ').' PRINT 'Unable to count stores for the stateID (' + @l_thisStateID + ').' GOTO error_lbl END PRINT '<task 6 get the count of the stores for the nation>' -- get the national count for all the stores SELECT @l_nationalStoreCount = COUNT(DISTINCT StoreID) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres PRINT '@l_nationalStoreCount : ' + CONVERT(nvarchar(10), @l_nationalStoreCount) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to count stores nationaly.' PRINT 'Unable to count stores nationaly.' GOTO error_lbl END PRINT 'All varaibles have been set, their values are:' PRINT '@l_thisStateID : ' + CONVERT(nvarchar(10), @l_thisStateID) PRINT '@l_yourStore : ' + CONVERT(nvarchar(10), @l_yourStore) PRINT '@l_yourState : ' + CONVERT(nvarchar(10), @l_yourState) PRINT '@l_national : ' + CONVERT(nvarchar(10), @l_national) PRINT '@l_stateStoreCount : ' + CONVERT(nvarchar(10), @l_stateStoreCount) PRINT '@l_nationalStoreCount : ' + CONVERT(nvarchar(10), @l_nationalStoreCount) PRINT '<task 7>' BEGIN PRINT '<task 7a>' -- get the state average sales SET @l_stateAverageSales = @l_yourState/@l_stateStoreCount PRINT '@l_stateAverageSales: ' + CONVERT(nvarchar(10), @l_stateAverageSales) -- get the national average sales SET @l_nationalAverageSales = @l_national/@l_nationalStoreCount PRINT '@l_nationalAverageSales: ' + CONVERT(nvarchar(10), @l_nationalAverageSales) -- set the statepercentage figure SET @l_statePercent = (@l_yourStore/@l_stateAverageSales)*100 PRINT '@l_statePercent contains: ' + CONVERT(nvarchar(10), @l_statePercent) -- set the national percentage figure SET @l_nationalPercent = (@l_yourStore/@l_nationalAverageSales)*100 PRINT '@l_nationalPercent contains: ' + CONVERT(nvarchar(10), @l_nationalPercent) SELECT @l_yourStore AS storeTtl, @l_stateAverageSales AS stateAverage, @l_nationalAverageSales AS nationalAverage, @l_statePercent AS statePercentage, @l_nationalPercent AS nationalPercentage, @l_FirstName AS FirstName, @l_LastName AS LastName, @l_industry AS industry, @l_genre AS genre, @l_category AS category, @l_total AS total, @in_ObjectID AS objectID, @in_ToDate AS toDate, @in_FromDate AS fromDate --SELECT @l_statePercent AS StatePercent, @l_nationalPercent AS NationalPercent, @l_yourStore AS thisStore, @l_yourState AS thisState, @l_national AS thisNational, IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Error in calculating the total results.' PRINT 'Error in calculating the total results.' GOTO error_lbl END END IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to retrieve the Benchmark report for the ID of (' + @in_ObjectID + ').' PRINT 'Unable to retrieve the Benchmark report for the ID of (' + @in_ObjectID + ').' GOTO error_lbl END PRINT '--- SP_ReportStoreBenchmark ends here ---' -- return successfully RETURN(0) error_lbl: IF (@out_error_code = 0) SELECT @out_error_code = 1, @out_error_message= 'Unable to retrieve the Benchmark report.' -- return with errors RETURN(1) END GO PRINT 'Created Procedure SP_ReportStoreBenchmark!' PRINT 'Done...'
View Replies !
Stored Procedure Creation Date
Is there any system stored procedure or any table that contains the creation date of stored procedures in one database . I really want to know wich is the last stored procedure created in a database. Thanks.
View Replies !
Format Date In A Stored Procedure
Dear friends, I have a stored procedure that returns some fiels. One of the fields is a datetime type. The field return in the follow format : 2006-11-13 0:00:00 How can I return only 2006-11-13? How can I use the format function? regards!!!
View Replies !
Stored Procedure - Date Problems
I have a stored date in my database, example 12 October 2000 which is a start date - now i would like to retrieve this data and the number of years and months from the current date (that is today - i am using the getdate() method)...how can i get the number of years and months passed? Thanks
View Replies !
Date Return From Stored Procedure...help!
Hi, I created this stored procedure to add 90 days to the current date, and return the new date: CREATE PROCEDURE Get90Days AS SELECT DATEADD(day, 90, current_timestamp) AS "RETURNDAYS" The procedure above returns this result: 2006-09-29 07:31:14.477 I need this procedure to return the date, only, like this: 09-29-2006 I tried using the convert switch to reformat the result to the date only, but have not been successful. What does this stored procedure need to look like to return just the date, like this: 09-29-2006 I appreciate your help. Thanks, Bill
View Replies !
Stored Procedure To Check Date Range
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck 2 @PID INTEGER = -1 OUTPUT, 3 @PCityFrom Char(3) = '', 4 @PCityTo Char(3) = '', 5 @PDateFrom DATETIME = '31 Dec 9999', 6 @PDateTo DATETIME = '31 Dec 9999', 7 @PIsExists BIT = 1 OUTPUT 8 AS 9 10 CREATE TABLE #TmpControlRequst 11 ( 12 IDINTEGER, 13 IsExistsCHAR(1) 14 ) 15 /*###Pseudo 16 1. Check the Date From and Date To 17 -- select all the value equal to parameter cityFrom and cityTo 18 -- insert the selection records into tmp table 19 --*/ 20 INSERT INTO #TmpControlRequst 21 (ID, IsExists) 22 SELECT ID, 23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 35 ELSE 0 END 36 FROM RequestTable 37 WHERE ID <> @PID 38 AND CityFrom = @PCityFrom 39 AND CityTo = @PCityTo 40 41 --======== FINAL RESULT 42 -- For tmp table:- 43 -- isExists = 1 ==> date lapse 44 -- isExists = 0 ==> date ok 45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting 46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1 47 ELSE 0 END 48 FROM #TmpControlRequst 49 WHEREIsExists = 1 50 51 SELECT @PIsExists 52 --========= 53 54 DROP TABLE #TmpControlRequst 55 56 --========= 57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
View Replies !
How To Know Latest Update Date Of Each Stored Procedure ?
on SQL Server 2000 They show only Create date but I need know update date because I install my system on customer's site and solve problem on customer site and I can't bring all stored procedure back to my office and restore all stored because of my database have two projects. Please Help me.....
View Replies !
|
TRACKER
