Parameter in select statement
Hi,
I need to pass a parameter into a stored procedure which values are dynamic always. Executing a variable which having the Select statement.
Following query working fine.
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
DECLARE @CountryId INT
DECLARE @CityId INT
SET @CountryId = 2
SET @CityId = ' AND CityId IN (23,45,85,86)'
SELECT @strQuery= 'SELECT VendorId From Vendors
WHERE CountryId = '+@CountryId+' '+@CityId+'
ORDERBY CreatedDate'
EXEC(@strQuery)
END
I need to execute above in an open select statement instead of executing @strQuery.
Getting error as When trying through following script as "Conversion failed when converting the varchar value ' AND CityId (23,45,85,86)' to data type int"
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDERBY CreatedDate
Please help me to get it working through above select statement.
Thanks
Sharma
Software/Hardware used:
Software/Hardware used:
ASKED:
October 4, 2010 9:31 PM
UPDATED:
October 15, 2010 5:32 AM
Answer Wiki:
Something like this <b>might </b>work:
<pre>...
SET @CityId = <b>'23,45,85,86'</b>
...
SELECT VendorId From Vendors
WHERE CountryId = @CountryId
AND <b>CHARINDEX(','+CAST(CityId as varchar)+',',','+@CityId+',') > 0</b>
ORDERBY CreatedDate</pre>
But it would be inefficient: If you have an index on the CityId, it won't be used.
If the number of possible values in the @CityId list has a known maximum, it would be better to have a function to split the list, and perform the query like this:
<pre>AND CityId in (@city1,@city2,...,@cityn)</pre>
Another option could be creating a function that parses the list and returns a temporary table with the values, which you could join to your Vendors table.
This last option (among others) is discussed in this interesting article:
<a href="http://www.sommarskog.se/arrays-in-sql-2005.html">Arrays and Lists in SQL Server 2005 and Beyond</a>
-----------------
To see all answers submitted to the Answer Wiki: View Answer History.
pass in the values of city as CITY IN (’23′,’45′,’86′,’85′) with values in single quotes