I am getting an error as :
Incorrect Syntax near ' + @Items + ' while making a new stored procedure stored procedure. I am attaching the code,
-- =============================================
-- Author: <Varun Sareen>
-- Create date: <04/05/2010>
-- Description: <Item L5 Description Search>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Search_Item_L5_Desc]
-- Add the parameters for the stored procedure here
@strSearchKeyword varchar(200),
@Current_Logged_User int,
@Items sysname,
@Unit_Of_Measurement sysname,
@Source sysname,
@User_Default_Sources sysname,
@User_Default_Options sysname,
@Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn sysname
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;
SET @Items=RTRIM(@Items)
SET @Unit_Of_Measurement=RTRIM(@Unit_Of_Measurement)
SET @Source=RTRIM(@Source)
SET @User_Default_Sources=RTRIM(@User_Default_Sources)
SET @User_Default_Options=RTRIM(@User_Default_Options)
SET @strSearchKeyword=RTRIM(@strSearchKeyword)
SET @Current_Logged_User=RTRIM(@Current_Logged_User)
SET @Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn=RTRIM(@Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn)
-- Insert statements for procedure here
Declare @temp_ItemsL5 Table
(
item_id int,
item_level5_code varchar(10),
item_short_description varchar(max),
item_long_description varchar(max),
unit_symbol varchar(25),
urc_code varchar(50),
source_code varchar(50),
item_level_id int,
number_of_units decimal(18,9),
cost_of_one_unit decimal(30,9),
net_item_amount decimal(30,9)
)
insert into @temp_ItemsL5
select a.item_id,a.item_level5_code,a.item_short_description,a.item_long_description,b.unit_symbol, a.urc_code,c.source_code,a.item_level_id,a.number_of_units,a.cost_of_one_unit,a.net_item_amount
from ' + @Items + ' a
inner join ' + @Unit_Of_Measurement + ' b on a.unit_id=b.unit_id
inner join ' + @Source + ' c on c.source_id=a.source_id
inner join ' + @User_Default_Sources + ' uds on uds.source_id = c.source_id
inner join ' + @User_Default_Options + ' udo on udo.user_default_option_id = uds.user_default_option_id
inner join ' + @Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn + '(@strSearchKeyword,' ') f on a.item_id=f.tempId
where udo.user_id= ' + @Current_Logged_User + ' order by f.Id
END
'@Items' is table name passed as a parameter
Thanks
Varun Sareen