InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 20:07:00 GMT20http://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspxThanks a lot all!Mon, 05 Nov 2012 17:10:28 GMTqriushttp://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspxNot only is Dwain 1000000% correct about not using dynamic sql for this, your dynamic sql is wide open to sql injection. You are directly executing a string passed in.Mon, 05 Nov 2012 08:30:54 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspx[quote][b]qrius (11/4/2012)[/b][hr]Hi, am trying to pass 'Orange,Guava' argurment to this stored Proc. dbo.TESCOS table has two columns, Fruit varchar(100) and Price int. I keep getting errors >> Msg 207, Level 16, State 1, Line 3Invalid column name 'Guava'.Any help? thanks!USE [Paul]GO/****** Object: StoredProcedure [dbo].[FruitPrice] Script Date: 11/04/2012 23:07:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery12.sql|7|0|C:\Users\Paul\AppData\Local\Temp\~vsD79B.sqlALTER PROC [dbo].[FruitPrice]( @OrderList varchar(500))ASBEGIN SET NOCOUNT ON DECLARE @SQL varchar(400) SET @SQL = 'SELECT Fruit,Price FROM dbo.TESCOS WHERE Fruit IN (' + @OrderList + ')' EXEC(@SQL) END[/quote]You don't need dynamic SQL at all for this:[code="sql"]ALTER PROC [dbo].[FruitPrice]( @OrderList varchar(500))ASBEGIN SET NOCOUNT ON SELECT Fruit,Price FROM dbo.TESCOS WHERE Fruit IN (SELECT Item FROM DelimitedSplit8K(@OrderList, ','))END[/code]Where the DelimitedSplit8K FUNCTION can be found here: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Sun, 04 Nov 2012 17:54:35 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspxThere are no quotes around the names of your fruits so they are being interpreted as column names.if the string you passed in was like this,[code="sql"]set @sql = ''''+'guava'+''''+','+''''+'orange'+''''[/code] it would work fine. That being said, you probably want to search for "string splitter" on here and use one of the prerolled methods for using comma delimited strings.Sun, 04 Nov 2012 17:09:06 GMTlnardozi 61862http://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspxby the way, depending on what you are trying to do there are other ways of passing lists of parameters into a stored procedure. for example xml or table-valued parameters.regardsDavidSun, 04 Nov 2012 16:46:27 GMTDavid Betteridgehttp://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspxYou need to surround the fruits with 'i.e. '''Orange'',''Guava''' [code="sql"]DECLARE @SQL AS VARCHAR(MAX)DECLARE @OrderList AS VARCHAR(MAX) = '''Orange'',''Guava''' SET @SQL = 'SELECT Fruit,Price FROM dbo.TESCOS WHERE Fruit IN (' + @OrderList + ')' SELECT @SQL[/code]Sun, 04 Nov 2012 16:43:01 GMTDavid Betteridgehttp://www.sqlservercentral.com/Forums/Topic1380844-391-1.aspxHi, am trying to pass 'Orange,Guava' argurment to this stored Proc. dbo.TESCOS table has two columns, Fruit varchar(100) and Price int. I keep getting errors >> Msg 207, Level 16, State 1, Line 3Invalid column name 'Guava'.Any help? thanks!USE [Paul]GO/****** Object: StoredProcedure [dbo].[FruitPrice] Script Date: 11/04/2012 23:07:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery12.sql|7|0|C:\Users\Paul\AppData\Local\Temp\~vsD79B.sqlALTER PROC [dbo].[FruitPrice]( @OrderList varchar(500))ASBEGIN SET NOCOUNT ON DECLARE @SQL varchar(400) SET @SQL = 'SELECT Fruit,Price FROM dbo.TESCOS WHERE Fruit IN (' + @OrderList + ')' EXEC(@SQL) ENDSun, 04 Nov 2012 16:35:08 GMTqrius