Link to home
Start Free TrialLog in
Avatar of pclarke7
pclarke7

asked on

Must declare the scalar variable error in SQL stored procedure

I am trying to write my 1st of many Stored Procedures which will perform functions on a MS SQL database (source) and a remote linked MS SQL database (target) such as :

  • Check Specific Databases exist on Source/Target Servers 
  • Check Specific tables exists on Source /Target databases
  • Check Tables have identical formats/fields on Source/Target databases
  • etc..

The 1st one that I have tried to develop is a stored procedure which will validate that a specific database exists on either Source/Target server. 


CREATE OR ALTER PROCEDURE Tios_Check_DB_Exists
(@linkedServer nvarchar(max),
@database nvarchar(max),
@rowCount int OUTPUT,
@sql nvarchar(max)=' ')
AS
BEGIN
SET NOCOUNT ON;
SET @sql='select @rowCount = Count(*) from '+@linkedServer+ '.master.dbo.sysdatabases where lower(name)=lower('''+@database+''')'
Print @sql
exec (@sql)  
RETURN @rowCount
END
GO

Open in new window


Calling the stored procedure

BEGIN
DECLARE @rowCount int
exec @rowCount = [XXX\SQLSERVER2019].master.dbo.Tios_Check_DB_Exists '[XXX\SQLSERVER2019]','DTA_TESTDTA' ,-1
print @rowCount
END

Open in new window


Result

select @rowCount = Count(*) from [XXX\SQLSERVER2019].master.dbo.sysdatabases where lower(name)=lower('DTA_TESTDTA')
Msg 137, Level 15, State 1, Line 62

Must declare the scalar variable "@rowCount".
-1

Completion time: 2022-01-01T20:17:52.9290824+00:00

Open in new window


Why are I getting the above error when I have defined @rowCount ?

How can I return any error messages encountered ?



Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pclarke7
pclarke7

ASKER

Thanks for putting me on the right track