Error while creating stored procedure from SQLCMD - Must declare the scalar variable

• anonymous2009

  Ten Centuries

  Points: 1164

  More actions

  February 2, 2013 at 10:16 pm

  #288787

  Hello,

  I'm Getting the following error when I run the below SQLCMD command and not sure how to resolve this issue:

  C:\Test>sqlcmd -S localhost\TESTINSTANCE -i test.sql -v filepath=".\"

  Changed database context to 'TEST_DB'.

  Msg 137, Level 15, State 2, Server TOM\TESTINSTANCE, Line 18

  Must declare the scalar variable "@indate".

  Table EMP:

  CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(50), EMP_JOIN_DATE DATETIME);

  Test.sql:

  USE TEST_DB

  GO

  :r $(filePath)Upgrade_Script.sql

  :r $(filePath)Create_StoredProcedures.sql

  GO

  Upgrade_Script.sql:

  USE master

  --GO

  --50001

  IF EXISTS(SELECT * FROM sys.messages WHERE message_id = 50001)

  BEGIN

  exec sp_dropmessage 50001

  END

  --go

  PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------'

  print 'End of Upgrade_Script '

  PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------'

  PRINT ' '

  --GO

  Create_StoredProcedures.sql:

  CREATE PROCEDURE [TEST_DB].[dbo].[GetSP] @indate DateTime AS

  BEGIN

  SET NOCOUNT ON

  SELECT * FROM EMP WHERE EMP_JOIN_DATE > @indate;

  END

  --GO

  Thanks!

• paul.knibbs

  SSCoach

  Points: 15320

  More actions

  February 4, 2013 at 4:36 am

  #1583887

  I'm pretty sure you can't specify the entire three-part database name in a CREATE PROCEDURE call like that--if you change that to USE Test_DB and only specify the procedure name in the CREATE call, does it work?