Hi,
I need to create a stored procedure which would
delete just the photo of a student once he graduates.
I found out that a delete SQL statement will delete a whole row.
Is there a way to just delete the Photo and leave the rest of the data
untouched.
Thanks.
Regds,
Ran
I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table
Please help!
************************************************** ***
My contrat DDL is follows
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 follows
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char],
@contactId[int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @contactId = contactId
and @studentId = studentId)
begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
go
I want to delete some rows in a table using a Stored procedure. I am using MS sql 2000 using Enterprise manager. I want to delete all rows that have col. status listed as shipped.
/Create Procedure [DeleteOld] AS
/
/Delete Equipment
/FROM Yahoo
/WHERE Status <> 'shipped'
I have two rows of data, one row has a col. names status, and in that row of data says "shipped". The other row says "hold" in the place of "shipped".
The procedure is written, and is setting under that database ready to be run. I do not get any error's when I excute the procedure from SQL query, but it just does nothing when run!
I need help to make this procedure work properly
Thank You,
Ernie
P.S. I am new to this forum!
i have accidentally deleted a stored procedure is there anyway of getting this back, just found out there is no back up
View 7 Replies View RelatedHi, I'm having a hell of a time trying to get a delete query to work properly. I have an existing query that finds records in the database that have expired and now I want to delete those records. I tried wrapping a delete around it, but it deletes everything.
How can I delete the results of this procedure? Thanks!
SELECT rssName, [Days Old] FROM
(SELECT rssName, DATEDIFF(D, timestamp, getdate()) AS [Days Old]
FROM tblRSSFeeds) AS feeds
WHERE [Days Old] > @expireTime
Table Strucure - :
TestEven(TestNo,EmpNo,TestDate)
I jst want to write a stored procedure to delete all data which is 2 years old. thank you in adavance.
Hussain
I have the following sp:@TABLE_NAME varchar(255),
@CONTROL_ID int
AS
DECLARE @sql varchar(4000)
SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = 5'SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = ' + @CONTROL_ID + ''
EXEC (@sql)
When I use the the first SELECT line, it works great.When I use the second dynamic select, SQL raises an error:
Syntax error converting the varchar value 'DELETE FROM [TABLE_SETTINGS] WHERE CONTROL_ID = ' to a column of data type int.What is wrong?
I have 3 tables...TableA, TableB, TableC TableA - Personal InformationPersonalInfoId (Primary) , First Name,Last NameTableB - Personal Information To Department IDReferenceID, FKPersonalInfoId, FKDepartmentIdTableC - DepartmentDepartmentId, DepartmentNameI am coding Asp.Net VB using VWD express with Sql Server Express. I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger. I never have used a Trigger and it is new to me. Can someone point me a way on how to use one in this case of my deleting scenario. Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid. In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department. Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure. Hope that made sense.Thanks.
View 2 Replies View RelatedHi
I'm new to SQL. The primary keys of the tables I use or not autonumbered.
I need to insert data and delete data. When I need to insert data al the primary keys should change. example
1 tim
2 frank
3 jan
4 klaus
I need to insert a new name at place number 3. So jan get's number 4, Klaus number 5.
The same thing goes if I want to delete a row
How can this be done with a stored procedure??? anybody an idea
Or are there script available for things like this?
Tim
How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script..
Code:
CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw
/***************************************************************
** CREATED: 12/04/2006
****************************************************************
** DESCRIPTION: Clean the tblSoldRaw by deleting any data that
** is over 90 days old. We need keep the database
** as small as possible so the performance won't
** suffer on the server's and the client's machine.
**
****************************************************************
** NOTES: Hooked up to BookItOut Data Importer Windows Service
****************************************************************
** MODIFICATIONS:
**
** DATE WHO MODIFICATION DESCRIPTION
** -------------------------------------------------------------
**
***************************************************************/
/***************************************************************
** Uses: tblSoldRaw
***************************************************************/
AS
DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90)
RETURN ROWCOUNT
ERROR_HANDLER:
RETURN -1
SET NOCOUNT OFF
Thanks...
How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.
CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
[code]...
Hi, I would like to delete some records in a table older than a specified date using a stored procedure. Can anyone help?
Thank you,
Cynthia
Hopefully someone can help me with this, or tell me if it's even possible for SQL Server 2005. I'm new to this and learning as I go along.
Here's what I want to do - I have a couple of queries running off a linked server. I want to take the results of those queries and insert them into a table in SQL Server, but first I want to delete the current contents of that table. I want to build a stored procedure that I can run monthly to temporarily store this data for review; once I'm ready to review the next month I want the procedure to delete the data in the table and then insert the next month's data. Hopefully that makes sense.
Is it possible to build? Anyone have any examples of such a thing, or can you point me someplace that might be able to help?
Thanks.
hi every body
can any onehelp me in making a stored procedure to delete a duplicate rows in tables
thanks in advance
hi guys,I am using SQL server 2005, i created a table of 4 columns. I want to create a stored procedure which will insert delete and modify the table using flag. all the three insert, delete and update must work in a single stored procedure. pls help me out. Thank You.
View 1 Replies View RelatedI would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
[Code] ....
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
Thanks In Advance
MPM
Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
end
Is this a good approach design, or its bad???
Thanks all
spaces problem on multi update delete stored procedure
on the the insert i get it like this
58830803, 55329429, 308962604, 55696314, 309128726
as you see only the first empid=58830803 is without spaces
how to fix this
my code
Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
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?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
Hello,I created a delete procedure which is working but I still have aproblem.When I delete a localized content from dbo.by27_ContentLocalized givena ContentName and ContentCulture I want to check if this is the onlyrecord in ContentLocalized for that ContentName.If it is then I also want to delete the record in dbo.by27_Contentwhich has that ContentName.How can I do this?Thanks,MiguelHere is my DELETE procedure:-- Define the procedure parameters@ContentCulture NVARCHAR(5),@ContentName NVARCHAR(100)AS-- Allows @@ROWCOUNT and the return of number of records whenExecuteNonQuery is usedSET NOCOUNT OFF;-- Declare and define ContentIdDECLARE @ContentId UNIQUEIDENTIFIER;SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName =@ContentName-- Check if ContentId is Not NullIF @ContentId IS NOT NULL BEGIN -- Check if ContentId is Null IF @ContentCulture IS NULL BEGIN -- Delete all localized contents from dbo.by27_ContentLocalized DELETE FROM dbo.by27_ContentLocalized WHERE ContentId = @ContentId -- Delete content from dbo.by27_Content DELETE FROM dbo.by27_Content WHERE ContentName = @ContentName; END ELSE -- Delete localized content from dbo.by27_ContentLocalized DELETE FROM dbo.by27_ContentLocalized WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture) END
View 4 Replies View RelatedHi, I am kinda stuck on a delete stored procedure in my project and I was wondering if you could give me a hand.
My table is like this: Image <----FKconstraint ----- ImageNote --------FKconstraint------>Note.
What I want to do is delete all the notes linkeds to and Image through ImageNote when I am calling the storedprocedure DeleteImage:
So far I did something like this:
DELETE FROM [ImageNote] WHERE [Image Id]=@Id
DELETE FROM [Note] WHERE Id IN (SELECT [Note Id] FROM [ImageNote] WHERE [Image Id]=@Id)
DELETE FROM [Image]
WHERE (Id = @Id)
RETURN
Obvisouly the second DELETE statement won't work as ""SELECT [Note Id] FROM [ImageNote] WHERE [Image Id]=@Id"" won't return a thing at that time. However I have to delete ImageNote first due to the constraint.
A friend told me I should try a trigger, but perhaps there would be something easier to do with some kind of array?