Generating delete statement from tablename stored in another table

Question

Post reply

Generating delete statement from tablename stored in another table

gaurav-404321

SSCommitted

Points: 1593
More actions
April 16, 2010 at 11:59 pm

#386724

I have a function that returns me values passed in comma separated string in table format.
select sectionnumeric from dbo.CreateSectionTableFromString('placementcandidatelanguageproficiency,placementcandidatetrainingsinternshipsprojects')
This will return values as:
placementcandidatelanguageproficiency
placementcandidatetrainingsinternshipsprojects
These two values returned are tablenames
I want to fire the delete statement from these tables now.
Please help in writing the query

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Is your expected output

DELETE FROM placementcandidatelanguageproficiency

DELETE FROM placementcandidatetrainingsinternshipsprojects

If it is so, then you can create the script using Dynamic SQL

DECLARE@strSQL VARCHAR(MAX)

SELECT@strSQL = COALESCE( @strSQL + CHAR(10), '' )

+ ' DELETE FROM ' + sectionnumeric

FROMdbo.CreateSectionTableFromString('placementcandidatelanguageproficiency,placementcandidatetrainingsinternshipsprojects')

PRINT( @strSQL )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 2

This is not working. Stored prcoedure is returning the query as :

DELETE FROM sectionnumeric

FROM dbo.CreateSectionTableFromString(placementcandidatelanguageproficiency,placementcandidatetrainingsinternshipsprojects,placementcandidatequalificationdetails)

This query is giving lot of errors. I think some syntax mistake is there

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 3

gaurav-404321 (4/17/2010)
This is not working. Stored prcoedure is returning the query as :
DELETE FROM sectionnumeric
FROM dbo.CreateSectionTableFromString(placementcandidatelanguageproficiency,placementcandidatetrainingsinternshipsprojects,placementcandidatequalificationdetails)
This query is giving lot of errors. I think some syntax mistake is there

Something like this :

DECLARE @SQL VARCHAR(MAX)

SELECT@SQL = ''

SELECT @SQL = @SQL + ' DELETE FROM ' + SectionNumeric + CHAR(13)

FROM dbo.CreateSectionTableFromString('placementcandidatelanguageproficiency,placementcandidatetrainingsinternshipsprojects,placementcandidatequalificationdetails')

PRINT ( @SQL )

--OR

EXECUTE ( @SQL )

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

gaurav-404321 (4/17/2010)
This is not working. Stored prcoedure is returning the query as :
DELETE FROM sectionnumeric
FROM dbo.CreateSectionTableFromString(placementcandidatelanguageproficiency,placementcandidatetrainingsinternshipsprojects,placementcandidatequalificationdetails)
This query is giving lot of errors. I think some syntax mistake is there

Can you give us the expected output. That would be helpful for us to understand what you need.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Paul White SSC Guru Points: 150467 More actions · Answer 5

Better still, explain what you are seeking to achieve as a whole - there is almost certainly a better design.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi