CODEWITHSUNDEEP
sql-serverstored-procedures
joshua
joshua

Reputation: 2371

Stored procedure is returning false value

I wrote a stored procedure for getting the count from a column but when I execute the procedure its returning a date value. While separate query execution returns the required result set.

ALTER PROCEDURE [dbo].[USP_ViewAdminDashBoard](@LoginUser varchar(75)) 
AS 
BEGIN 

    DECLARE @LastLoginDate  as DateTime 

    Select  @LastLoginDate = dbo.UDF_GetLastLoginByUser(@LoginUser);

    Select  'Last Login Date', @LastLoginDate 
    Union
    Select  'No. Active Business Associates' as Title, COUNT(isActive) Total 
    from    (select 1)d(isActive)
    where   isActive = 1 
  Union Select 'New Registration Today' as Title, COUNT(1) Total from dbo.TBL_iREGFORM     where isActive = 1 And GETDATE()>= RegDate
 Union Select 'Registration Pending for Verification' as Title, COUNT(1) Total from dbo.TBL_iREGFORM where isActive = 1 and isVerified = 1
END

Upvotes: 1

Views: 454

Answers (1)

nathan_jr
nathan_jr

Reputation: 9302

You are UNION-ing a datetime @LastLoginDate with an integer, COUNT(isActive).

This is causing the implicit conversion you are experiencing. To return these values in the same query you will need to convert them into a common datatype, likely varchar. Or, you can use two distinct queries.

If you do choose to continue with the union, be sure to use UNION ALL instead. Read more here.

Upvotes: 3

Related Questions