Dynamic Sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Sql

Author

Topic

ryoka012
Starting Member

20 Posts

Posted - 2013-07-24 : 04:51:16

Hi,

im using dynamic sql i have an error i just would like to get your opinion in where is have an error "Incorrect syntax near the keyword 'like'."

declare @FieldName as varchar(50)
,@SearchItem as varchar(50)
,@PCInventory as varchar(50)
,@sqlTableName as varchar(100)
,@sqlFieldName as varchar(100)
,@sqlSearchItem as varchar(100)
set @FieldName='Department'
set @SearchItem='PCC'

set @sqlTableName='SELECT * FROM PCInventory ' + @FieldName
set @sqlFieldName=@sqlTableName + 'where' + @FieldName
set @sqlSearchItem=@sqlFieldName like '%' + @SearchItem + '%'
exec(@sqlSearchItem)

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 05:02:36

[code]DECLARE @FieldName VARCHAR(50) = 'Department',
@SearchItem VARCHAR(50) = 'PCC',
@PCInventory VARCHAR(50),
@sqlTableName VARCHAR(100),
@sqlFieldName VARCHAR(100),
@sqlSearchItem VARCHAR(100);

-- Prevent SQL Injection
SET @sqlTableName = 'SELECT * FROM PCInventory ';
SET @sqlFieldName = @sqlTableName + 'WHERE ' + QUOTENAME(@FieldName);
SET @sqlSearchItem = @sqlFieldName + ' LIKE ' + QUOTENAME('%' + @SearchItem + '%', '''') + ';';

-- Display the final result
EXEC (@sqlSearchItem)[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ryoka012
Starting Member

20 Posts

Posted - 2013-07-24 : 05:05:15

thanks SwePeso..it works like a charm..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-28 : 11:42:23

Always read this www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources