Use where with variables

glaucomorandini

Is there any way to set constant variables within the where. I wanted to use a fixed value on all commands. For example, Code = 1. In another database I would use Code = 2. This value 1 and 2 would be fixed for each project, there is some way to change all, without having to change all where.

sam.blackburn

The WHERE clause in SQL Data Compare can call functions, which can execute arbitrary SQL. In your situation, perhaps you could extract part of the WHERE clause into a function, which can be defined differently on each database:

CREATE FUNCTION dbo.ShouldIncludeRow(@id INT) RETURNS BIT AS
BEGIN
DECLARE @Code INT = 1
...
END

SQL Data Compare could then be configured to include rows WHERE dbo.ShouldIncludeRow(id) = 1

Would this work for you?

glaucomorandini

Sorry for being a bit amateur yet, where do I enter this function?

sam.blackburn

You'd need to create the function in both databases, i.e. run that SQL in SSMS. I don't really understand enough about your setup to know whether that's something you'd want to do, though!

glaucomorandini

Thanks a lot, man, it worked.