Maduranga Siriwardena Maduranga Siriwardena - 1 year ago 96
Java Question

Declare and use t-sql variable from java using JDBC driver

I'm trying to run this sql script from a java client on a sql server.

DECLARE @CON_NAME VARCHAR(100);
DECLARE @COMMAND VARCHAR(200);

SELECT @CON_NAME=NAME FROM sys.key_constraints WHERE [type] = 'PK' AND [parent_object_id] = Object_id('dbo.MY_TABLE');
SELECT @COMMAND=CONCAT('ALTER TABLE MY_TABLE DROP CONSTRAINT ', @CON_NAME);
SELECT @COMMAND;
EXEC (@COMMAND);


What the java client does is run the script line by line.

I'm getting the exception below:

com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the scalar variable "@CON_NAME".


How can I solve this issue?

Answer Source

Instead of running the script line by line, send the entire script as one statement.

Running each line separately, means you're running multiple scripts, like if you had put each line in a separate .sql file.

DECLARE'd variables are local to the script, so running the lines individually makes no sense.