I am trying to create a Stored Procedure, and pass the name of a table as a parameter and I get this message:Must declare the table variable "@tblName".
What do I have to change to make it work?
CREATE PROCEDURE [usp_SaveToErrorLog]
@tblName as nvarchar(50),
@subject as nvarchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblName))
Begin
Insert Into @tblName
(ErrorNumber, ErrorMessage, ErrorProcedure, ErrorState, ErrorSeverity, ErrorLine)
Select
ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE()
End
END
Best Answer
Unfortunately, you can't use a variable containing the table name in an INSERT statement. The error message is actually occurring because it is expecting a variable that is a table type to insert rows into, but you're passing a variable with a string value.
You can use Dynamic SQL to execute the command using a variable table name value like this:
What this does is dynamically create your T-SQL command and store it in the @Sql variable. The stored procedure sp_executesql is then used to execute the T-SQL commands in the @Sql variable, which has the actual table name from the @tblName variable.