Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute a dynamic select statment Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:04 PM
Points: 22, Visits: 176
Can anybody help me actually execute the resultant DELETE statements in this query

drop table #incoming
CREATE TABLE #incoming
([id] [bigint] IDENTITY(1,1) NOT NULL,
[tablename] nvarchar(50),
[action] [int] NULL,
[rowid] [bigint])

INSERT #incoming (tablename,action,rowid) VALUES('#test',0,1111)
INSERT #incoming (tablename,action,rowid) VALUES('#test',0,1211)
INSERT #incoming (tablename,action,rowid) VALUES('#test',0,1311)
INSERT #incoming (tablename,action,rowid) VALUES('#test',1,1111)

drop table #test
create table #test
([tid] [bigint],
[data1] nvarchar(50),
[data2] nvarchar(50))
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1211,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1311,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')

SELECT 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) from #incoming where action = 0

SELECT * from #test

Post #1393089
Posted Wednesday, December 5, 2012 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
Hello Alan,
Try this...

DECLARE @QRY varchar (255)

SELECT @QRY = 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) from #incoming where action = 0

--PRINT @QRY
EXEC (@QRY)
Post #1393117
Posted Wednesday, December 5, 2012 10:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:04 PM
Points: 22, Visits: 176
arunyadav007

That only seems to execute the last statement.

I guess i need to somehow loop the results.

Alan
Post #1393120
Posted Wednesday, December 5, 2012 10:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
Woh ! I thought you only need to execute the last delete statement.. But, I guess you need to execute a delete statement on all the tablenames from your temporary table.. Let me try...
Post #1393125
Posted Wednesday, December 5, 2012 10:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 477, Visits: 3,689
Change to

declare @q varchar(max)
SELECT @q = ISNULL(@q, '') + 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) + '; ' from #incoming where action = 0
exec(@q)


but beware the dangers of this type of dynamic sql.

Edit: Read about the dangers here http://www.sommarskog.se/dynamic_sql.html


______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1393127
Posted Wednesday, December 5, 2012 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:04 PM
Points: 22, Visits: 176
Yes i need to run the results of the SELECT 'DELETE..... statement

DELETE FROM #test where tid =1111
DELETE FROM #test where tid =1211
DELETE FROM #test where tid =1311

Got me stumped
Post #1393128
Posted Wednesday, December 5, 2012 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:04 PM
Points: 22, Visits: 176
toddasd

Bingo, thank you so much.

This is part of a database sync system. The commands are built inside a stored procedure they are not stored in tables, just the data. Like table name, action, change data.

Thanks again



Post #1393132
Posted Thursday, December 6, 2012 5:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
toddasd (12/5/2012)
Change to

declare @q varchar(max)
SELECT @q = ISNULL(@q, '') + 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) + '; ' from #incoming where action = 0
exec(@q)


but beware the dangers of this type of dynamic sql.

Edit: Read about the dangers here http://www.sommarskog.se/dynamic_sql.html

This appears to be deleting everything from #incoming

Whereas it should not have deleted the below row as action != 0

4 #test 1 1111
Post #1393458
Posted Thursday, December 6, 2012 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:32 AM
Points: 5,490, Visits: 10,354
Put a PRINT @q in your script and you'll see why that's happening.

John
Post #1393463
Posted Thursday, December 6, 2012 5:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:04 PM
Points: 22, Visits: 176
Hi

Bum data

INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1211,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1311,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')

Should read

INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1211,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1311,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1411,'foo','foo')
Post #1393464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse