Click here to Skip to main content
15,882,388 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

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
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'

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