Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Swati Jain
Posting Yak Master
139 Posts |
Posted - 2008-03-24 : 03:36:33
|
Following stored proc uses dynamic sql but it gives the errorMsg 137, Level 15, State 2, Line 3Must declare the scalar variable "@ProjectBenefitID".though its declared .please. tell the workaroundALTER PROCEDURE [dbo].[spPMPT_GetBenefit]@ProjectBenefitID INT,@OrderBY VARCHAR(40),-- Parmeters for Paging [Start]@TotalPages INT OUT , @CurrentPageNumber INT OUT , @NumberOfRecords INT = 5 , /*PageSize*/ @CurrentPage INT = 0 /*PageNumber*/-- Parmeters for Paging [End]ASSET NOCOUNT ON DECLARE @TMP FLOAT DECLARE @ErrorMsgID INT DECLARE @ErrorMsg VARCHAR(200) ----- Paging declarations start DECLARE @SQLFinal NVARCHAR(4000) DECLARE @Count INT DECLARE @SC VARCHAR(4000) ----- Paging declarations endDECLARE @Select AS VARCHAR(4000) DECLARE @From AS VARCHAR(4000) DECLARE @Where AS VARCHAR(4000) DECLARE @LsOrderBy AS VARCHAR(4000) -- Initialize vars SET @SC = '' SET @From = '' SET @Where = '' SET @Select = '' SET @SQLFinal = '' SET @Count = 0 IF (@CurrentPage = 0 OR @CurrentPage IS NULL) BEGIN --Generate error message SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.' --Raise error to the user RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/) --Return error indicator RETURN (-1) END IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL ) BEGIN --Generate error message SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.' --Raise error to the user RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/) --Return error indicator RETURN (-1) END IF (@Orderby IS NULL OR @Orderby = '') BEGIN --Generate error message SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.' --Raise error to the user RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/) --Return error indicator RETURN (-1) END CREATE TABLE #TEMP_BENEFIT1 ( AssessBenefitID INT, ProjectBenefitID INT, ExpectedQuantity INT, ExpectedQuality VARCHAR(2000), Comments VARCHAR(2000) )INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID, Quantity,Quality, Comments FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationIDCREATE TABLE #TEMP_BENEFIT2 ( AssessBenefitID INT, ProjectBenefitID INT, ActualQuantity INT, QtyFileID INT, QtyFileName VARCHAR(100), QtyFilepath VARCHAR(100), ActualQuality VARCHAR(2000), QuaFileID INT, QualFileName VARCHAR(100), QualFilepath VARCHAR(100), Comments VARCHAR(2000), refAssessBenefitID INT, DateasON DATETIME )INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID, PAB.Quantity,pab.qtyFileID, (SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName, (SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath, PAB.Quality,pab.quaFileID, (SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName, (SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath, PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasON FROM PMPT_AssessBenefit PAB WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A' DECLARE @UNIT VARCHAR(100)SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)IF @UNIT IS NULL SET @UNIT = '' SET @Select=' DECLARE @UNIT VARCHAR(100)SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID))+' SELECT T1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity, CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality , T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasONFROM #TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2 WHERE T1.AssessBenefitID = T2.refAssessBenefitID' |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-24 : 04:56:15
|
I can see the only problem with this SP, that is extra brace highlighted in red below:ALTER PROCEDURE [dbo].[spPMPT_GetBenefit]@ProjectBenefitID INT,@OrderBY VARCHAR(40),-- Parmeters for Paging [Start]@TotalPages INT OUT , @CurrentPageNumber INT OUT , @NumberOfRecords INT = 5 , /*PageSize*/ @CurrentPage INT = 0 /*PageNumber*/-- Parmeters for Paging [End]ASSET NOCOUNT ON DECLARE @TMP FLOAT DECLARE @ErrorMsgID INTDECLARE @ErrorMsg VARCHAR(200)----- Paging declarations startDECLARE @SQLFinal NVARCHAR(4000)DECLARE @Count INTDECLARE @SC VARCHAR(4000)----- Paging declarations endDECLARE @Select AS VARCHAR(4000) DECLARE @From AS VARCHAR(4000) DECLARE @Where AS VARCHAR(4000) DECLARE @LsOrderBy AS VARCHAR(4000)-- Initialize vars SET @SC = '' SET @From = '' SET @Where = ''SET @Select = ''SET @SQLFinal = '' SET @Count = 0 IF (@CurrentPage = 0 OR @CurrentPage IS NULL) BEGIN --Generate error message SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.' --Raise error to the user RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/) --Return error indicator RETURN (-1) END IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL ) BEGIN --Generate error message SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.' --Raise error to the user RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/) --Return error indicator RETURN (-1) ENDIF (@Orderby IS NULL OR @Orderby = '') BEGIN --Generate error message SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.' --Raise error to the user RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/) --Return error indicator RETURN (-1) ENDCREATE TABLE #TEMP_BENEFIT1(AssessBenefitID INT,ProjectBenefitID INT,ExpectedQuantity INT,ExpectedQuality VARCHAR(2000),Comments VARCHAR(2000))INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID,Quantity,Quality,CommentsFROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationIDCREATE TABLE #TEMP_BENEFIT2(AssessBenefitID INT,ProjectBenefitID INT,ActualQuantity INT,QtyFileID INT,QtyFileName VARCHAR(100),QtyFilepath VARCHAR(100),ActualQuality VARCHAR(2000),QuaFileID INT,QualFileName VARCHAR(100),QualFilepath VARCHAR(100),Comments VARCHAR(2000),refAssessBenefitID INT,DateasON DATETIME)INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID,PAB.Quantity,pab.qtyFileID,(SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName,(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath,PAB.Quality,pab.quaFileID,(SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName,(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath,PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasONFROM PMPT_AssessBenefit PABWHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A' DECLARE @UNIT VARCHAR(100)SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)IF @UNIT IS NULLSET @UNIT = '' SET @Select=' DECLARE @UNIT VARCHAR(100)SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID))+'SELECT T1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity,CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality ,T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasONFROM #TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2 WHERE T1.AssessBenefitID = T2.refAssessBenefitID'Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
|
|
|
|