I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.
Error: Unterminated String Constant.
What is the problem?
Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By '+ @.SortExpression+') as Row_Count,Rank() Over (Order By '+ @.SortExpression+') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between '+@.startRowIndex+' and '+ @.maxRowIndex+' ';
Exec(@.strSQL);
Can you please try:
Where Row_Count Between ' +Convert(Varchar,@.startRowIndex)+' and '+Convert(Varchar,@.maxRowIndex)+''
|||Try
Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By ' + @.SortExpression + ') as Row_Count,Rank() Over (Order By ' + @.SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between ' +CONVERT(VARCHAR(10),@.startRowIndex) -- the integer needs to be converted to varchar for the + to be a concatenate!
+ ' and ' +CONVERT(VARCHAR(10),@.maxRowIndex) + ' ';
PRINT @.strSQL -- Comment this out once the TSQL is generated correctly
Exec(@.strSQL);
It works! I was converting it to an integer with the convert function. I didn't realized I had to convert it as a string..
)