question

kudhus avatar image
kudhus asked

passing multiple values to single parameter for inline queries

Hi All, I want to execute the below given query without stored procedure.below given query is executing for only one value but for more than one it can not produce the result.Could you please suggest on below script. DECLARE @SQL NVARCHAR(2000) SET @SQL = 'SELECT account FROM invoice WHERE account IN(@AccountList)' EXEC sp_executesql @SQL, N'@AccountList varchar(5000)', @AccountList = '''AN565'',''AT060'''
sql queryinline-table-valued-functionexecute-sql-taskquery-store
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
NeerajTripathi avatar image
NeerajTripathi answered
If you want to manipulate and pass more than one value in one variable of where clause then you have to use logic to split the values. Please follow as below. 1. Create table invoice(Account varchar(100)) Insert invoice Select 'AN565' Union Select 'AT060' 2. Create one function to split the values. http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/ 3. Use below query as your code passing parameters wrongly and also use the split function to split value.. DECLARE @SQL NVARCHAR(2000) SET @SQL = 'SELECT account FROM invoice WHERE account IN(Select splitdata from [fnSplitString](@AccountList,'',''))' EXEC sp_executesql @SQL, N'@AccountList varchar(5000)', @AccountList = 'AN565,AT060' Hope this will help you. Thanks, NeerajTripathi
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.