SQL Server prompt user for input and pass to variable

Question

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

SQL server integrates quite nicely into .net applications. You could even create an application to run as a scheduled task that executes your parameterized query at the appropriate time each day with the appropriate date. If you are at all familiar with C# or VB.NET I would suggest an approach like this. — user8061994
– user8061994, Commented Aug 30, 2018 at 20:19
Another possibility would be to use sqlcmd utility. e.g. : blogs.msdn.microsoft.com/ssehgal/2009/04/03/… You could write a batch file which prompts user for the required info, then pass as variables in your call to sqlcmd . — Moe Sisko
– Moe Sisko, Commented Aug 31, 2018 at 2:52

CurseStacker · Accepted Answer · 2018-08-31 00:49:05Z

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.

answered Aug 31, 2018 at 0:49

CurseStacker

1,0898 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt G Over a year ago

Based on my research (and you've now confirmed), the stored procedure is the most logical solution; however, is there a way to enforce this so that every time the script is run, this execute procedure screen pops up? At least then, the user will be reminded to confirm/change the date.

CurseStacker Over a year ago

Every time a user runs a stored procedure, the parameters window (shown above) will prompt with blank values, and running them without providing a value will cause an error. Procedure or function 'MySP' expects parameter '@MyParameter', which was not supplied. so it shouldn't be an issue.

Ryan Wilson · Accepted Answer · 2018-08-30 20:53:47Z

0

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);

edited Aug 30, 2018 at 20:53

answered Aug 30, 2018 at 20:28

Ryan Wilson

10.9k3 gold badges23 silver badges46 bronze badges

2 Comments

Matt G Over a year ago

This is a great call actually. For now, it may cause issues because of the potential timing difference in between the automatic file save (Day X), and the day the script is manually run (Day Y). Once/if I can automate this script to run on a regular cadence in harmony with the file-save automation, this should work perfectly; so thank you for the input- it will definitely help out in the future state if I am able to get there before the completion of my project.

Ryan Wilson Over a year ago

@MattG Glad I could help, if you are happy with my answer, please mark it as the accepted answer. Thank you.

Collectives™ on Stack Overflow

SQL Server prompt user for input and pass to variable

My question is this:

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My question is this:

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related