Must Declare the Scalar Variable

Must declare the scalar variable

You can't concatenate an int to a string. Instead of:

SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;

You need:

SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);

To help illustrate what's happening here. Let's say @RowTo = 5.

DECLARE @RowTo int;
SET @RowTo = 5;

DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;

In order to build that into a string (even if ultimately it will be a number), I need to convert it. But as you can see, the number is still treated as a number when it's executed. The answer is 25, right?

In your case you can use proper parameterization rather than use concatenation which, if you get into that habit, you will expose yourself to SQL injection at some point (see this and this:

SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';

EXEC sys.sp_executesql @sql,
  N'@RowFrom int, @RowTo int',
  @RowFrom, @RowTo;

How to fix Must declare the scalar variable issue in sql server dynamic query?

At a total guess, @lnRowIdMin is declared outside of the dynamic SQL, and needs to have the value assigned. You therefore need to parametrise your SQL, and use an OUTPUT parameter:

--Prior stuff here, including declaration of @lnRowIdMin

DECLARE @SQL nvarchar(MAX),
        @Param nvarchar(MAX);
SET @SQL = N'SELECT @lnRowIdMin = MIN(TMP_ROW_ID) FROM  ' + QUOTENAME(N'#' + @lvcBaseTable) + N';';
SET @Param = N'@lnRowIdMin int OUTPUT'; --Guessed datatype

EXEC sp_executesql @SQL, @Param, @lnRowIdMin = @lnRowIdMin OUTPUT;

Must declare the scalar variable SQL Server?

Here is an example of what your declarations might look like.

DECLARE @WebsiteID uniqueidentifier = NEWID()
    ,@FromDate DATETIME = GETDATE() - 1
    ,@ToDate DATETIME = GETDATE()

SELECT CD.CartId
    ,PR.Name
    ,PR.SKU
    ,CD.Quantity
    ,CD.Price
    ,CD.Total
    ,CD.IsAddedFromWidget
    ,CD.WidgetSlotLabel
    ,CD.AddToCartDate
    ,CO.UpdatedDate AS [CheckoutDate]
    ,CD.PurchaseDate
FROM [Tracking].[CartDetail] CD
INNER JOIN [Tracking].[Cart] C ON CD.CartId = C.Id
INNER JOIN [Tracking].[Product] PR ON CD.ProductId = PR.Id
INNER JOIN [Tracking].[Checkout] CO ON C.$NODE_ID = CO.$TO_ID
WHERE C.WebsiteId = @WebsiteID
    AND C.STATUS = 20
    AND CD.PurchaseDate >= @FromDate
    AND CD.PurchaseDate <= @ToDate
ORDER BY CD.PurchaseDate DESC
    ,CD.CartId DESC

Also, it appears to me that @Websited is a typo, so I've used @WebsiteID.

I've also removed the invalid { } braces from your code.

Additionally, the column names $NODE_ID and $TO_ID look a little weird to me. (The usage of $ is what I am talking about).

Dynamix SQL Error Must declare the scalar variable

You execute your generated statement with EXEC, so you call a new instance which is not aware of any declarations you made in your base script
You have to add the declarations to your new script

WHILE (@addCnt >= @cnt)
BEGIN
    SET @var = '@inAdd' + CAST(@cnt AS nvarchar(3))
    PRINT @var
    SET @sql = 'DECLARE ' + @var + ' nvarchar(50); DECLARE @inadd nvarchar(50); SET @inadd = ' + @var
    PRINT @sql
    EXEC sp_executesql @sql
    SET @cnt = @cnt + 1
    ... code to process @inAdd
END

UPDATE:
Considering your exact definition of the requirement, I would suggest, you think about other ways of doing it:

1. Import your address data into an import table and then use a sql script to process all rows in this table

2. Code a simple command line tool which processes your addresses (maybe in connection with an sql script to process the single lines)

3. If you are on SQL-Server:

   3.1) Pass your address data as "User-Defined Table Type"

   3.2) Pass your address data as one big string with a seperator und in your script split the string, and insert the result in a temporary table, then process all rows of the table

best regards,
Jimmy

Must declare the scalar variable @EmailID

You are not actually adding a value to your @EmailId parameter. You do it to @email and @PersonId, you are missing @EmailId.

Must declare the scalar variable @studentID

You're adding the parameters to the wrong SqlCommand. This

cmd.Parameters.AddWithValue("@studentID", studentId);

should be

sqlcmd.Parameters.AddWithValue("@studentID", studentId);

---

Related Topics