3 Replies - 1722 Views - Last Post: 27 August 2012 - 04:55 AM

#1 hamidkhl  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 03-November 08

Problem with my stored proc

Posted 24 May 2012 - 03:15 PM

Hello dear friends

I have a simple database

Attached Image


I wrote this stored proc


ALTER PROC BadHesab(@BadHesabiDays INT)
AS
BEGIN
    

	CREATE TABLE #Temp
	(
		DateItem DATETIME
	)
	
	INSERT INTO #Temp 
		SELECT Buy.[Date] 
			FROM Buy
	
	CREATE TABLE #DeffNum
	(
		Num INT
	)	

	INSERT INTO #DeffNum
	SELECT DATEDIFF(DAY,(SELECT #Temp.DateItem FROM #Temp ) ,GETDATE())
	
	SELECT * FROM #DeffNum 
	
	
	SELECT Customer.FirstName, Customer.LastName , Buy.[Date] AS 'Buy Date', GETDATE() AS 'Now', Buy.Price AS 'Price',Buy.[Count] AS 'Count', Goods.Title AS 'Good'
	FROM Buy INNER JOIN Customer 
	ON Buy.Customer_Id = Customer.Id 
			INNER JOIN Goods 
	ON Buy.Good_Id= Goods.Id 
	WHERE  @BadHesabiDays < (SELECT #DeffNum.Num 
								FROM #DeffNum 
									WHERE  #DeffNum.Num  > @BadHesabiDays)
	
	DROP TABLE #Temp
	DROP TABLE #DeffNum
	
END




but i get this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I know it's about this: SELECT DATEDIFF(DAY,(SELECT #Temp.DateItem FROM #Temp ) ,GETDATE())

but I can't any repletion way!
please help

Is This A Good Question/Topic? 0
  • +

Replies To: Problem with my stored proc

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Problem with my stored proc

Posted 26 May 2012 - 09:28 AM

Hey.

The problem there, as the error says, is that you are using a sub-query that returns multiple rows, but the SQL Server is expecting only one value.

If you are doing what I think you are doing, you may want to try this instead:
INSERT INTO #DeffNum
SELECT DATEDIFF(DAY, DateItem ,GETDATE()) FROM #Temp


There, instead of returning a list of dates into the DATEDIFF function, I return a result set where DATEDIFF is applied to each single DateItem field value.
Was This Post Helpful? 0
  • +
  • -

#3 Pirion  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-August 12

Re: Problem with my stored proc

Posted 26 August 2012 - 07:18 PM

View Posthamidkhl, on 24 May 2012 - 03:15 PM, said:

Hello dear friends

I have a simple database

Attachment 3.JPG


I wrote this stored proc


ALTER PROC BadHesab(@BadHesabiDays INT)
AS
BEGIN
    

	CREATE TABLE #Temp
	(
		DateItem DATETIME
	)
	
	INSERT INTO #Temp 
		SELECT Buy.[Date] 
			FROM Buy
	
	CREATE TABLE #DeffNum
	(
		Num INT
	)	

	INSERT INTO #DeffNum
	SELECT DATEDIFF(DAY,(SELECT #Temp.DateItem FROM #Temp ) ,GETDATE())
	
	SELECT * FROM #DeffNum 
	
	
	SELECT Customer.FirstName, Customer.LastName , Buy.[Date] AS 'Buy Date', GETDATE() AS 'Now', Buy.Price AS 'Price',Buy.[Count] AS 'Count', Goods.Title AS 'Good'
	FROM Buy INNER JOIN Customer 
	ON Buy.Customer_Id = Customer.Id 
			INNER JOIN Goods 
	ON Buy.Good_Id= Goods.Id 
	WHERE  @BadHesabiDays < (SELECT #DeffNum.Num 
								FROM #DeffNum 
									WHERE  #DeffNum.Num  > @BadHesabiDays)
	
	DROP TABLE #Temp
	DROP TABLE #DeffNum
	
END




but i get this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I know it's about this: SELECT DATEDIFF(DAY,(SELECT #Temp.DateItem FROM #Temp ) ,GETDATE())

but I can't any repletion way!
please help



On lines 31 - 33, you are doing a single value vs. single value comparison, but you have multiple values in your #temp table. What is your intention with the WHERE clause on 31 - 33?
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5882
  • View blog
  • Posts: 12,761
  • Joined: 16-October 07

Re: Problem with my stored proc

Posted 27 August 2012 - 04:55 AM

Let's start over. First, you have this basic result set:
SELECT Customer.FirstName, Customer.LastName, 
		Buy.[Date] AS [Buy Date], 
		GETDATE() AS [Now], 
		Buy.Price,
		Buy.[Count], 
		Goods.Title AS [Good]
	FROM Buy 
		INNER JOIN Customer 
			ON Buy.Customer_Id = Customer.Id 
		INNER JOIN Goods 
			ON Buy.Good_Id = Goods.Id 



If I got it right, running that should get all entries.

Now, you want to filter that. First, you want the number of days between you [Buy Date] and now. That would reasonably be:
SELECT Buy.[Date], 
		DATEDIFF(DAY,Buy.[Date], GETDATE()) as [Days1],
		GETDATE() - Buy.[Date] as [Days2] -- should be the same as Days1, just easier to write
	FROM Buy



Notice how no temp tables were killed for this result. But you have further criteria:
SELECT *
	FROM Buy
	WHERE GETDATE() - Buy.[Date] < @BadHesabiDays



If that behaves properly, put it all together:
SELECT Customer.FirstName, Customer.LastName, 
		Buy.[Date] AS [Buy Date], 
		GETDATE() AS [Now], 
		Buy.Price,
		Buy.[Count], 
		Goods.Title AS [Good]
	FROM Buy 
		INNER JOIN Customer 
			ON Buy.Customer_Id = Customer.Id 
		INNER JOIN Goods 
			ON Buy.Good_Id = Goods.Id 
	WHERE GETDATE() - Buy.[Date] < @BadHesabiDays



Your preferred choice should be a query with joins. Avoid the IN statement, you never need it. If you must, use temp tables. These are only a good choice in that they will later allow you to use join logic and avoid the worst case, cursors. However, if you can load it into a temp table, you might be able to get it into a single query, which is the goal.

Become fluent in SQL before all other tools. T-SQL is nice, but shouldn't be a replacement for good ole SQL.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1