Hi
In below Sp i am getting error - Conversion failed when converting the varchar value 'SELECT MAX(docentry) FROM Department' to data type int.
Docentry is of data type Int.
ALTER PROCEDURE [dbo].[MaxDocEntry] -- Add the parameters for the stored procedure here @TableName VARCHAR(50) ,@DocEntry INT OUTPUT ,@Success BIT OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @SQL NVARCHAR(1000); DECLARE @Result INT; SET @SQL = 'SELECT @Result = ISNULL(MAX(docentry), 0) + 1 FROM ' + @TableName + ' WITH (TABLOCKX)'; EXEC sp_executesql @SQL, N'@Result INT OUTPUT', @Result OUTPUT; SET @DocEntry = @Result; IF @@ROWCOUNT > 0 SET @Success = 1; ELSE SET @Success = 0; END
When i execute below in Sql it works fine.
DECLARE @SQL NVARCHAR(1000); DECLARE @Result INT; SET @SQL = 'SELECT @Result = ISNULL(MAX(docentry), 0) + 1 FROM ' + 'Department' + ' WITH (TABLOCKX)'; EXEC sp_executesql @SQL, N'@Result INT OUTPUT', @Result OUTPUT; print @result
Thanks