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/
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -