Click here to Skip to main content
15,906,766 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am getting an error as :Incorrect Syntax near ' + @Items + ' while making a new stored procedure stored procedure. I am attaching the code,

C++
-- =============================================
-- 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
Posted
Updated 4-May-10 1:49am
v2

Varun Sareen wrote:
from ' + @Items + ' a


This should just be from a.
 
Share this answer
 
Comments
Tom Deketelaere 4-May-10 8:14am    
Actually the 'a' is his alias for the table name passed in the @items parameter.
Abhinav S 4-May-10 10:41am    
Ah I missed that....Thanks.
Varun Sareen 5-May-10 0:45am    
then where will we define the alias name for the table with parameter name '@Items'
The problem is situated in your sql-string.

Varun Sareen wrote:
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


You can't just build your SQL-string like this.

have a look at
http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong[^]

Short example:
SQL
declare @kolom as nvarchar(20)
declare @xpwhere as nvarchar(100)
declare @res as nvarchar(max)
declare @params nvarchar(4000)

set @kolom = 'AdresID'
set @xpwhere = 'AdresID = 3'

declare @sql as nvarchar(255)

select @params = N'@result nvarchar(max) output'
set @sql = 'SELECT @result = ' + @kolom + ' FROM dbo.Adressen WHERE ' + @xpwhere
exec sp_executesql @sql, @params, @result=@res output
print @res
 
Share this answer
 
Comments
Varun Sareen 5-May-10 0:50am    
hey dear tom thanks for a fast reply to my query. But actually my problem is that i m trying to send a table name as parameter to stored procedure and then using it in the query but not been able to succeed. :(
Tom Deketelaere 5-May-10 2:56am    
Yes I know you can't just attach the parameter to the query and run it.
You have to use 'sp_executesql'
Check the link in my answer it's explained there

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900