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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
Error When Attempting To Insert Record Using Stored Procedure
I am trying to save user data to a sql table but keep getting the following error; Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'. I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows; Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString End Function Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process. Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help. Thanks, Jaime
View Replies !
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 !
How To Check If An Insert Stored Proc Succeeded
Hi, How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[showtube_addNewSUser] -- Add the parameters for the stored procedure here @UserId uniqueidentifier, @FirstName nvarchar(32), @LastName nvarchar(32), @DescShort nvarchar(256), @DescLong ntext, @ClanID uniqueidentifier AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID) values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID); END I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.
View Replies !
How To Check Whether Stored Procedure Is Being Used
Hi, I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission. How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out? At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
View Replies !
How To Check Stored Procedure?
Hello, everyone: I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks. ZYT
View Replies !
Stored Procedure To Check Different Criterias
I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0 ,@OriginalMax DECIMAL=0 AS DECLARE @CRI8 VARCHAR(500)SELECT @CRI1='' SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax' SELECT @Criteria = ......+ @CRI8 When I execute this stored procedure, I get the following error message. SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin'. Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
View Replies !
How To Check For Nulls With-in A Stored Procedure
I have a Stored Procedure as followsUSE [MyDataBase] GO /****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SPLogins] ( @LoginName varchar(50), @LoginD int output, ) AS Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value What Is the correct wayt to go about it.
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 !
Select And Check A Boolean Value With Stored Procedure?
Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
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 Check When A Stored Procedure Was Last Called/executed
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View Replies !
Query - Check For A String In Stored Procedure
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
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 !
Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View Replies !
Transaction On Stored Procedure
hello, i'm executing a select statement inside the BEGIN TRANSACTION block on a Stored Procedure. my question is, does it lock the table? actually, i'm getting the maximum value of a field and i don't want other users to read until the transaction is done. do i still need to use tablockx to manually lock the table or does the transaction handles the locking? please help thanks!
View Replies !
Stored Procedure And Transaction
I have a function in which I use a stored procedure and a transaction.I use the following code, but I get the exception e (last try-catch): This SqlTransaction has completed; it is no longer usable.(btw, the SendEmail function currently returns False, so the transaction should ALWAYS roll back) Public Shared Function SaveGBEntry(ByVal myGBEntry As GBEntry, ByVal Language As String) As Boolean Dim bSuccess As Boolean = False Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewGBEntry", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True Dim UserCodeOwner As Integer = UserFunctions.GetUserCode(myGBEntry.UserNameOwner) Dim OwnerEmail As String = UserFunctions.GetUserEmail(myGBEntry.UserNameOwner) cmd.Parameters.Add(New SqlParameter("@UserCodeSender", myGBEntry.UserCodeSender)) cmd.Parameters.Add(New SqlParameter("@GBText", myGBEntry.Text)) cmd.Parameters.Add(New SqlParameter("@GBUpdateDate", myGBEntry.UpdateDate)) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCodeOwner)) cmd.ExecuteNonQuery() myTrans.Commit() If MailFunctions.SendEmail(myGBEntry.UserNameOwner, OwnerEmail, ConfigurationManager.AppSettings("emailINFOname"), ConfigurationManager.AppSettings("emailINFOAddress"), True, "NewGBEntry", Language) Then bSuccess = True fBeginTransCalled = False End If Catch ex As Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", ex.Message) Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", e.Message) End Try End If MyConnection.Close() End Try Return bSuccess End Function
View Replies !
Stored Procedure And Transaction
I have a stored procedure that registers a user. It also checks whether an email or username exists before registering them.Does this need to be in a transaction to prevent duplication of email/username? The data is highly sensitive and should not be duplicated at all.I tried writing methods in .net like CheckEmail() and CheckUsername() but that probably wont protect against duplicates if 2 people submit at the same time on a busy server. So the next best thing is a stored procedure but should it be in a transaction or not is what I'm wondering.Thanks again,you guys are great!
View Replies !
Transaction Through Stored Procedure
i have to update two tables from ASP pages with same data but i wantthat both of them should be updated at one time. If either of them isnot updated then my transaction should roll back.I want this thing tobe in a stored procedure. so that i have to write an execute statementonly on the ASP page and pass the parameters.Looking forward for ur replyDEEPAK
View Replies !
Transaction Within 2 Stored Procedure
Hi, Can I BEGIN a transaction in one stored procedure and decide to either ROLLBACK or COMMIT in another stored procedure? I've tried it and keep getting an error. These are my stored procedures. CREATE PROCEDURE A AS BEGIN TRANSACTION Foo Update address Set local = 'Y' Where id = 5 SAVE TRANSACTION FooSaved /*This transaction is not committed yet */ return(0) CREATE PROCEDURE B @city varchar(255) AS Exec A If @city = 'Boston' /*local*/ COMMIT TRANSACTION FooSaved Else /* Not local*/ Begin ROLLBACK TRANSACTION FooSaved COMMIT TRANSACTION FooSaved /* I tested taking out this statement and I still get same errors*/ End return(0) In ISQL, I called Exec B 'boston' and Exec B 'dallas'. But when I print out the value of local, it always gets updated to 'Y' even if it's 'Dallas' . I also get this error. Msg 266, Level 16, State 1 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count=2, Current count =3 Any help would be appreciated, Angel
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 !
Should Insert, Update And Delete Stored Procs Be Wrapped In Transaction?????
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRYINSERT INTO [syl_Categories] VALUES( @CategoryName, @LanguageID) SELECT SCOPE_IDENTITY() AS [CategoryID] RETURNEND TRY BEGIN CATCH --Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1 END CATCH END
View Replies !
Should Insert, Update And Delete Stored Procs Be Wrapped In Transaction?????
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY INSERT INTO [syl_Categories] VALUES( @CategoryName, @LanguageID) SELECT SCOPE_IDENTITY() AS [CategoryID] RETURN END TRY BEGIN CATCH --Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
HELP! Stored Procedure And Transaction Problems
I have a stored procedure I want to use in a transaction...I have no idea where the problem is, since I dont receive an error...but tblMessages and tblUsersAndMessages are not updated..(hence something is wrong ;))...it might be the sp or just my code...anyway..here's all the data:STORED PROCEDUREset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo ALTER PROCEDURE [dbo].[spNewMessage]@MessageID int,@UserIDSender uniqueidentifier,@MessageTitle nvarchar(50),@MessageContent text,@MessageType int,@UserID uniqueidentifier ASBegin Set NoCount on DECLARE @WhateverID INT INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType) SET @WhateverID=SCOPE_IDENTITY() INSERT INTO tblUsersAndMessages(MessageID,UserID)VALUES (@WhateverID,@UserID) End TABLE DEFINITIONtblUsersAndMessages allow nullsMessageID int falseUserID uniqueidentifier falseNew bit false *default set to ((1)) tblMessages allow nullsMessageID int false *PKUserIDSender uniqueidentifier falseMessageTitle nvarchar(50) trueMessageContent text trueSentDateTime datetime false * default set to (getdate())MessageType int false THE CODE Public Shared Function SendMessage(ByVal SenderName As String, ByVal To As String), ByVal MessageTitle As String, ByVal MessageContent As String, ByVal MessageType As String) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewMessage", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True Dim UserIDSender As Guid = UserFunctions.GetUserID(SenderName) Dim UserIDReceiver As Guid = UserFunctions.GetUserID(To) 'create message cmd.Parameters.Add(New SqlParameter("@UserIDSender", UserIDSender)) cmd.Parameters.Add(New SqlParameter("@MessageTitle", MessageTitle)) cmd.Parameters.Add(New SqlParameter("@MessageContent", MessageContent)) cmd.Parameters.Add(New SqlParameter("@MessageType", CInt(MessageType))) 'userid receiver cmd.Parameters.Add(New SqlParameter("@UserID", UserIDReceiver)) cmd.ExecuteNonQuery() myTrans.Commit() bSuccess = True fBeginTransCalled = False Catch ex As Exception Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
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 !
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 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 !
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 !
Rollback Transaction To Previous Stored Procedure?
Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful? Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records ======================================= create stored procedure combine_sp as begin call delete_sp -- have rollback transaction in the delete_sp -- what to do if following has error occured, but we already deleted the records above? call insert_sp -- have rollback transaction in the insert_sp end go ======================================= Thanks a lot.
View Replies !
Stored Procedure Or Alert For Transaction Log % Full?
I am new to SQL server 6.5 I am in the process odf setting up full database backups and incremental backups. For now, in the test environment,I will take a full backup and perhaps dump the transaction log twice a day. But what I would really like to do as well is either use a stored procedure or some how set uyp an alert to dump the database's transaction log when it gets, let's say 65% or 75% full so that it doesn't give me a log full message. 1) Does any have a stored procedure to do this? 2) If not, how exactly would I set up an alert to kick off a transaction log dump when the log is 65% full(or whatever percentage I decide to use)? Any information would be greatly appreciated. Thanks. David Spaisman
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 !
|
TRACKER
