How to use a SQL Server Parameter Input from Command Line?

Question

I'm a SQL Newbie and I'm trying to figure out how to use a Parameter with SQL Thru Command-Line

For my boss/staff I have written Batch Files to run SQL Code for them to export data and the like. In Access all I have to do is [Paramerter] and it prompts for data to be entered.

The @State Variable I'd like to be able to be set dynamically. I'd like the batch file to ask for State and Query use that information. I have no idea how to do it.

Batch File

sqlcmd -E -S ServerName -i C:\Lead$\SQL\MakeSTPhoeLists.sql
pause

The SQL File

Use LeadsDb
Go

Declare @State VarChar(2)
Set @State = 'DE'
DELETE FROM tblzExportPhone

INSERT INTO tblzExportPhone ( Phone )
SELECT tblLeads.Phone
FROM tblLeads
WHERE tblLeads.ST = @State

Declare @FileName VarChar(100)
Set @FileName = 'H:\Leads\Storage\STLists\' +  @State +'StatePhoneList.csv'

DECLARE @bcp_cmd4 VARCHAR(400) = ' BCP.EXE LeadsDb..tblzExportPhone out ' 
SET @bcp_cmd4 = @bcp_cmd4 + 'H:\Leads\SQL\Formats\PhoneTmp.csv' + ' -T -f H:\Leads\SQL\Formats\tblzExportPhone.fmt' 
SET @bcp_cmd4 = @bcp_cmd4 + ' & Copy /b     H:\Leads\SQL\Formats\ExPhone.csv+H:\Leads\SQL\Formats\PhoneTmp.csv ' + @FileName + ' /y'
Set @bcp_cmd4 = @bcp_cmd4 + ' & Del H:\Leads\SQL\Formats\PhoneTmp.csv'

Thank You.

Answer 1

Essentially what I found out is you have to make a Stored Procedure and Call a query to run it. I'm going to leave an example. BTW SQL Query Creates CSV File from table Using bcp and then Zips it up using RAR Dos Command.

BTW Stored procedure is used due to every which other way I try to do it withoutI failed, but once I added it I was golden. Can use Dos prompt insead of bat file but I'm setting up bat files for 'Semi-Computer Smart people'

Hope this helps a newbie like me out^^

BAT File (cmd prompt)

Echo phone numbers in the State to send to Paramount.
Echo Then after the file is made converts it to Zip.
Echo '
Set /p State=Enter Initials of the State? :
sqlcmd -E -S Titania -i H:\Lead$\SQL\STPhones.sql -v StateName=%State%

SQL Command #1

Use dbNameHere
go
Declare @State NVarChar(2)
EXECUTE [dbo].[STPhoneListB] @State=$(StateName)

SQLCommand #2

Use dbNameHere
Go
CREATE PROCEDURE [dbo].[STPhoneListB]
    @State NVarChar(2) = 'DE',
    @Folder VarChar(100) = 'H:\Lead$'
AS
BEGIN
    Declare @FileName VarChar(150)
    Set @FileName = @Folder + '\STPhones_'+ @State +'.csv'

    Declare @DosCMD VarChar(150) = 'Del ' + @Folder +'\STPhones'+ @State
           +'.Zip /q'
    EXEC master..xp_cmdshell @DosCMD 
    DECLARE @bcp_cmd4 VARCHAR(400)
    DELETE FROM tblzExportPhone

    INSERT INTO tblzExportPhone ( Phone )
    SELECT tblLeads.Phone
    FROM tblLeads
    WHERE tblLeads.ST = @State
    Set @bcp_cmd4 = 'BCP.EXE LeadsDb..tblzExportPhone out ' 
    SET @bcp_cmd4 = @bcp_cmd4 + 'H:\SQL\PhoneTmp.csv' +
           ' -T -f H:\SQL\tblzExportPhone.fmt' 
    SET @bcp_cmd4 = @bcp_cmd4 
           + ' & Copy /b H:\SQL\ExPhone.csv+H:\SQL\PhoneTmp.csv '
           + @FileName + ' /y'
    Set @bcp_cmd4 = @bcp_cmd4 + ' & Del H:\SQL\PhoneTmp.csv'
    EXEC master..xp_cmdshell @bcp_cmd4 
    Set @bcp_cmd4 = 'cd '+ @Folder + 
           ' & "C:\Program Files\WinRAR\rar.exe" m STPhones_'+ @State +'.Zip ' + 
           'STPhones_'+ @State +'.csv'

    EXEC master..xp_cmdshell @bcp_cmd4 
    DELETE FROM tblzExportPhone
END

Answer 2

In your sql file use this notation $(statename)

Add this to your command file -v statename = %1

And execute it passing the parameter mycommanfile.cmd DE

Also read this for a full example.