Must Declare the Scalar Value in SQL

Question

I am kinda new to stored procedure and scalar values. Somehow I came up with a thread wherein I need to convert my stored procedure to scalar value in order to produce the output I wanted. I tried converting my stored procedure into a function and now I am getting the error Must declare the scalar value @---

The code is

Create Function fn_logs( @Month varchar(50) ,@Year varchar(50) ,@date_from datetime ,@date_to datetime) RETURNS @Logs TABLE ( -- Columns returned by the function UserID int PRIMARY KEY NOT NULL, Fullname nvarchar(max) NULL, Description nvarchar(250) NULL, Department nvarchar(250) NULL, DepartmentHead nvarchar(250) NULL, Position nvarchar(250) NULL, Date nvarchar(250) NULL, Month1 nvarchar(250) NULL, Year1 nvarchar(250) NULL, AMIN nvarchar(250) NULL, AMOUT nvarchar(250) NULL, PMIN nvarchar(250) NULL, PMOUT nvarchar(250) NULL ) begin DECLARE @AM DATETIME , @AM_MID DATETIME ,@AM_OUT DATETIME, @PM DATETIME,@PM_MID DATETIME,@PM_OUT DATETIME, @ABSENT NVARCHAR, @Four INt, @IN_AM DATETIME, @OUT_AM DATETIME,@IN_PM DATETIME,@OUT_PM DATETIME; SET @AM= '12:01:00 AM' SET @AM_MID = '10:00:00 AM'; SET @AM_OUT = '12:59:59 PM'; SET @PM = '12:00:00 PM'; SET @PM_MID = '3:00:00 PM'; SET @PM_OUT = '11:59:59 PM'; SET @IN_AM = '8:00:00 AM'; SET @OUT_AM = '12:00:00 PM'; SET @IN_PM = '1:00:00 PM'; SET @OUT_PM = '5:00:00 PM'; SET @ABSENT = 'ABSENT'; SET @Four = 4; INSERT into @Logs Select @UserID= usrinfo.ID, @Fullname=usrinfo.Name, @Description=usrinfo.Description, @Department=grop.Description, @DepartmentHead=grop.DepartmentHead, @Position=grop.HeadPosition, @Date=FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy'), @Month1=DATENAME(month, auth.TransactionTime), @Year1=DATEPART(year, auth.TransactionTime), @AMIN=max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) , @AMOUT=min(case when auth.FunctionKey = '2' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_OUT then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) , @PMIN=max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @PM and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @PM_MID then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end), @PMOUT=min(case when auth.FunctionKey = '2' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @PM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @PM_OUT then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) from NGAC_AUTHLOG as auth INNER JOIN NGAC_USERINFO as usrinfo ON usrinfo.ID = auth.UserID INNER JOIN NGAC_GROUP as grop ON grop.ID = usrinfo.GroupID where auth.AuthResult ='0' AND usrinfo.GroupID = '1' AND DATENAME(month, auth.TransactionTime)=@Month AND DATEPART(year, auth.TransactionTime)=@Year AND FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy') between @date_from and @date_to group by FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy'),usrinfo.ID, usrinfo.Name,grop.DepartmentHead, grop.HeadPosition, grop.Description, usrinfo.ID, usrinfo.Description, DATEPART(year, auth.TransactionTime), DATENAME(month, auth.TransactionTime); RETURN; end;

The stored procedure is about getting the Employee Logs in a specific day. It consists of three databases for the Employee information while the time is being displayed by selecting the max() and min() time of Employee depending on the time range manually selected. The error persists in the Insert method after Select

What am I doing wrong? Thanks for the enlightenment.

RETURNS @LogsTABLE - should be RETURNS @Logs TABLE , space is missing. — Abhishek
– Abhishek, Commented Mar 7, 2018 at 8:50

Rigerta · Accepted Answer · 2018-03-07 08:55:38Z

Replace the INSERT statement with this:

insert into @Logs select usrinfo.ID, usrinfo.Name, usrinfo.Description, grop.Description, grop.DepartmentHead, grop.HeadPosition, FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy'), DATENAME(month, auth.TransactionTime), DATEPART(year, auth.TransactionTime), max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) , min(case when auth.FunctionKey = '2' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_OUT then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) , max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @PM and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @PM_MID then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end), min(case when auth.FunctionKey = '2' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @PM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @PM_OUT then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) from NGAC_AUTHLOG as auth INNER JOIN NGAC_USERINFO as usrinfo ON usrinfo.ID = auth.UserID INNER JOIN NGAC_GROUP as grop ON grop.ID = usrinfo.GroupID where auth.AuthResult ='0' and usrinfo.GroupID = '1' and DATENAME(month, auth.TransactionTime)=@Month and DATEPART(year, auth.TransactionTime)=@Year and FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy') between @date_from and @date_to

The way you have it, you are only assigning values to those variables. The SELECT in that case is only working as a SET:

declare @SomeVar int select @SomeVar = 5 -- this is just setting a value to the variable set @SomeVar = 5 -- same as this

You don't need the additional variables (@UserID, @Fullname etc.) in that scenario.

So I was doing it the wrong way. I was adding additional variables in it. Thanks for the help. Works perfectly

B3S · Accepted Answer · 2018-03-07 08:57:16Z

I've commented the code below adding an explanation about your errors

Create Function fn_logs( @Month varchar(50) ,@Year varchar(50) ,@date_from datetime ,@date_to datetime) RETURNS @Logs TABLE --error here previous was @LogsTABLE ( -- Columns returned by the function UserID int PRIMARY KEY NOT NULL, Fullname nvarchar(max) NULL, Description nvarchar(250) NULL, Department nvarchar(250) NULL, DepartmentHead nvarchar(250) NULL, Position nvarchar(250) NULL, Date nvarchar(250) NULL, Month1 nvarchar(250) NULL, Year1 nvarchar(250) NULL, AMIN nvarchar(250) NULL, AMOUT nvarchar(250) NULL, PMIN nvarchar(250) NULL, PMOUT nvarchar(250) NULL ) begin DECLARE @AM DATETIME , @AM_MID DATETIME ,@AM_OUT DATETIME, @PM DATETIME,@PM_MID DATETIME,@PM_OUT DATETIME, @ABSENT NVARCHAR, @Four INt, @IN_AM DATETIME, @OUT_AM DATETIME,@IN_PM DATETIME,@OUT_PM DATETIME; SET @AM= '12:01:00 AM' SET @AM_MID = '10:00:00 AM'; SET @AM_OUT = '12:59:59 PM'; SET @PM = '12:00:00 PM'; SET @PM_MID = '3:00:00 PM'; SET @PM_OUT = '11:59:59 PM'; SET @IN_AM = '8:00:00 AM'; SET @OUT_AM = '12:00:00 PM'; SET @IN_PM = '1:00:00 PM'; SET @OUT_PM = '5:00:00 PM'; SET @ABSENT = 'ABSENT'; SET @Four = 4; INSERT into @Logs ( UserID, Fullname, Description, Department, DepartmentHead, Position, Date, Month1, Year1, AMIN, AMOUT, PMIN, PMOUT ) Select --You cant use variable assignment in an insert+subquery usrinfo.ID, usrinfo.Name, usrinfo.Description, grop.Description, grop.DepartmentHead, grop.HeadPosition, FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy'), DATENAME(month, auth.TransactionTime), DATEPART(year, auth.TransactionTime), max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) , min(case when auth.FunctionKey = '2' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_OUT then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) , max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @PM and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @PM_MID then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end), min(case when auth.FunctionKey = '2' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @PM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @PM_OUT then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) from NGAC_AUTHLOG as auth INNER JOIN NGAC_USERINFO as usrinfo ON usrinfo.ID = auth.UserID INNER JOIN NGAC_GROUP as grop ON grop.ID = usrinfo.GroupID where auth.AuthResult ='0' AND usrinfo.GroupID = '1' AND DATENAME(month, auth.TransactionTime)=@Month AND DATEPART(year, auth.TransactionTime)=@Year AND FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy') between @date_from and @date_to group by FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy'),usrinfo.ID, usrinfo.Name,grop.DepartmentHead, grop.HeadPosition, grop.Description, usrinfo.ID, usrinfo.Description, DATEPART(year, auth.TransactionTime), DATENAME(month, auth.TransactionTime); RETURN; end;

Collectives™ on Stack Overflow

Must Declare the Scalar Value in SQL

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related