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.