Parameter in select statement

[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

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

[rch1231]

Hello,

What you need to do is get rid of the variables.

SELECT VendorId From Vendors
WHERE CountryId in (223, 245, 285, 286)
ORDERBY CreatedDate'

[plugsharma]

Hello,

What you need to do is get rid of the variables.

SELECT VendorId From Vendors
WHERE CountryId in (223, 245, 285, 286)
ORDERBY CreatedDate'

Hi,

Im using a SP as follows:

CREATE PROCEDURE spGetVendor
@CountryId INT,
@CityId NVARCHAR(50)
AS

SET NOCOUNT ON;
SELECT VendorId From Vendors
WHERE CountryId = @CountryId + ' ' +@CityId
ORDER BY CreatedDate
GO

When execute above sp as
Exec dbo.GetActivityReportIz1 '51', ' AND CityId IN (23,45,85,86)'

getting error "Conversion failed when converting the varchar value ' AND CityId (23,45,85,86)' to data type int"

[glennt]

The problem is that you have declared @CityId as an integer but you are trying to set it to a string value: SET @CityId = ' AND CityId IN (23,45,85,86)'
Anything in single quotes is considered to be a string value and unless it can be converted to an integer, which this cannot be because there are characters other than numbers, you will get an error. Change the variable @CityId to a VARCHAR or equivalent then give it a try.

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