/*
AUTHOR : DANDY WEYN - WWW.DANDYMAN.NET - dandy@dandyman.net
SCRIPTNAME : DYNAMICSQL.SQL
LASTMODIFIED : 02-FEB-2004


DESCRIPTION :
THIS SAMPLE SHOWS HOW YOU CAN BUILD AN RUN A DYNAMIC SQL STATEMENT

EXAMPLE :
YOU WANT TO CREATE A STORED PROCEDURE THAT ADDS THE COLUMN LASTMODIFIED AND MODIFIEDBY COLUMN TO IT
*/


CREATE PROCEDURE STP_ADDLOGINFO_TO_TABLE (@tablename varchar(100))
AS
BEGIN
declare @statement nvarchar(4000)
set @statement = 'ALTER TABLE [' + @tablename + ']' + char(13) + 'ADD LASTMODIFIED SMALLDATETIME DEFAULT GETDATE(),
                  MODIFIEDBY VARCHAR(255) DEFAULT SUSER_SNAME()'
print @STATEMENT
--- THE STRING YOU HAVE BUILT CAN BE EXECUTED USING EXECUTE OR BY CALLING THE STORED PROCEDURE SP_EXECUTESQL
--- EXECUTE (@statement)
EXEC SP_EXECUTESQL @statement
END


-- EXAMPLE creates a table
if exists (select name from sysobjects where name = 'tbl_demo')
drop table tbl_demo
create table tbl_demo (demo int)
-- adds the logcolumns to the table
EXEC STP_ADDLOGINFO_TO_TABLE 'tbl_demo'

-- view the tables result
EXEC SP_HELP tbl_demo