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

Microsoft SQL Server

Avatar of undefined
Last Comment
pclarke7

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Pavel Celba

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pclarke7

ASKER
Thanks for putting me on the right track
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes