Solved

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

Posted on 2013-02-02
7
858 Views
Last Modified: 2014-02-11
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".

Open in new window


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

Open in new window


Test.sql:
    USE TEST_DB
    GO
    :r $(filePath)Upgrade_Script.sql
    :r $(filePath)Create_StoredProcedures.sql
    GO

Open in new window


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

Open in new window


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

Open in new window


Thanks!
0
Comment
Question by:sath350163
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Accepted Solution

by:
esolve earned 167 total points
ID: 38848234
It seems you're not allowed to use the database name in the stored procedure creation. Try changing your proc to this:

use [TEST_DB]
GO

CREATE PROCEDURE [dbo].[GetSP] 
(
	@indate DateTime
) 
AS
SET NOCOUNT ON
BEGIN
 	SELECT * FROM EMP WHERE EMP_JOIN_DATE > @indate;
END

Open in new window


Also see this article which applies to your scenario:

http://ask.sqlservercentral.com/questions/5108/database-use-issues-with-dynamic-sql.html

DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

SET @dbName = 'master';
SET @sql = 'SELECT DB_NAME()';

SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@BigSQL)

Open in new window

0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38848462
I get a different error message if I try with MSSQL 2005 or 2008: Msg 166, which points out you can't use the DB name in create/alter procedure (and the next error is the one you showed). You will need to change the DB context prior to issuing a command creating or changing procedures or functions, as shown above.
The puzzling part is that you do not get the correct error message first ...
0
 

Author Comment

by:sath350163
ID: 38849359
I get the error message "Create/Alter Procedure does not allow specifying the database name  as a prefix to the object name" only when I hover above the Db name in Stored procedure from SSMS.

Trial 2:
Even after removing the database name from the CREATE PROCEDURE, I still the exact same error (below is the code):
Create_StoredProcedures.sql:
    CREATE PROCEDURE [dbo].[GetSP] @indate DateTime AS
    BEGIN
    	SET NOCOUNT ON
    	SELECT * FROM EMP WHERE EMP_JOIN_DATE > @indate;
    END
    --GO

Open in new window


    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".

Open in new window


Trial 3:
So I tried to include the "USE TEST_DB GO" at the top of the CREATE PROCEDURE script. Though the execution from command line does not throw any error, the stored procedure gets created in MASTER db instead of TEST_DB.
    USE TEST_DB
    GO
    CREATE PROCEDURE [dbo].[GetSP] @indate DateTime AS
    BEGIN
    	SET NOCOUNT ON
    	SELECT * FROM EMP WHERE EMP_JOIN_DATE > @indate;
    END
    --GO

Open in new window


Trial 4:
So I removed the "USE TEST_DB GO" from the top of "Create_StoredProcedures.sql" script, and included "USE TEST_DB" (without GO) in "Test.sql" right before invoking the "Create_StoredProcedures.sql" script (below is the code). This approaches throws "CREATE/ALTER PROCEDURE must be the first statement in a query batch."

Note:I get this same error, even when I include "USE TEST_DB" at the top of the "Create_StoredProcedures.sql" script with no "GO" in addition to "USE TEST_DB" without "GO" in "Test.sql" right before invoking the "Create_StoredProcedures.sql" script.

Create_StoredProcedures.sql.
CREATE PROCEDURE [dbo].[GetSP] @indate DateTime AS
BEGIN
	SET NOCOUNT ON
	SELECT * FROM [dbo].EMP WHERE EMP_JOIN_DATE > @indate;
END
--GO

Open in new window


Test.sql:
USE TEST_DB
GO
:r $(filePath)Upgrade_Script.sql
USE TEST_DB
:r $(filePath)Create_StoredProcedures.sql
GO

Open in new window


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

Changed database context to 'TEST_DB'.
Msg 111, Level 15, State 1, Server localhost\TESTINSTANCE, Procedure GetSP, Line 19
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Open in new window



Trial 5:
So I retained the "USE TEST_DB GO" at the top of the "Create_StoredProcedures.sql" script and also included "USE TEST_DB" (without GO) in "Test.sql" right before invoking the "Create_StoredProcedures.sql" script (below is the code). Now it creates the stored procedure in TEST_DB.

Test.sql:
USE TEST_DB
GO
:r $(filePath)Upgrade_Script.sql
USE TEST_DB
:r $(filePath)Create_StoredProcedures.sql
GO

Open in new window


Create_StoredProcedures.sql:
USE TEST_DB
GO
CREATE PROCEDURE [dbo].[GetSP] @indate DateTime AS
BEGIN
	SET NOCOUNT ON
	SELECT * FROM [dbo].EMP WHERE EMP_JOIN_DATE > @indate;
END
--GO

Open in new window


So after including "USE TEST_DB" both in Test.sql and in Create_StoredProcedures.sql, with "GO" only inside "Create_StoredProcedures.sql" script, the stored procedure gets created in TEST_DB database.

My real problem:
My real "Test.sql" script has much more logic than what I have stated above. Below is the whole logic. Though Trial 5 created the stored procedure in TEST_DB, due to the GO inside the "Create_StoredProcedures.sql" script, the context of the variables declared in the "Test.sql" script gets lost (below is the code and error returned).

Table Version:
CREATE TABLE [dbo].[Version]
(Version varchar(10) NOT NULL,
 UpdatedDt datetime NOT NULL);

Open in new window


Test.sql:
USE TEST_DB
GO

DECLARE @v_current_version        VARCHAR(10),
	@v_temp		           VARCHAR(10),
	@v_flag	           VARCHAR(1),
	@v_target_version         VARCHAR(10) = '2',
	@err_message	NVARCHAR(4000);
		
BEGIN
	BEGIN TRY
	
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))
		BEGIN
				SELECT @v_current_version = [Version] FROM Version;
				
		
			IF (@v_current_version <> @v_target_version)
			BEGIN
				IF (@v_current_version = '1')
					BEGIN
						 :r $(filePath)Upgrade_Script.sql
						 USE TEST_DB
						 :r $(filePath)Create_StoredProcedures.sql
						 SET @v_flag = 'Y';
					END
				ELSE
					BEGIN
						
						SET @v_temp = ISNULL(@v_current_version,'N/A')
						RAISERROR(50002,16,1,@v_temp)
					END
				
				
				IF (@v_flag = 'Y')
					BEGIN
						print '---------------------Starting to update Database Version----------------------------'					
						BEGIN TRANSACTION 
							UPDATE [dbo].[Version] 
							   SET [Version] = @v_target_version,
								   [UpdatedDt] = GETDATE()
						       
						COMMIT TRANSACTION;
						
						PRINT '                                                           '
						print 'Database Version after upgrade: ' 
						SELECT [version] FROM [TEST_DB].[dbo].[Version]
						PRINT '                                                           '						
						
						print '---------------------Done updating Database Version----------------------------'

					END
			END --if current version <> target version
		END	--for IF table exists
	ELSE 
		/*------------------------------------------------------
		Version does not exist in TEST_DB database
		------------------------------------------------------*/
		BEGIN
			--Not able to determine current database version
			RAISERROR(50001,16,1)
		END

END TRY
BEGIN CATCH
	BEGIN
		--Building error message
		SET @err_message = 'Err in database upgrade: '+ERROR_MESSAGE()
		RAISERROR(@err_message, 16, 1)
	END      			
END CATCH

END
GO

Open in new window


Error:
C:\TEST>sqlcmd -S localhost\TESTINSTANCE -i test.sql -v  filepath=".\"
Changed database context to 'TEST_DB'.
Msg 102, Level 15, State 1, Server localhost\TESTINSTANCE, Line 42
Incorrect syntax near 'TEST_DB'.
Msg 156, Level 15, State 1, Server localhost\TESTINSTANCE, Procedure GetSP, Line 8
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Server localhost\TESTINSTANCE, Procedure GetSP, Line 11
Must declare the scalar variable "@v_current_version".
Msg 137, Level 15, State 2, Server localhost\TESTINSTANCE, Procedure GetSP, Line 12
Must declare the scalar variable "@v_temp".
Msg 137, Level 15, State 2, Server localhost\TESTINSTANCE, Procedure GetSP, Line 16
Must declare the scalar variable "@v_flag".
Msg 137, Level 15, State 2, Server localhost\TESTINSTANCE, Procedure GetSP, Line 21
Must declare the scalar variable "@v_target_version".
Msg 156, Level 15, State 1, Server localhost\TESTINSTANCE, Procedure GetSP, Line 35
Incorrect syntax near the keyword 'END'.
Msg 102, Level 15, State 1, Server localhost\TESTINSTANCE, Procedure GetSP, Line 45
Incorrect syntax near 'TRY'.
Msg 137, Level 15, State 1, Server localhost\TESTINSTANCE, Procedure GetSP, Line 49
Must declare the scalar variable "@err_message".
Msg 137, Level 15, State 2, Server localhost\TESTINSTANCE, Procedure GetSP, Line 50
Must declare the scalar variable "@err_message".

Open in new window




How do I get the stored procedure created in TEST_DB database at the same time not loose the variable declaration context in "Test.sql"?

Thanks!
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 167 total points
ID: 38849526
Your Upgrade_Script.sql changes to Master db, so all subsequent commands/files are applied to that DB, if there is no USE.
Firstly you should explicitely refer to Master in that script:
--50001
IF EXISTS(SELECT * FROM master.sys.messages WHERE message_id = 50001)
BEGIN
   exec master.dbo.sp_dropmessage 50001
END
    
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT '																	End of Upgrade_Script																     '	  
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT '																																									 '

Open in new window

and then call sqlcmd with the proper DB (-d ...). That will solve the USE issue, but not the issue that each CREATE needs to be the only command in a T-SQL batch (and that rendering any var declaration void). That is the reason SSMS nowadays uses dynamic procedures to create  procedures aso. That is, it fills a string with the command and executes that (with exec sp_executesql).
0
 
LVL 14

Assisted Solution

by:nishant joshi
nishant joshi earned 166 total points
ID: 38850098
Error is really simple as stated integer variable not declare.

you have variable in stored procedure and while calling you are not passing any parameter.Pass the parameter to stored procedure or assign default value to parameter.

Thanks,
Nishant
0
 

Author Comment

by:sath350163
ID: 38851915
The Test.sql calls the Create_StoredProcedures.sql file which has code inside it to create stored procedure.
The stored procedure is not being executed in this case.

Am I missing something here.

Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38852226
Simply put you cannot create a Stored Procedure that does not compile for whatever reason (including missing declared parameters or variables).
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question