问题描述:

DECLARE @CityId AS VARCHAR(20) = NULL

DECLARE @CityList AS VARCHAR(20) = '1, 2, 3, 4, 5';

IF (@CityId IS NULL)

SET @CityId = @CityList;

SELECT *

FROM City

WHERE CityID IN (@CityId)

I have a stored procedure that lists all cities. But If parameter is set, then it should display only specific information about that city. CityID in City Table is bigint. When CityId is left null, it gives error saying 'Error converting data type varchar to bigint.'

Note: If I construct following query, everything seems OK.

SELECT * FROM City WHERE CityID IN (1, 2, 3, 4, 5)

but if I go ahead with the following query, it gives error.

SELECT * FROM City WHERE CityID IN ('1, 2, 3, 4, 5')

I guess I should be constructing int array in this case but I don't know how to do that.

Any suggestions?

网友答案:

You can use dynamic SQL

exec('SELECT * FROM City WHERE CityID IN (' + @CityId + ')')
网友答案:

Try this:

IF (@CityId IS NULL)
    SET @CityId = ',' + REPLACE(@CityList, ' ', '') + ','; 
ELSE
    SET @CityId = ',' + @CityId + ',';

SELECT * 
FROM City 
WHERE charindex(',' + CAST(CityID as nvarchar(20)) + ',', @CityId) > 0
网友答案:
DECLARE @CityId AS VARCHAR(20) = NULL
DECLARE @CityList AS VARCHAR(20) = '1, 2, 3, 4, 5';

IF (@CityId IS NULL)
    SET @CityId = @CityList;

DECLARE @SQL NVARCHAR(MAX);        

 SET @SQL = N'        
 SELECT *
FROM City
 where  
  1 = 1  
  AND Cityid in ('[email protected]+')    
  ';        


 SELECT @SQL = @SQL + N' ';        


 EXEC Sp_executeSQL         
   @SQL
网友答案:

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `mystoredprocedure`$$
CREATE PROCEDURE `mystoredprocedure`(IN city_id AS VARCHAR(20))
BEGIN
SET @CityId = city_id; 
SET @where_condition = "";
IF (@CityId IS NULL)  THEN 
SET @where_condition  = CONCAT(@where_condition,"1, 2, 3, 4, 5"); 
ELSE
SET @where_condition  = @CityId;
END IF;
SET @query = CONCAT("SELECT * FROM City WHERE CityID IN (",@where_condition,")");
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Call Statement

CALL `mystoredprocedure`(NULL);

OR

CALL `mystoredprocedure`('1,2,3');
相关阅读:
Top