Solved

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

Posted on 2013-02-02
7
806 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
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 68

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 68

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now