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

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'

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"

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.