I am getting must declare scalar variable i have a

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

I am getting must declare scalar variable i have a

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-03-23 : 15:07:43

I am getting must declare scalar variable error message, i already have declared a variable within the Sp, not sure what it means.
i have it declared @UserID

Thank you very much for the helpful info.


ALTER PROCEDURE [dbo].[USP_GetOBProjectsByModuleCount]

		@UserName		nvarchar(50),

		@ProgID			INT,

		@ModuleType		nvarchar(50),

		@gLanguage		nvarchar(20),
		
		@searchKey varchar(50)


 AS



DECLARE @ErrorCode				int

DECLARE @EnterpriseLevelAccess			INT --- changed from Varchar(50)

DECLARE @UserID	as int


declare @query nvarchar(2000)


SET NOCOUNT ON

SELECT @ErrorCode = @@Error

IF @ErrorCode = 0

BEGIN

	SET @EnterpriseLevelAccess = (SELECT EnterpriseLevelAccess FROM TAB_Users WHERE UserName = @UserName)



	IF @EnterpriseLevelAccess = 1 OR @EnterpriseLevelAccess = 2 OR @EnterpriseLevelAccess = 3

	BEGIN

		SET @UserID = (SELECT top 1 UserID FROM TAB_ccsNetUsers WHERE UserName = @UserName)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-23 : 15:11:50

Why are you checking @@ERROR when it hasn't done anything yet?

Please post the entire code as what you have posted doesn't have an issue that I can spot. I would bet the error is where you are building @query, which you haven't posted.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-03-23 : 15:15:30

Sorry Tara,

here is the entire Sp:



ALTER PROCEDURE [dbo].[USP_GetOBProjectsByModuleCount]

		@UserName		nvarchar(50),

		@ProgID			INT,

		@ModuleType		nvarchar(50),

		@gLanguage		nvarchar(20),
		
		@searchKey varchar(50)


 AS



DECLARE @ErrorCode				int

DECLARE @EnterpriseLevelAccess			INT --- changed from Varchar(50)

DECLARE @UserID	as int


declare @query nvarchar(2000)


SET NOCOUNT ON

SELECT @ErrorCode = @@Error

IF @ErrorCode = 0

BEGIN

	SET @EnterpriseLevelAccess = (SELECT EnterpriseLevelAccess FROM TAB_Users WHERE UserName = @UserName)



	IF @EnterpriseLevelAccess = 1 OR @EnterpriseLevelAccess = 2 OR @EnterpriseLevelAccess = 3

	BEGIN

		SET @UserID = (SELECT top 1 UserID FROM TAB_Users WHERE UserName = @UserName)

		
		

		set @query= N'select count(*) from (select ROW_NUMBER() OVER(ORDER BY projid)  RowNr, * from (Select  0 as ProjID, ''* Select *'' as ProjNO,null as ProjName, null as AccessMode union SELECT projid,  ProjNo, ProjName, 2 as AccessMode FROM TAB_Projects 

			where ProgID = @ProgID AND Deleted = ''0''  and projno like ''%'+@searchKey+'%''  )t1 ) tab '

				
			exec sp_executesql @query,N'@ProgID int',@ProgID
			
			
			SELECT @ErrorCode = @@Error
	END

	ELSE

	BEGIN	

		IF @EnterpriseLevelAccess = 4

		BEGIN	

		/*Select 0 as ProjID, '* Select *' as ProjNO,null as ProjName, null as AccessMode 

		union 

			SELECT A.ProjID, B.ProjNO, B.ProjName, A.AccessMode  FROM  TAB_UserProjects AS A JOIN

						'TAB_Projects AS B ON A.ProjID = B.ProjID

			WHERE  A.UserID = @UserID AND A.AccessMode = 2  AND  A.ProgID = @ProgID AND A.Deleted = '0'  AND B.Deleted = 0 */

			set @query= N'select count(*) from (select ROW_NUMBER() OVER(ORDER BY projid)  RowNr, * from (Select  0 as ProjID, ''* Select *'' as ProjID,null as ProjName, null as AccessMode union SELECT A.projid,  B.ProjNo, B.ProjName, A.AccessMode FROM TAB_Projects 

			AS A JOIN

						TAB_Projects AS B ON A.ProjID = B.ProjID

			WHERE  A.UserID = @UserID AND A.AccessMode = 2  AND  A.ProgID = @ProgID AND A.Deleted = ''0''  AND B.Deleted = 0 and projno like ''%'+@searchKey+'%''  )t1 ) tab order by projno asc '
					exec sp_executesql @query,N'@ProgID int',@ProgID
			SELECT @ErrorCode = @@Error

		END

		ELSE

		BEGIN

			IF @EnterpriseLevelAccess = 5

			BEGIN

				/*Select 0 as ProjID, '* Select *' as ProjNO,null as ProjName, null as AccessMode 

				union 

				SELECT Distinct  A.ProjID, B.ProjNO, B.ProjName, A.AccessMode  FROM  TAB_UserContracts AS A JOIN

					TAB_Projects AS B ON A.ProjID = B.ProjID

				WHERE  A.UserID = @UserID AND A.AccessMode = 2  AND  A.ProgID = @ProgID AND A.Deleted = '0'  AND B.Deleted = 0 */

				set @query= N'select count(*) from (select ROW_NUMBER() OVER(ORDER BY projid)  RowNr, * from (Select  0 as ProjID, ''* Select *'' as ProjID,null as ProjName, null as AccessMode union SELECT A.projid,  B.ProjNo, B.ProjName, A.AccessMode FROM TAB_Projects 

			AS A JOIN

						TAB_Projects AS B ON A.ProjID = B.ProjID

			WHERE  A.UserID = @UserID AND A.AccessMode = 2  AND  A.ProgID = @ProgID AND A.Deleted = ''0''  AND B.Deleted = 0 and projno like ''%'+@searchKey+'%''  )t1 ) tab order by projno asc '
			
					exec sp_executesql @query,N'@ProgID int',@ProgID
					
				set @ErrorCode = @@Error

			END

		END

	END

END



SET NOCOUNT OFF

RETURN @ErrorCode

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-23 : 15:18:31

Why are you using dynamic SQL for this?

select blah
from blahtable
where somecolumn like '%' + @somevar + '%'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 15:25:25

Tara is correct. btw you're getting the error because the code executed here:


exec sp_executesql @query,N'@ProgID int',@ProgID

is in a different context and cannot see the variables defined in the calling context.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources