0

I'm using SP that gets date as parameter, I'm trying to use the varable inside the query but SQL doesn't seems to "see" it

alter procedure PipeReport
  @1 as DATE
as
begin

DECLARE @SqlCD NVARCHAR(MAX) 
DECLARE @Thickness NVARCHAR(100)

--DECLARE @SlpCode INT

SET @SqlCD = N'
/* SELECT FROM  [dbo].[ORDR]  T2 */
 

  declare   @1 as DATE

SELECT C0.DocNum ,OI.ItemName'

/* הנתונים שעליהם רצה הלולאה */
DECLARE SizeList CURSOR FOR

       SELECT 

            cast( R1.U_Thickness as nvarchar)--+''/''

       FROM
              RDR1 R1
              INNER JOIN ORDR R ON R1.DocEntry = R.DocEntry
       WHERE
              R.U_ImpDate=@1
                            and 
              R1.[U_Shelf] is null
                       and 
R1.[LineStatus]=N'O'
       GROUP BY
              U_Thickness
                       
       HAVING 
       R1.U_Thickness IS NOT NULL 
       AND 
       R1.U_Thickness > 0 



OPEN SizeList


FETCH NEXT FROM SizeList INTO @Thickness


WHILE @@FETCH_STATUS = 0
BEGIN
       SET @Thickness = ( SELECT TOP 1 U_Thickness FROM RDR1 WHERE U_Thickness = @Thickness)
       SET @SqlCD = @SqlCD + ',SUM( CASE WHEN T0.U_Thickness = ' + CONVERT(NVARCHAR(10),@Thickness) + N' THEN T0.Quantity END) AS N'' ' + @Thickness + N''' '
      
       FETCH NEXT FROM SizeList INTO @Thickness
END

SET @SqlCD = @SqlCD + N' FROM     
RDR1 T0   
INNER JOIN ORDR C0 ON C0.DocEntry = T0.DocEntry 
INNER JOIN OITM OI ON t0.ItemCode = OI.ItemCode
where 
C0.U_ImpDate = @1
AND 
T0.[U_Thickness] <>0 
and 
T0.[U_Shelf] is null
and 
OI.U_Group=400 
and 
T0.[LineStatus]=N''O''
AND C0.DocNum IS NOT NULL


Group BY ROLLUP(C0.DocNum),OI.ItemName
ORDER BY 1 DESC; '

CLOSE SizeList
DEALLOCATE SizeList

EXEC(@SqlCD)
end

1 Answer 1

4

A variable or parameter is visible only within the scope in which it is declared. The parameter @1 is visible in the body of your procedure, but not in the dynamic script your procedure is generating and executing. Even though the dynamic script is declaring a variable with the same name and type, the value of the @1 parameter is not automatically passed to it.

If you want to pass it, use EXEC sp_executesql <your query> instead of EXEC(<your query>) to run your dynamic SQL, because the former allows you to use proper parametrisation. In your specific case, what you need to do is this:

  1. Remove the declare @1 as DATE line from the dynamic SQL.

  2. Replace the EXEC(@SqlCD) line with this:

    EXEC sp_executesql @SqlCD, N'@1 DATE', @1;
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.