SQL Server prompt user for input and pass to varia

问题:

I am trying to write code for my client that they can use in the future without my help. They will install SQLServer Express on one of their desktops (like I have been using) so that the designated person can run the pre-written queries on locally stored data for specific information they need on a daily basis. I don't want to get into why we are using SQL Server Express and managing our data in this manner, but trust that we've looked into alternatives and this is the most feasible option.

I would like to make executing the import query as simple as possible for them, as they are not familiar with SQL or coding in general. At a high level, I have about 15 tab-delimited files automatically loaded into a folder on my local drive every day with the filename consistent except for the pre-fix, which is the date that the file was loaded. For example, 20180912-xxx, 20180912-yyy, etc. for all of the files loaded on Sept 12.

I run a bulk import that creates a Database for Sept 12 ("sep12"), and then loads the tables into the database; then I use the @date variable in the import statements to define the file location. For example:

CREATE DATABASE aug29
USE aug29
DECLARE @date VARCHAR(15)
SET @date = '20180829'

@import = 'BULK INSERT dbo.Table FROM ''\\Drive\Documents\' + @Date + '-xxx.txt''
WITH (FIRSTROW = 2, FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')'
EXEC(@import)

As you can see, there is manual edit needed for the 1st, 2nd, and 4th lines. I've simplified the code to only require 1 manual edit, by defining 1 variable as the date (ex. @dateinput = '9/12/2018'), and then the other variables define themselves accordingly (ex. @DBName = 'sep12', @FilePrefix = '20180912', etc.).

My question is this:

Can I prompt the user to enter this date before running the code? Something like Click Execute > message prompt pops up > User enters a date > click OK > code runs with the inputted value stored as the variable

回答1:

Create a Stored Procedure?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[ImportData]
    @date   varchar(15)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @query AS varchar(MAX)
    SET @query = 'CREATE DATABASE aug29
                  USE aug29
                  BULK INSERT dbo.Table FROM ''\\Drive\Documents\''' + @date + '''-xxx.txt''
                  WITH (FIRSTROW = 2, FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')'
   EXEC(@query)
END

Then on SSMS open your database > Programmability > Stored Procedures > Right Click on the newly created Stored Procedure (ImportData or whichever you name it) > Execute Stored Procedure.

Users can then input the value for the @date parameter.

回答2:

You could make your script completely independent of user input by using some of SQL Server's built in functions like I am doing below:

--Variable to use for dynamic sql
DECLARE @sqlStatement varchar(MAX) = '';
--Returns the  month as its full name like June, or July
DECLARE @fullMonthValue varchar(100) = DATENAME(month, DATEADD(month, MONTH(GETDATE()) -1, CAST('2008-01-01' AS datetime)));
--Get the database name how you gave in your example, for example today is August 30th, this will result in aug30
DECLARE @databaseName varchar(100) = LOWER(SUBSTRING(@fullMonthName, 1, 3)) + CAST(DAY(GETDATE()) AS varchar(3));
--Now get the current date as string for use in your bulk insert
DECLARE @today = CAST(GETDATE() AS Date);
--cast the current date to varchar (string) and remove the hyphens
DECLARE @stringDate = REPLACE(CAST(@today AS varchar(100)), '-', ''); --Need to remove the hyphens
--Set the sql statement for creating the database
SET @sqlStatment = 'Create DataBase ' + @databaseName;

--Execute the sqlStatement to create the database
EXEC(@sqlStatement);

--At this point @stringDate is already the format you want for your example variable of @date


  --Just put your USE statement into your dynamic sql string
    @import = 'USE ' + @databaseName + 'BULK INSERT dbo.Table FROM ''\\Drive\Documents\'' + @stringDate + '-xxx.txt''
    WITH (FIRSTROW = 2, FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')';

    EXEC(@import);