Parameter in select statement

[gpsharma]

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

[gpl]

See my answer in this question

http://bytes.com/topic/sql-server/answers/893211-how-pass-comma-separated-string-into-query