Adding Delete To Stored Procedures
Aug 31, 2006
I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out
REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])
SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @@Error <> '0'
RETURN
GO
View 4 Replies
ADVERTISEMENT
Jun 14, 2006
Hi
I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.
For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.
In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.
Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.
If anyone can point me in the right direction on this...
I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?
Thanking you in advance
View 1 Replies
View Related
Jul 23, 2005
How can i add Execute permissions on the Stored Procedures under thecreated user permission iusing SQLDMO ?
View 1 Replies
View Related
Mar 21, 2007
Hi All,
In SQL server 2000 enterprise manager you were able to select multiple stored procedures and drop them all in one go. In 2005 all the stored procs are listed differently (in a tree view) and you cannot therefore select multiple SP for deleteing. Is there another way to accomplish this?
Danny
View 3 Replies
View Related
Feb 24, 2006
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.
CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO
<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>
Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
____________________________________________________________
CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO
<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>
Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
View 2 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Jul 20, 2005
How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Dec 3, 2003
Hi everyone out there in ASP.NET land.
Have a quick question...
How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?
Thanks ahead of time,
Denvas
View 2 Replies
View Related
Dec 29, 2005
Hello,
I'm in the progress of developing a shopping cart system that operates with an SQL database in Visual Web Developer 2005. I've managed to successfully add items to the cart and display them, but I'm having trouble providing the user with the option of removing items from the cart.
My understanding so far is that I've got to adjust the DELETE SQL statement of the data source. At first I was thinking along the lines of a simple statement:
DELETE * FROM ShoppingCart WHERE CartID=@CartID AND CategoryID=@CategoryID AND ProductID=@ProductID
However, I've realised that the parameters I need for this SQL query aren't automatically passed in when a user clicks the "Delete" text of the Delete field (at least I think this is the problem).
The error message which I'm getting when I try to remove an item from the cart is as follows:
Incorrect syntax near '*'.
If anyone could let me know where I'm going wrong and point me in the right direction I'd be really grateful.
Thank you,
Luke
View 2 Replies
View Related
Apr 28, 2008
Hi there, I have a simple data flow from OLE DB to OLE DB that writes data from a view into a table. Is it possible to delete the table or the rows within the table before wirting the new data???
Thanks in advance!
View 10 Replies
View Related
Nov 30, 2006
Hello, I am moving from Access to SQL and I am trying to create DELETE and a SELECT procedures. I was following the Asp.Net 2.0 Membership, Roles, etc procedures but I am not getting there. Could someone, please, post just the 3 procedures for this example? It would be a start for me to work on ... I have related two tables: <Content> and <ContentLocalized> <Content> |----- [ContentId] Type=UniqueIdentifier PK | [ContentName] Type=NVarChar(100) | | <ContentLocalized> | [ContentLocalizedId] Type=UniqueIdentifier PK | -------> [ContentId] Type=UniqueIdentifier FK | [ContentCulture] Type=NVarChar(5) | [ContentHtml] Type=NVarChar(MAX) Basically I want to create 3 stored procedures: 1. SELECT [ContentHtml] Given [ContentName] and [ContentCulture] 2. DELETE a content in <Content> and all its dependencies in <ContentLocalized> given [ContentName] 3. DELETE a content record in <ContentLocalized> given [ContentName] and [ContentCulture] I allready have an Access Query for (1) but I don't think I should try to use it in SQL: SELECT ContentLocalized.ContentHtml FROM Content INNER JOIN ContentLocalized ON Content.ContentId=ContentLocalized.ContentId WHERE (((ContentLocalized.ContentCulture)=[@ContentCulture]) AND ((Content.ContentName)=[@ContentName])); Thank You Very Much, Miguel
View 1 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Apr 6, 2005
calculate age:CREATE PROCEDURE selectage ASSELECT Age = datediff(yy, birthday, getdate()) + case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0 else -1endFROM nopaymy ? is how do fix this problem:I add inWHERE (((Age)>= @age1 And (Age)<= @age2)) and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?
View 6 Replies
View Related
Mar 27, 2006
Currently I add a new stored procedure in Enterprise Manager by right-clicking Stored Procedure and clicking New Stored Procedure then copy/paste text of procedure into window displayed. Whenever we set up new customer, this process is repeated several times to get all of our stored procedures loaded.
Is there a way to automate this process?
Thanks
View 2 Replies
View Related
Jan 31, 2008
Hello,
What is the easiest way to add a strored prodedure in SQL- server? Sounds like silly question but it doesn´t seem that obvious to me.
BTW, I´m using SQL-server 2005.
Thanks in advance!
View 6 Replies
View Related
Apr 12, 2007
Hi all,I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.
View 2 Replies
View Related
Jul 19, 2007
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
View 8 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Jan 7, 2004
Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.
Thank you,
View 5 Replies
View Related
Aug 2, 2001
In SQL7, if you add a column to a table, is it necessary to recompile all
stored procs that reference that table?
Under 6.5, this caused problems at our site unless the Procs were 'refreshed'.
Is this only necessary where select * is used?
Thanks
View 1 Replies
View Related
Dec 14, 2007
I am trying to add a data driven subscription to my report. When I get to the screen to enter the Delivery Query, I enter the stored procedure (GetStatusReportData) in the query box. When i try to validate, I get a "Query is not valid" error. If I replace the stored procedure name with a generic query (select * from jobs), it works fine.
Is there some sort of syntax I need to use to enter a stored procedure here? I have used "exec GetStatusReportData" and that did not work either.
Thanks,
Stuart Fox
View 1 Replies
View Related
