|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Proc - DELETE FROM @TableNameI'm attempting to delete contents of @TableName where @TableName is
an input arg to my stored procedure as follows. How do I execute this query? Or is there a simpler way to do this? This must be really trivial I suspect. CREATE PROCEDURE sp_DeleteRecordset @TableName varchar(50) AS BEGIN SET NOCOUNT ON DECLARE @QRY varchar(100) Set @QRY = "DELETE FROM " + @TableName ??????? END GO thanks, jack CREATE PROCEDURE sp_DeleteRecordset
@TableName varchar(50) AS BEGIN SET NOCOUNT ON DECLARE @QRY varchar(100) Set @QRY = "DELETE FROM " + @TableName EXEC (@QRY) END GO Show quote "hushtech" <husht***@discussions.microsoft.com> wrote in message news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... > I'm attempting to delete contents of @TableName where @TableName is > an input arg to my stored procedure as follows. How do I execute this > query? > Or is there a simpler way to do this? This must be really trivial I > suspect. > > CREATE PROCEDURE sp_DeleteRecordset > @TableName varchar(50) > AS > BEGIN > SET NOCOUNT ON > DECLARE @QRY varchar(100) > Set @QRY = "DELETE FROM " + @TableName > ??????? > END > GO > > thanks, jack Thanks Yosh!
I just couldn't get the form of the EXEC right. jack- Show quote "Yosh" wrote: > CREATE PROCEDURE sp_DeleteRecordset > @TableName varchar(50) > AS > BEGIN > SET NOCOUNT ON > DECLARE @QRY varchar(100) > Set @QRY = "DELETE FROM " + @TableName > > EXEC (@QRY) > > END > GO > > "hushtech" <husht***@discussions.microsoft.com> wrote in message > news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... > > I'm attempting to delete contents of @TableName where @TableName is > > an input arg to my stored procedure as follows. How do I execute this > > query? > > Or is there a simpler way to do this? This must be really trivial I > > suspect. > > > > CREATE PROCEDURE sp_DeleteRecordset > > @TableName varchar(50) > > AS > > BEGIN > > SET NOCOUNT ON > > DECLARE @QRY varchar(100) > > Set @QRY = "DELETE FROM " + @TableName > > ??????? > > END > > GO > > > > thanks, jack > > > I could tell you knew what you were doing. It's those damn parens that get
me every now and then. =0} Show quote "hushtech" <husht***@discussions.microsoft.com> wrote in message news:0626D726-1CC5-46F3-A111-C0CD91B70FDE@microsoft.com... > Thanks Yosh! > I just couldn't get the form of the EXEC right. > > jack- > > "Yosh" wrote: > >> CREATE PROCEDURE sp_DeleteRecordset >> @TableName varchar(50) >> AS >> BEGIN >> SET NOCOUNT ON >> DECLARE @QRY varchar(100) >> Set @QRY = "DELETE FROM " + @TableName >> >> EXEC (@QRY) >> >> END >> GO >> >> "hushtech" <husht***@discussions.microsoft.com> wrote in message >> news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... >> > I'm attempting to delete contents of @TableName where @TableName is >> > an input arg to my stored procedure as follows. How do I execute this >> > query? >> > Or is there a simpler way to do this? This must be really trivial I >> > suspect. >> > >> > CREATE PROCEDURE sp_DeleteRecordset >> > @TableName varchar(50) >> > AS >> > BEGIN >> > SET NOCOUNT ON >> > DECLARE @QRY varchar(100) >> > Set @QRY = "DELETE FROM " + @TableName >> > ??????? >> > END >> > GO >> > >> > thanks, jack >> >> >> Try:
CREATE PROC usp_TruncateTable @TableName varchar(50) AS DECLARE @SQL varchar(150) SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']' EXEC (@SQL) HTH Jerry Show quote "hushtech" <husht***@discussions.microsoft.com> wrote in message news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... > I'm attempting to delete contents of @TableName where @TableName is > an input arg to my stored procedure as follows. How do I execute this > query? > Or is there a simpler way to do this? This must be really trivial I > suspect. > > CREATE PROCEDURE sp_DeleteRecordset > @TableName varchar(50) > AS > BEGIN > SET NOCOUNT ON > DECLARE @QRY varchar(100) > Set @QRY = "DELETE FROM " + @TableName > ??????? > END > GO > > thanks, jack Just know that if you issue a TRUNCATE statement on a table, it removes all
rows from a table without logging the individual row deletes. Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:eIuaWHMwFHA.2540@TK2MSFTNGP09.phx.gbl... > Try: > > CREATE PROC usp_TruncateTable > @TableName varchar(50) > AS > DECLARE @SQL varchar(150) > SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']' > EXEC (@SQL) > > HTH > > Jerry > "hushtech" <husht***@discussions.microsoft.com> wrote in message > news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... >> I'm attempting to delete contents of @TableName where @TableName is >> an input arg to my stored procedure as follows. How do I execute this >> query? >> Or is there a simpler way to do this? This must be really trivial I >> suspect. >> >> CREATE PROCEDURE sp_DeleteRecordset >> @TableName varchar(50) >> AS >> BEGIN >> SET NOCOUNT ON >> DECLARE @QRY varchar(100) >> Set @QRY = "DELETE FROM " + @TableName >> ??????? >> END >> GO >> >> thanks, jack > > Yosh,
Yes...I wrote it that way to increase the performance of his delete while minimizing the amount of logging involved. Jerry Show quote "Yosh" <yoshi@nospam.com> wrote in message news:%23wv8fKMwFHA.3400@TK2MSFTNGP14.phx.gbl... > Just know that if you issue a TRUNCATE statement on a table, it removes > all rows from a table without logging the individual row deletes. > > > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message > news:eIuaWHMwFHA.2540@TK2MSFTNGP09.phx.gbl... >> Try: >> >> CREATE PROC usp_TruncateTable >> @TableName varchar(50) >> AS >> DECLARE @SQL varchar(150) >> SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']' >> EXEC (@SQL) >> >> HTH >> >> Jerry >> "hushtech" <husht***@discussions.microsoft.com> wrote in message >> news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... >>> I'm attempting to delete contents of @TableName where @TableName is >>> an input arg to my stored procedure as follows. How do I execute this >>> query? >>> Or is there a simpler way to do this? This must be really trivial I >>> suspect. >>> >>> CREATE PROCEDURE sp_DeleteRecordset >>> @TableName varchar(50) >>> AS >>> BEGIN >>> SET NOCOUNT ON >>> DECLARE @QRY varchar(100) >>> Set @QRY = "DELETE FROM " + @TableName >>> ??????? >>> END >>> GO >>> >>> thanks, jack >> >> > > Thanks Jerry!
I know you knew what it did. I just wanted to make sure he did. Very good point!!! =0} Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:uxH3gOMwFHA.3300@TK2MSFTNGP09.phx.gbl... > Yosh, > > Yes...I wrote it that way to increase the performance of his delete while > minimizing the amount of logging involved. > > Jerry > "Yosh" <yoshi@nospam.com> wrote in message > news:%23wv8fKMwFHA.3400@TK2MSFTNGP14.phx.gbl... >> Just know that if you issue a TRUNCATE statement on a table, it removes >> all rows from a table without logging the individual row deletes. >> >> >> "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message >> news:eIuaWHMwFHA.2540@TK2MSFTNGP09.phx.gbl... >>> Try: >>> >>> CREATE PROC usp_TruncateTable >>> @TableName varchar(50) >>> AS >>> DECLARE @SQL varchar(150) >>> SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']' >>> EXEC (@SQL) >>> >>> HTH >>> >>> Jerry >>> "hushtech" <husht***@discussions.microsoft.com> wrote in message >>> news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... >>>> I'm attempting to delete contents of @TableName where @TableName is >>>> an input arg to my stored procedure as follows. How do I execute this >>>> query? >>>> Or is there a simpler way to do this? This must be really trivial I >>>> suspect. >>>> >>>> CREATE PROCEDURE sp_DeleteRecordset >>>> @TableName varchar(50) >>>> AS >>>> BEGIN >>>> SET NOCOUNT ON >>>> DECLARE @QRY varchar(100) >>>> Set @QRY = "DELETE FROM " + @TableName >>>> ??????? >>>> END >>>> GO >>>> >>>> thanks, jack >>> >>> >> >> > > Thanks Jerry,
I see the proper EXEC form, and your naming is much more informative. jack- Show quote "Jerry Spivey" wrote: > Try: > > CREATE PROC usp_TruncateTable > @TableName varchar(50) > AS > DECLARE @SQL varchar(150) > SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']' > EXEC (@SQL) > > HTH > > Jerry > "hushtech" <husht***@discussions.microsoft.com> wrote in message > news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... > > I'm attempting to delete contents of @TableName where @TableName is > > an input arg to my stored procedure as follows. How do I execute this > > query? > > Or is there a simpler way to do this? This must be really trivial I > > suspect. > > > > CREATE PROCEDURE sp_DeleteRecordset > > @TableName varchar(50) > > AS > > BEGIN > > SET NOCOUNT ON > > DECLARE @QRY varchar(100) > > Set @QRY = "DELETE FROM " + @TableName > > ??????? > > END > > GO > > > > thanks, jack > > > I like his use of the "brackets" around the table name.
Show quote "hushtech" <husht***@discussions.microsoft.com> wrote in message news:6EEE4CE7-072B-4604-BC27-CD4953067CA6@microsoft.com... > Thanks Jerry, > I see the proper EXEC form, and your naming is > much more informative. > > jack- > > "Jerry Spivey" wrote: > >> Try: >> >> CREATE PROC usp_TruncateTable >> @TableName varchar(50) >> AS >> DECLARE @SQL varchar(150) >> SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']' >> EXEC (@SQL) >> >> HTH >> >> Jerry >> "hushtech" <husht***@discussions.microsoft.com> wrote in message >> news:638E8161-3D2A-4878-A06D-E1A126EC37D1@microsoft.com... >> > I'm attempting to delete contents of @TableName where @TableName is >> > an input arg to my stored procedure as follows. How do I execute this >> > query? >> > Or is there a simpler way to do this? This must be really trivial I >> > suspect. >> > >> > CREATE PROCEDURE sp_DeleteRecordset >> > @TableName varchar(50) >> > AS >> > BEGIN >> > SET NOCOUNT ON >> > DECLARE @QRY varchar(100) >> > Set @QRY = "DELETE FROM " + @TableName >> > ??????? >> > END >> > GO >> > >> > thanks, jack >> >> >>  |
|||||||||||||||||||||||
Other interesting topics