August 9, 2008 at 2:33 am
I am creating a stored procedure. Following is the codE:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetORIMSDetails]
@PivotBrandName nvarchar(200)
AS
BEGIN
SET NOCOUNT OFF;
DECLARE @PivotColumnName NVARCHAR(100)
DECLARE @strsql nvarchar(1000)
DECLARE @TotalStores DECIMAL
DECLARE @TotalSum DECIMAL
DECLARE @Mean DECIMAL
SELECT @PivotColumnName =DBO.GetPivotColumnName(@PivotBrandName)
SET @strsql=N'select @TotalStores= count(@PivotColumnName) FROM districtprofileORIMS'
PRINT @PivotColumnName
execute sp_executesql @strsql,N'@TotalStores DECIMAL OUTPUT,@PivotColumnName nvarchar(100)',@TotalStores OUTPUT,@PivotColumnName
PRINT @TotalStores
SET @strsql=N'SELECT @TotalSum= SUM(@PivotColumnName) FROM districtprofileORIMS'
execute sp_executesql @strsql,N'@TotalSum DECIMAL OUTPUT,@PivotColumnName nvarchar(100)',@TotalSum OUTPUT,@PivotColumnName
--IF @TotalSum!=0 && @TotalStores>0
--BEGIN
SET @Mean=@TotalSum/@TotalStores
PRINT @Mean
--END
END
it is giving me following error:
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for sum operator.
how can i solve this problem . any help please.
August 9, 2008 at 3:27 am
What are you trying to do?
If you're trying to set column names with a variable, it doesn't work that way. What your query is doing is summing the value contained within the variable, not the column so named. If you want to dynamically specify the column name, you need something more like this:
SET @strsql=N'SELECT @TotalSum= SUM(' + @PivotColumnName + ') FROM districtprofileORIMS'
execute sp_executesql @strsql,N'@TotalSum DECIMAL OUTPUT',@TotalSum OUTPUT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply