Product:

Microsoft SQL server 2019

Issue:

How create a store procedure, than when run will add username and date to a row?

Suggested solution:

Paste below code in SQL management studio and edit it to your needs:

CREATE PROCEDURE [dbo].[A_AddDataToTable] 
AS
BEGIN
---- create the variables
DECLARE @TableName as nvarchar(50)
DECLARE @SqlCode as nvarchar(200)
DECLARE @TimeValue as nvarchar(50)
DECLARE @Userman as nvarchar(50)

---- enter the name of your table below instead of Donald
SET @TableName = 'Table_'+'Donald'

BEGIN
---- check if the table exist, then not create it
IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = @TableName) 

---- fill variable with the SQL code to create the table
set @SqlCode=''
set @SqlCode= @SqlCode + 'CREATE TABLE ' + @TableName + ' '
set @SqlCode= @SqlCode + ' (Rowid int IDENTITY(1000, 1) PRIMARY KEY, '
set @SqlCode= @SqlCode + ' ProUser nvarchar(50) , '
set @SqlCode= @SqlCode + ' ProDate nvarchar(50) ) ;'


---- execute the code in the string
EXEC sp_executesql @SqlCode

END
---- every time the processes is run, do below
---- add a row with date and username

---- get the values for date
SELECT @TimeValue = convert(varchar, getdate(), 0)
---- get the name of the user who run the process
SELECT @Userman = CURRENT_USER

---- build the string of values
set @SqlCode=''
set @SqlCode= @SqlCode + 'INSERT INTO '+ QUOTENAME(@TableName) +' (ProUser,ProDate) ' 
set @SqlCode= @SqlCode + 'VALUES ( ''' + (@UserMan) + ''' , ''' + (@TimeValue) + ''' ) '

-- execute the code in the string
EXEC sp_executesql @SqlCode

END


The result in the table will be like this, after a few run of the SP.

 

More Information:

https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/

https://codingsight.com/10-sp_executesql-gotchas-to-avoid-for-better-dynamic-sql/