0

I have a batch file that prompts the user to type what column they want to delete from and what value they want to be deleted. After the user types both values my SQL Script begins to look for that specific column and that specific value:

declare @columnName nvarchar(255)
declare @intValue int
set @columnName = '$(colu)' --COLUMN NAME WHERE VALUE IS LOCATED
set @intValue = '$(sn)'             --VALUE TO BE DELETED
declare @DeleteValue varchar(10)
set @DeleteValue = convert(varchar(10), @intValue)

declare @sql nvarchar(max) = ''

select @sql =  @sql + 'delete ' + object_name(c.object_id) + ' where ' + @columnName + ' = ' + @DeleteValue + ';'
from sys.columns c 
where c.name = @columnName

select @sql

exec sp_executesql @sqlde here

DATABASE SAMPLE:

Student Table

Grade       Student_id
17             6
22             14
25             14
26             15
32             15

Course table

Grade     Course
17         math
22         math
25         literature
26         history
32         Science

When I delete a student id I also want it to delete the corresponding grade from every table.

For an example: If I want to delete student_id 1 it should delete student_id 14 + grades 22 & 25 plus math and literature courses that are associated with student_id 14.

However my code doesn't do this

royalblue
  • 439
  • 2
  • 8
  • 18
  • You are looking for cascading deletes. You can add these when you define the `foreign key` constraints. – Gordon Linoff Oct 13 '15 at 20:59
  • The way the database is designed I can't just write a simple query to do that. I must go the "long" way and write a script to delete in order to try to clean up the db. – royalblue Oct 13 '15 at 21:02
  • Then you'll have to query all the foreign keys ( http://stackoverflow.com/questions/1229968/is-it-possible-to-list-all-foreign-keys-in-a-database ) on that table & field. Then cascade the data yourself. – Reinard Oct 13 '15 at 21:04

0 Answers0