Richard Richard - 15 days ago 6
SQL Question

cannot PUT the declared variable on sp_executesql

I have this code in my SP:

ALTER PROCEDURE [dbo].[API_LIST_CONTENT_TEST]
@Page int,
@PageSize int = 20,
@TotalPage int Output
AS
BEGIN
SET NOCOUNT ON

-- PAGING --
DECLARE @FirstPage INT, @LastPage INT, @TotalRecord MONEY
SELECT @FirstPage = (@Page - 1) * @PageSize,
@LastPage = (@Page * @PageSize) + 1,
@TotalPage = 0,
@TotalRecord = 0

-- DATA --
CREATE TABLE #Data(
ID INT IDENTITY(1,1) NOT NULL,
Distance INT
)

DECLARE @TodayOpen nvarchar(100), @TodayClose nvarchar(100), @SQLSELECT NVARCHAR(MAX)
SELECT @TodayOpen = DATENAME(dw,GETDATE())+'Open', @TodayClose = DATENAME(dw,GETDATE())+'Close', @SQLSELECT = ''

SELECT @TotalRecord = COUNT(1) FROM #Data
SELECT @TotalPage = CASE WHEN @TotalRecord = 0 THEN 0 ELSE CEILING(@TotalRecord/@PageSize) END

SELECT @SQLSELECT = N'
SELECT
B.[Address],
B.'+ @todayOpen +',
B.'+ @todayClose +'
FROM #Data D
INNER JOIN dbo.Bis B WITH (NOLOCK) ON D.BizID = B.BizID
LEFT JOIN dbo.Category C WITH (NOLOCK) ON B.FIDCategory = C.CategoryID
WHERE B.[Status] = 3 AND ID > '+ @FirstPage +'
ORDER BY ID'

EXEC (@SQLSELECT)

DROP TABLE #Data

END


it results with exception:


"Conversion failed when converting the nvarchar value
SELECT
B.FIDCategory,......
....WHERE B.[Status] = 3 AND ID > '
to data type int.


The
@FirstPage
can't be read at SQLSELECT, why?

I'm First Grad Programmer, so any help is appreciated.

Answer

You need to convert @FirstPage variable from int to Varchar as you are creating dynamic query and set string value to @SQLSELECT variable which is varchar so it is not allow to set INT value in it.

Your SP look like

ALTER PROCEDURE [dbo].[API_LIST_CONTENT_TEST]
@Page int,
@PageSize int = 20,
@TotalPage int Output
AS
BEGIN
SET NOCOUNT ON

-- PAGING --
DECLARE @FirstPage INT, @LastPage INT, @TotalRecord MONEY
SELECT  @FirstPage = (@Page - 1) * @PageSize,
        @LastPage = (@Page * @PageSize) + 1,
        @TotalPage = 0,
        @TotalRecord = 0

-- DATA --
CREATE TABLE #Data(
    ID INT IDENTITY(1,1) NOT NULL,
    Distance INT
)

DECLARE @TodayOpen nvarchar(100), @TodayClose nvarchar(100), @SQLSELECT NVARCHAR(MAX)
SELECT @TodayOpen = DATENAME(dw,GETDATE())+'Open', @TodayClose = DATENAME(dw,GETDATE())+'Close', @SQLSELECT = ''

SELECT @TotalRecord = COUNT(1) FROM #Data
SELECT @TotalPage = CASE WHEN @TotalRecord = 0 THEN 0 ELSE CEILING(@TotalRecord/@PageSize) END

SELECT @SQLSELECT = N'
SELECT 
    B.[Address],
    B.'+ @todayOpen +',
    B.'+ @todayClose +'
FROM #Data D
    INNER JOIN dbo.Bis B WITH (NOLOCK) ON D.BizID = B.BizID
    LEFT JOIN dbo.Category C WITH (NOLOCK) ON B.FIDCategory = C.CategoryID
WHERE B.[Status] = 3 AND ID > '+ Convert(Varchar(10),@FirstPage) +' 
ORDER BY ID'

EXEC (@SQLSELECT)

DROP TABLE #Data

END