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