Maduranga Siriwardena Maduranga Siriwardena - 3 months ago 19
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

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.

Comments