quote:

---

Originally posted by plugsharma

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 Varchar(255)

SET @CountryId = 2
SET @CityId = ' AND CityId IN (23,45,85,86)'
SELECT @strQuery= 'SELECT VendorId From Vendors
WHERE CountryId = '+ Cast ( @CountryId as varchar(30)) +' '+@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

---

quote:

---

Originally posted by jcelko

Why are you using dynamic SQL at all? Why don't you know about the ISO couuntry codes? Surely you did not cram both a country and city into one column -- they are totally different things. This is all you need to do it right:

CREATE PROCEDURE LocateVendorsByLocation
(@in_country_code CHAR(3), --iso-3166
@in_city_name VARCHAR(15))
AS
SELECT vendor_id
FROM Vendors
WHERE country_code = @in_country_code
AND city_name = @in_city_name --I think that this will work only for 1 value and not multiple csv values
ORDER BY creation_date;


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

---