Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Anybody can ask a question
Anybody can answer
The best answers are voted up and rise to the top
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)--+'"https://dba.stackexchange.com/"' 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
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, used
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:
declare @1 as DATEline from the dynamic SQL.
EXEC(@SqlCD)line with this:
EXEC sp_executesql @SqlCD, N'@1 DATE', @1;
Not the answer you’re looking for? Browse other questions tagged sql-server or ask your own question.