asked on

Pass Environment Variable into SQL Script

I am having some issues trying to pass an environment variable into a SQL Script, however, I the SET @ContactID = %ContactID% statement returns %C instead of 10.

I can get it to work, but I have to change the batch file to this:

SET ContactID=10
SQLCMD -S . -E -Q "DECLARE @contactid varchar(2) SET @contactid = '%Contact%' PRINT @contactID"

I also tried this, but it doesn't work:

SQLCMD -S . -E -Q "DECLARE @contactid varchar(2) SET @contactid = '%Contact%' :r test.sql"

However, I would like to keep it in the external SQL Script. Is there anyway to do this, maybe using POWERSHELL?

-- This is the batch File Code
 
SET ContactID=10
 
SQLCMD -S . -E -itest.sql
 
pause
 
-- This is the SQL File Code (test.sql)
 
DECLARE @ContactID VARCHAR(2)
SET @ContactID = '%ContactID%'
PRINT @ContactID

Open in new window

ASKER CERTIFIED SOLUTION

Sander Stad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sander Stad

Little typo at the Powershell script.

###### Powershell Script #################################################
$contactId = [Environment]::GetEnvironmentVariable("Contact","User")
 
SQLCMD -S . -E -q "EXEC YourDatabase.NameOfStoredProcedure @ContactId = N$contactId"
##########################################################################

Open in new window

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

If you want to use batch variables (environment variables):

set ContactID=10
sqlcmd -itest.sql

test.sql would be the same.