I have the following part of a query:
Where id in (1,2,3) And country in('France','Italy','Spain')
I want to declare 2 variables and do it like:
Where id in (idsVaraible) And country in(countriesVriable)
It is more like substituting sql code in sql code to make my long query readable and more useful, is there any way to do this?
I think it's more like eval in java script.
Well if you need to pass these sets in as strings, one way would be dynamic SQL:
DECLARE @ids VARCHAR(32) = '1,2,3';
DECLARE @countries VARCHAR(2000) = 'France,Italy,Spain';
DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ...
WHERE id IN (' + @ids + ') AND country IN ('''
+ REPLACE(@countries, ',',''',''') + ''');';
PRINT @sql;
-- EXEC sp_executesql @sql;
Another way would be table-valued parameters. First create these types in your database:
CREATE TYPE dbo.TVPids AS TABLE(ID INT);
CREATE TYPE dbo.TVPcountries AS TABLE(Country VARCHAR(255));
Now your stored procedure can take these types as input:
CREATE PROCEDURE dbo.whatever
@i dbo.TVPids READONLY,
@c dbo.TVPcountries READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT ... FROM dbo.yourtable AS t
INNER JOIN @i AS i ON i.ID = t.ID
INNER JOIN @c AS c ON c.country = t.country;
END
GO
Now your app can pass these two parameters in as sets (e.g. from a DataTable) instead of building a comma-separated string or handling multiple parameters.
Please try using temp table variables:
DECLARE @tblID as TABLE(ID INT)
DECLARE @tblCountry as TABLE(Country NVARCHAR(50))
INSERT INTO @tblID VALUES (1),(2),(3)
INSERT INTO @tblCountry VALUES ('France'),('Italy'),('Spain')
WHERE id in (select ID from @tblID) And country in(select Country from @tblCountry)
