CODEWITHSUNDEEP
sqlsql-serverstored-procedures
StuckAtWork
StuckAtWork

Reputation: 1

Stored Procedure Output Value Not Updating

Everyone.

I have created a stored procedure in SQL Server 2016 that has the purpose of giving the of the total number of sales recommendations [@Recommendation in Recommendations table] in my table that contain this string: '____Document DOB in sales record' past any given date. I want to put this result into a temporary table as well.

Helpful Table Info:

Regardless of what I change the date to - or even if I make it =, >=, etc - the same numeric value is displayed.

If you need anything else, please don't hesitate to ask me.

USE [Test_Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE usp_SalesRefCount
AS
BEGIN

--IF OBJECT_ID('tempdb..DocDOB') IS NOT NULL DROP TABLE #DocDOB
DECLARE @NumRecs INT;
-- Count of the number of recommendations


SELECT @NumRecs = COUNT(Recommendation)
FROM [Master_AGGREGATE].[dbo].[Recommendations]​
WHERE Recommendation = '____Document DOB in pt record' ​
and SalesProcessTime > '2018-10-20'

CREATE TABLE ##RefCount (NumberOfRecs INT); 
INSERT INTO ##RefCount
SELECT @NumRecs

END

EXEC usp_SalesRefCount

SELECT * FROM ##DocDOB

Upvotes: 0

Views: 59

Answers (2)

Alex
Alex

Reputation: 76

You are inserting data into a global temporary table "##RefCount" and selecting from another one "##DocDOB", so the result will never change.

Anyway, as @DavidBrowne said, you should just return the result directly

CREATE PROCEDURE usp_SalesRefCount
AS
BEGIN
  SELECT COUNT(Recommendation)
  FROM [Master_AGGREGATE].[dbo].[Recommendations]​
  WHERE Recommendation = '____Document DOB in pt record' ​
  and SalesProcessTime > '2018-10-20'
END

Also, another idea, maybe get that date as a parameter, so you don't have to update the stored procedure when you need to select by another date

CREATE PROCEDURE usp_SalesRefCount
@selectDate DateTime
AS
BEGIN
  SELECT COUNT(Recommendation)
  FROM [Master_AGGREGATE].[dbo].[Recommendations]​
  WHERE Recommendation = '____Document DOB in pt record' ​
  and SalesProcessTime > @selectDate
END

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Why are you using a global temporary table at all? Why not just return the results from the procedure directly? eg:

CREATE PROCEDURE usp_SalesRefCount
AS
BEGIN

  DECLARE @NumRecs INT;

  SELECT @NumRecs = COUNT(Recommendation)
  FROM [Master_AGGREGATE].[dbo].[Recommendations]​
  WHERE Recommendation = '____Document DOB in pt record' ​
  and SalesProcessTime > '2018-10-20'

  SELECT @NumRecs NumRecs 

END

Upvotes: 1

Related Questions