Wednesday, September 17, 2008

To pass a table name and FieldName as Parameter in SQl procedure(Update Display Order)

--usp_Admin_DisplayOrder_UpdateDisplayOrder 'Tbl_Category','1'
-- select * from Tbl_ForumPost

ALTER PROCEDURE [dbo].[usp_Admin_DisplayOrder_UpdateDisplayOrder]
@TableName varchar(100),
@Level varchar(10)

AS
DECLARE @RetVal INT,@strSql varchar(1000)
SET @RetVal = -1
SET @strSql =''
BEGIN TRY
BEGIN TRAN
IF(@Level <> '')
BEGIN
SET @strSql =@strSql+' Update '+ @TableName +' Set DisplayOrder=IsNull(DisplayOrder,0) +1 WHERE Level='+@Level
END
ELSE
BEGIN
SET @strSql =@strSql+' Update '+ @TableName +' Set DisplayOrder=IsNull(DisplayOrder,0) +1'
END

EXEC(@strSql)

COMMIT TRAN
--RETURN @RetVal
--PRINT @strSql
END TRY

BEGIN CATCH
ROLLBACK TRAN
SET @RetVal = -1
RETURN @RetVal
END CATCH

No comments: