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.
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 ' + @FieldNameset @sqlFieldName=@sqlTableName + 'where' + @FieldNameset @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 InjectionSET @sqlTableName = 'SELECT * FROM PCInventory ';SET @sqlFieldName = @sqlTableName + 'WHERE ' + QUOTENAME(@FieldName);SET @sqlSearchItem = @sqlFieldName + ' LIKE ' + QUOTENAME('%' + @SearchItem + '%', '''') + ';';-- Display the final resultEXEC (@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.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|