CODEWITHSUNDEEP
mysqlsqlsql-server
Fat Monk
Fat Monk

Reputation: 2265

Only run SQL query if condition met

I'm trying to write an 'idiot proof' SQL script that can be run by non-SQL literate users.

My idea is to have a couple of variables defined at the top of the script, then run particular queries based on those variable.

I'm testing on mySQL, but it will eventually run on SQL-Server.

In pseudo-code this is what I'm trying to do:

# Set matchThis to the value to match
SET @matchThis = "matchMe";

# Uncomment (remove the #) one of the two lines below to update or just view
#SET @update = "YES";
SET @update = "NO";

IF @update = "YES" {
    UPDATE myTable SET myColumn = "changed" WHERE matchVal = @matchThis;
} ELSE {
    SELECT * FROM myTable WHERE matchVal = @matchThis;
}

I want to do this entirely in SQL if there is a way.

I've seen guides on using SELECT IF etc but can't figure out how to achieve the above.

Upvotes: 4

Views: 22584

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You are quite optimistic if you want to get a script that will run the same in MySQL and SQL Server. However, for your basic script, you can do this:

-- Set matchThis to the value to match
SET @matchThis = 'matchMe';

-- Uncomment (remove the #) one of the two lines below to update or just view
-- SET @update = 'YES';
SET @update = 'NO';

UPDATE myTable
    SET myColumn = 'changed'
    WHERE matchVal = @matchThis AND @update = 'YES';

SELECT *
FROM myTable
WHERE matchVal = @matchThis AND @update <> 'YES';

This is slightly different from your script. This actually runs the select if @update is 'YES', but it will not return any rows.

Two notes:

  • SQL Server does not use double quotes for string constants.
  • MySQL does not allow if statements, except in stored programs.
  • The standard mechanism for commenting out a line is two hyphens (--)

If you need a script to run in T-SQL, you should develop it using T-SQL.

Upvotes: 1

blubr
blubr

Reputation: 122

This is for MSSQL. I think that you got everything down but the syntax. I hope this helps/works.

DECLARE @matchthis AS VARCHAR(MAX)
DECLARE @update AS VARCHAR(1)

SET @matchthis = 'matchme'
--@update can be Y or N. User changes this here.
SET @update = 'Y'

IF @update = 'Y'
    UPDATE mytable SET myColumn = 'changed' WHERE matchval = @matchthis
ELSE IF @update = 'N'
    SELECT * FROM myTable WHERE matchval = @matchthis

I didn't know whether to make the changed a variable, but if you wanted to make it a variable, follow the same syntax as for @matchthis (declare and set).

If you want to make this really idiot proof, I'd say the best thing to do is make a stored procedure so that users don't see the code, they just have the input box.

Upvotes: 4

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131512

There are two questions here. One is why the IF statement doesn't work - because T-SQL doesn't have braces. The syntax is shown in the documentation.

The important question though is how to pass parameters to the script without having the users modify the script itself. This is done using Script Variables. When a script is executed by using the sqlcmd command, any text of the form $(SomeName) is replaced with command-line parameters or environment variables with the same name.

For example, if you have the following script

USE AdventureWorks2012;
SELECT x.$(ColumnName)
FROM Person.Person x
WHERE c.BusinessEntityID < 5;

This command will run it with FirstName as the column name

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

Upvotes: 1

Related Questions