MS SQL Server MSEQ - IF statement in SQL?

Author

IF statement in SQL?

bakedmike via SQLMonster.com

2005-11-08, 4:11 pm

Can I write an if statement in T-SQL for MSSQL2K or do I have to do it in the
WHERE clause?

Would like to say:

IF field1 = e
insert into db2.accounts from db1.accounts where in db2.employee
IF field2 = c
insert into db2.accounts from db1.accounts where in db2.company

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...r-mseq/200511/1

David Portas

2005-11-08, 4:11 pm

"bakedmike via webservertalk.com" <u15016@uwe> wrote in message
news:570f23fad2491@u
we...
> Can I write an if statement in T-SQL for MSSQL2K or do I have to do it in
> the
> WHERE clause?
>
> Would like to say:
>
> IF field1 = e
> insert into db2.accounts from db1.accounts where in db2.employee
> IF field2 = c
> insert into db2.accounts from db1.accounts where in db2.company
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...r-mseq/200511/1

Yes, there is an IF construct, but your pseudo code doesn't make much sense.
In what table does "field1" exist? How is SQL supposed to know the table if
you don't specify it? Which row or rows in that table is your IF statement
intended to refer to? Most likely you should be using a WHERE clause,
perhaps with an EXISTS subquery, but without a better spec that's just a
guess.

--
David Portas
SQL Server MVP
--

bakedmike via SQLMonster.com

2005-11-09, 1:23 pm

Okay, here's the two statements I wanted to run using an if instead of
seperate statements. What you still don't see is that I have moved several
tables before this using a where clause. So here I want to compare to the
data that I moved so that I can leave out data referencing data I didn't move.

Run 1st:
INSERT INTO db2.dbo.Accounts (List all the fields in the table)
SELECT List of Fields in the Table FROM db1.dbo.Accounts WHERE db1.
dbo.Accounts.ACCT_ID
NOT IN (Limitors) AND (db1.dbo.ACCOUNTS.ACCT_ID IN
(SELECT db2.dbo.EMPLOYEE.EID FROM db2.dbo.EMPLOYEE));

Then:
INSERT INTO db2.dbo.Accounts (List of fields in table)
SELECT List of Fields in Table FROM db1.dbo.Accounts WHERE db1.dbo.
Accounts.ACCT_ID
NOT IN (Limitors) AND (db1.dbo..ACCOUNTS.ACCT_ID IN
(db2.dbo.COMPANY.CID FROM db2.dbo.COMPANY));

What I want to say is

If Acct_Type = E
INSERT INTO db2.dbo.Accounts (List all the fields in the table)
SELECT List of Fields in the Table FROM db1.dbo.Accounts WHERE db1.
dbo.Accounts.ACCT_ID
NOT IN (Limitors) AND (db1.dbo.ACCOUNTS.ACCT_ID IN
(SELECT db2.dbo.EMPLOYEE.EID FROM db2.dbo.EMPLOYEE))
If Acct_Type = C
INSERT INTO db2.dbo.Accounts (List of fields in table)
SELECT List of Fields in Table FROM db1.dbo.Accounts WHERE db1.dbo.
Accounts.ACCT_ID
NOT IN (Limitors) AND (db1.dbo..ACCOUNTS.ACCT_ID IN
(db2.dbo.COMPANY.CID FROM db2.dbo.COMPANY))

David Portas wrote:
>[quoted text clipped - 6 lines]
>
>Yes, there is an IF construct, but your pseudo code doesn't make much sense.
>In what table does "field1" exist? How is SQL supposed to know the table if
>you don't specify it? Which row or rows in that table is your IF statement
>intended to refer to? Most likely you should be using a WHERE clause,
>perhaps with an EXISTS subquery, but without a better spec that's just a
>guess.
>

--
Message posted via http://www.webservertalk.com

Hugo Kornelis

2005-11-10, 8:24 pm

On Wed, 09 Nov 2005 19:20:28 GMT, bakedmike via webservertalk.com wrote:

(snip)
>What I want to say is
>
>If Acct_Type = E
>INSERT INTO db2.dbo.Accounts (List all the fields in the table)
> SELECT List of Fields in the Table FROM db1.dbo.Accounts WHERE db1.
>dbo.Accounts.ACCT_ID
>NOT IN (Limitors) AND (db1.dbo.ACCOUNTS.ACCT_ID IN
>(SELECT db2.dbo.EMPLOYEE.EID FROM db2.dbo.EMPLOYEE))
>If Acct_Type = C
>INSERT INTO db2.dbo.Accounts (List of fields in table)
> SELECT List of Fields in Table FROM db1.dbo.Accounts WHERE db1.dbo.
>Accounts.ACCT_ID
>NOT IN (Limitors) AND (db1.dbo..ACCOUNTS.ACCT_ID IN
>(db2.dbo.COMPANY.CID FROM db2.dbo.COMPANY))

Hi bakedmike,

But what is this "Acct_Type"? Is it a variable? In that case, it should
be prefixed with @, and the rest of the code is just fine.

Or is Acct_Type a column in the Accounts table, and should the decision
to match against either the COMPANY or the EMPLOYEE table me made on a
per-row basis? In that case, try:

INSERT INTO db2.dbo.Accounts (List of fields in table)
SELECT List of Fields in Table
FROM db1.dbo.Accounts
WHERE db1.dbo.Accounts.ACCT_ID NOT IN (Limitors)
-- The line above is bad syntax and won't run.
-- I hope you just made a mistake when copying.
AND ( ( db1.dbo.AcctType = 'C'
AND db1.dbo.ACCOUNTS.ACCT_ID IN
(SELECT db2.dbo.COMPANY.CID
FROM db2.dbo.COMPANY))
OR ( db1.dbo.AcctType = 'E'
AND db1.dbo.ACCOUNTS.ACCT_ID IN
(SELECT db2.dbo.COMPANY.CID
FROM db2.dbo.EMPLOYEE)))

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

bakedmike via SQLMonster.com

2005-11-11, 1:23 pm

Acct_Type is a column.

Is it possible to do an IF statement this way or do you have to write a
WHERE clause?

Does IFs only work with variables in T-SQL?

Thanks for the response and the where clause.

Hugo Kornelis wrote:
>(snip)
>[quoted text clipped - 10 lines]
>
>Hi bakedmike,
>
>But what is this "Acct_Type"? Is it a variable? In that case, it should
>be prefixed with @, and the rest of the code is just fine.
>
>Or is Acct_Type a column in the Accounts table, and should the decision
>to match against either the COMPANY or the EMPLOYEE table me made on a
>per-row basis? In that case, try:
>
>INSERT INTO db2.dbo.Accounts (List of fields in table)
>SELECT List of Fields in Table
>FROM db1.dbo.Accounts
>WHERE db1.dbo.Accounts.ACCT_ID NOT IN (Limitors)
> -- The line above is bad syntax and won't run.
> -- I hope you just made a mistake when copying.
>AND ( ( db1.dbo.AcctType = 'C'
> AND db1.dbo.ACCOUNTS.ACCT_ID IN
> (SELECT db2.dbo.COMPANY.CID
> FROM db2.dbo.COMPANY))
> OR ( db1.dbo.AcctType = 'E'
> AND db1.dbo.ACCOUNTS.ACCT_ID IN
> (SELECT db2.dbo.COMPANY.CID
> FROM db2.dbo.EMPLOYEE)))
>
>Best, Hugo

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...r-mseq/200511/1

Hugo Kornelis

2005-11-11, 8:24 pm

On Fri, 11 Nov 2005 18:45:18 GMT, bakedmike via webservertalk.com wrote:

>Acct_Type is a column.
>
>Is it possible to do an IF statement this way or do you have to write a
>WHERE clause?
>
>Does IFs only work with variables in T-SQL?
>
>Thanks for the response and the where clause.

Hi bakedmike,

Judging by the questions you ask, you are a beginner in the field of
SQL. Unfortunately, it's not really possible to really learn all the ins
and outs of SQL by trial, error and Usenet messages. I recommend that
you start off by reading some books.

A very basic entry-level SQL book (not specific for SQL Server, though):
* SQL Server for Mere Mortals (Hernandez / Viescas).

The following two books are medium-level books that ARE specific for SQL
Server. I haven't read them myself, but I've heard lots of positive
comments on them:
* Advanced Transact-SQL for SQL Server 2000 (Ben-Gan / Moreau)
* The Guru's Guide to Trnasact-SQL (Henderson)

For an IF, the only requirement is that the logical expression evaluates
to one single logical value: True, False, or Unknown. There are
virtually no limitations on how complex the expression is, and it might
involve several subqueries that process thousands of rows. As long as
the end result is no more or less than one single logical value.

If you need to evaluate some logic conditions for each row in the table,
and take action per row dependant on the outcome of the logic, CASE is
the expression to use. You _could_ set up a loop to get the rows one by
one, use an IF, then take approppriate action - but that would be a
terrible waste of resources, result in unwieldy code and limit your
scalability. SQL Server is optimized for set-based processing (i.e. use
one query that processes all rows "at once", instead of looping and
processing them one by one).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links