IF NOT EXISTS problem

Hi,

I am migrating a project from SQL to SQL Compact Edition and the following statement keeps failing in the CE project:

Code Snippet

IF NOT EXISTS (SELECT * FROM Court2 WHERE BookingDate = '2007-05-28') INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52) ELSE UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')

It works fine in SQL.

I've done a fair bit of searching for the solution and it appears my syntax is not perfect but I can't see where.

Any suggestions.

Here's the error that CE produces:

Code Snippet

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

Thanks,

Glen.

Hi Glen,

IF.. ELSE is not available in SQL CE, as only a subset of the full T-SQL grammar is supported by SQL CE,

so you will have to do this in code, like:

Get a DataReader with

Code Snippet

SELECT * FROM Court2 WHERE BookingDate = '2007-05-28'

and perform your insert or update depending on the result of this.

For documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

|||

Thank you.

I'm now working on the next issue:

Unfortunately I'm only a hacker and learning the hard way .. Is there somewhere that I can go that lists the differences and workarounds for the CE that I can read.

Cheers.

|||See SQL CE Compact Edition BOL, - for documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

If Is Null in Select Statement

Greetings,
I am getting the following error
"Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IF'."
My SQL statement is:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
dbo.qry1.col5 END
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
Note: qry1 and qry2 are the same query but am join different columns to the
same column in the table
Thanks for the help
KeithSELECT ..., COALESCE(qry1.col5, qry2.col3)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||IF IS NULL doesnt exist in SQL Server. Use ISNULL(Columtocheck, ElseValue),
OR COALESCE(Columntocheck[,ColumnTocheck], ElseValue). If you wanna put an
IF / CAse Expression in your query refer to the BOL and to the syntax of
CASE, example:
CASE Somecolumn
WHEN NULL THEN 'SomeValue'[Or a cloumn]
WHEN ...
...
ELSE 'Something'
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith" <Keith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||If is a Transact SQL Control flow statement, and cannot be used inside of a
SQL Statement. What you want is the SQL Case Expression. (look it up in
Books OnLIne)
as Folows:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
Case When dbo.qry1.col5 Is Null
Then dbo.qry2.col3
Else dbo.qry1.col5 End
FROM dbo.tbl1
LEFT OUTER JOIN dbo.qry2
ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN dbo.qry1
ON dbo.tbl1.col4 = dbo.qry1.col5
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith|||Hi Keith,
The query can be re-written as
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
ISNULL(dbo.qry1.col5, dbo.qry2.col3)
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
best Regards,
Chandra
---
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith

IF in a Select statement

Hi,

What would be the best way to do this, I have a table like so:

JobID Estimate1 Estimate2
 1 20.00 25.00
 2 52.12 47.31
 3 45.00 34.00
 4 60.00 65.00

and I need to create an SQL query that will grab the Sum of the highest estimates, ie (25.00 + 52.12 + 45.00 + 65.00)

I was hoping to do something similar to the SUMIF function in excel:
SELECT SUMIF(Estimate1>=Estimate2, Estimate1, Estimate2) FROM Estimates
but I haven't seen anything simular to that in the documetation.You should be able to use aCASE statement to accomplish this.

Terrisql

If Exists Statement Problem...

Can someone give me a hand with this?

Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.

Todaysstock table updates the allstocks table with this statement,

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks

this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?

ANy help would be appreciated. Thanksif exists (select 1 from allstock a inner join todaysstocks t on a.ticker=t.ticker)
delete a from allstock a inner join todaysstocks t on a.ticker=t.ticker
...|||insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--|||This may be a hog...
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--
This one will use an index (if one exists) on ticker:

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select a.exchange,a.[todays date],a.ticker,a.[opened date], a.[closed date],a.[over/under]
from todaysstocks a left outer join allstocks b on a.ticker=b.ticker
where b.ticker is null

But this will not alter the data that is already in allstocks, rather it will insert 0 rows. The question was:

How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?|||You need to passes to accomplish this. The first statement updates existing rows, and the second statement adds new rows.

--Update existing records (you can eliminate fields that are part of the natural key):
update allstocks
set allstocks.exchange = todaysstocks.exchange,
allstocks.transdate = todaysstocks.[todays date],
allstocks.ticker = todaysstocks.ticker,
allstocks.[opened date] = todaysstocks.[opened date],
allstocks.[closed date] = todaysstocks.[closed date],
allstocks.[over/under] = todaysstocks.[over/under]
from allstocks
inner join todaysstocks on allstocks.keyfields = todaysstocks.keyfields

--Add new records:
insert into allstocks (exchange, transdate, ticker, [opened date], [closed date], [over/under])
select exchange, [todays date], ticker, [opened date], [closed date], [over/under]
from todaysstocks
left outer join allstocks on todaysstocks.keyfields = allstocks.keyfields
where allstocks.keyfields is null

If Exists Statement In Stored Procedure

Hello all!

Newbie question:

There appears to be something wrong with this syntax in SQL Server 2000:

CREATE PROCEDURE spAddNewUser

@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT

AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

I can't get past the last "if exists" statement, without getting a syntax error when checking syntax. I get "ERROR: Incorrect Syntax near ')'.

I'm sure it's a very simple mistake...

Thanks in advance for any help :-)I think the IF statement is expecting some more code.

if i add the code

begin
print 'yes'
end

after your code then i don't get any parse errors.

What extra code do you want to put in, as your stored procedure does not do anything at the mo :confused:|||I don't know if the code you have shown is what you have, but if it is you are missing the brackets around the paramter list.

CREATE PROCEDURE spAddNewUser
(
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
)
AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

Also don't forget to use BEGIN and END if you need to run a block of code when the IF statment is true.|||Try this way:

CREATE PROCEDURE spAddNewUser
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
AS
IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)
select 'exists!'
ELSE
select 'not exists!'|||all of you are correct...I guess it was just waiting for more info (what happens AFTER the IF EXISTS statement).

I just went ahead and completed the code and it was fine.

<blush>

:-)

IF EXISTS statement in my perl program

I am having trouble finishing my query.

This is what I have:

IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})

I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

Any suggestions would be appreciated.

Thanks,
LauraUse a SELECT statement instead of the VALUES clause.

-PatP|||And using a select statement will still insert the values into the table?|||In an INSERT statement, you can use the VALUES clause for a list of constants, or a SELECT clause for a list of expressions. The SELECT can include multiple rows and/or contain UNION operators to create multiple row inserts using just one INSERT statement. The SELECT buys you the ability to use expressions (including function calls), generate multiple rows, etc.

-PatP|||Ok I think I got it. Off the subject, can the IF...ELSE Contain an embedded IF...ELSE? Is it ok to have two inserts with the if section?

For example,

IF NOT EXISTS (Select ApplicationID from Application Where (Application = '$_' ))
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')

ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";

Thanks for your help.
-Laura|||I think what you meant was:IF NOT EXISTS (Select ApplicationID
FROM Application Where (Application = '$_' ))
BEGIN
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')
END
ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";Note the addition of the BEGIN...END (in red) to your code.

-PatP|||CROSS JOIN?

And what's with the double quote on the end?|||Ok, so the cross join isn't the way that I'd approach it, but it would work... Kind of like the way you had to construct joins using the pre-SQL-89 syntax. Ugly, but adequate to the job!

The double quote hanging off of the end is actually because Laura is taking this SQL out of the middle of her Perl code. It isn't really part of the SQL syntax at all.

-PatP|||Really...OK

Laura...start writting stored procedures and execute them instead....

Never did learn Pearl...though we did use it for an Oracle project once...

I gotta find a Rexx interpreter for Windoze....|||Originally, I used a left join but when I tested my query in sql server's enterprise manager it automatically changed it to cross join.

The begin and end worked. Thanks for the help.

-Laura|||I gotta find a Rexx interpreter for Windoze....I've never tried it, but I've heard that Reginald (http://www.borg.com/~jglatt/rexx/win32/rxusrw32.htm) isn't too bad.

-PatP|||Yeah, I am pretty new at SQL Server. Just started learning it last month because I am going to be the database administrator. So, I have not begun using stored procedures yet, but I will.

Thanks,
Laura|||Hold the phone...Enterprise Manager?

Do you mean Query Analyzer?

And Pat....RxSocks....Not that I'll find a practical application for it...(Well maybe I could replace DTS), but I bet I can get it to talk to SQL Server...

Very cool

Thanks|||Enterprise Manager -> Opened the table view to see the records that were stored -> on the top toolbar there is an sql button that I tested my statements. It probably isn't the best way, but I wanted to have quick access to my table design and query results.

I am aware of Query Analyzer but I have not used it much.

-Laurasql

If exists from within a select statement.

I have a simple SQL statement, I need to populate columns from several
tables.
I need to populate some of the columns depending if there is data in a
child table that matched the parent table (a one to many relationship).
How can I put in an "if exists" into a select statement?
I need results that will be
file_a.first, file_a.second, file_b.first (when file_a.third = "y"),
file_b.first (when file_a.fourth = "y") ..
so kinda like :
select file_a.first, file_a.second,
if file_a.third = "y" then select file_b.first from file_b ..
if file_a.fourth= "y" then select file_b.first from file_b ..You can=B4t use specify dynnamic resultssets until you use dynmaic SQL
to display the columns.
http://www.sommarskog.se/dynamic_sql.html
Without dynamic SQL you could display the columns but fill them in
conditionally:
SELECT
OneTable.SomeColumn0
CASE OneTable.SomeColumn0 WHEN 'Y' THEN OtherTable.SomeColumn0 END...
But assuming that fileA is your parent table which attributes from
which row do you want to display ? If there are two rows returned for
one parent row, your query like the above one wouldn=B4t be reliable.
Perhaps it would be better to post your DDL and sample data that our
thoughts would=B4nt bw based on assumptions:
http://www.aspfaq.com/5006
HTH, jens Suessmeyer.sql

If EXISITS.....DROP Table doesn't work

Hi
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
Regards
Steen
Use
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:[vbcol=seagreen]
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:

If EXISITS.....DROP Table doesn't work

Hi
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
--
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
--
Regards
SteenUse
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:
>> Hi
>> Can any of you tell me what I've done wrong in the statement below.
>> The problem is that when I run the whole statement, it won't drop
>> the table if it already exist. If I then just run the first 2-3
>> lines where it looks for the table and then drop it if it exists, it
>> seems to work.
>> It might just be me that haven't got the syntax right, but it
>> puzzles me why it seems to behave differently depending on how much
>> of the statement I run. The way I have noticed the problem, is that
>> I'm using the same code for 2 databases, where I use 2 different
>> columns in one of the tables. In one table I use the columns "navn1'
>> and "navn2" where in the other I use "kontaktnavn1" and
>> "kontaktnavn2". If I then run my code on the first base (and where
>> the temp table doesn't exist already) it works fine and do the job.
>> Then I change the names of those 2 columns to match the names in the
>> second database -run the code, and then I get an "Invalid column
>> name "kontaktnavn1 and "kontaktnavn2. To me that looks like the code
>> it still using the same "version" of the temp table as I created
>> with the first run of the code.
>> When I then just execute the first lines of my code where it checks
>> for the table and drop it if it exist, and then afterwards run the
>> full script, then it works fine with the new names.
>> To me that looks like it wont drop the table if it exist, but I
>> might be wrong?
>> Additionally I've noticed that temp tables are being named slightly
>> different if they are local or global temp tables. In this case I'm
>> using a global temp table and when I run the "SELECT....from
>> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
>> expected. If I use a local temp table it returns the value
>> "#ejd_adm_temp_____________..." which means that I can't find it in
>> INFORMATION_SCHEMA.TABLES because it has a different name.
>> Is this a known issue or is it just me that are missing something on
>> how to use temp tables (...could very well be the case..:-)...).
>>
>> Here's the code that I'm running...
>> --
>> USE TESTDE
>> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_NAME = '##ejd_adm_temp')
>> DROP table ##ejd_adm_temp
>> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
>> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
>> l.opkrbetalmeddel INTO ##ejd_adm_temp
>> FROM lejer l
>> JOIN ejendom e on l.ejendomnr=e.ejendomnr
>> JOIN AdrFunk AF on e.ejendomID=AF.recordID
>> JOIN Adresse A on A.adresseident=AF.adresseident
>> --
>> Regards
>> Steensql

if exisits statement

I want to run an if exists statement to see if some sql retuns true.
i have got a simple statement to work
if exists(select * from imagebank1)
execute things

this works.
what i want to be able to do is test a more complecated statment with contains variables.
so is it possible to do if exists(@.sql)

the @.sql is equal to
set @.sql ='SELECT [KEY],RANK,imageID,imagename, txtdescription,Metkeyword,
metsystamatic,MetSubjectArea,MetType,PictURL, PictFileURL
FROM CONTAINSTABLE(imagebankmeta1,*,'''+@.schParam+''') C
JOIN imagebankmeta1 P
ON P.imageID = C.[KEY]
where rank > 5
'+ @.SchPict +
' ORDER BY RANK DESC'

any help much appreciated
thanksHave you tried @.@.rowcount ?|||No.
I have read about it but have not put it in to any of my procedures.
how could i utilise this function

would i call my seq first and then do the @.@.rowcount to see if no results have been returned then run some other sql ...

the recordset from the procedure is been sent to an ASP page which then displays the results. So if i checked the sql after it had run i would have some how delay the recodset been set to the asp page until it had been passed through a checker of some kind..

sorry but my knowledge of stored procedures is limited.

thanks
john|||The solution really depends on what you are trying to accomplish. Could you explain in greater detail what you are doing - are you checking the existence of rows within the stored procedure or asp - does it matter ?

if else statement problems

Hello to all...

I have an if-else statement that looks for a null array. I know the array is null, because I didn't put anything in it, but my Sql will still try to insert:

if (Line1Array ==null) Response.Write("Array 1 is empty");else for (int i = 0; i < Line1Array.Length; i++) SqlDataSource1.InsertParameters.Clear(); SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text); SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear); SqlDataSource1.Insert();

Does anyone see what is wrong with the statement?

Thanks for your help

Just because you didn't put any items into your array doesn't mean it will evaluate to null. If you created a new instance of the array, then the array will not evaluate to null. Instead, the Length will simply be zero.|||

if (Line1Array ==null)

{ Response.Write("Array 1 is empty");

}else

{ for (int i = 0; i < Line1Array.Length; i++)

{ SqlDataSource1.InsertParameters.Clear(); SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text); SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear); SqlDataSource1.Insert();

}

}

|||

Thanks for shedding light on that... I guess thats why you guys get paid the big bucks! I ended up doing this.. and it works nicely!

if (Line1Array.GetValue(1) ==null) Response.Write("Array 1 is empty");else for (int i = 0; i < Line1Array.Length; i++) SqlDataSource1.InsertParameters.Clear(); SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text);if (OPSLabel !=null) { SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); } SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear);if (OPSLabel !=null) { SqlDataSource1.Insert(); }

|||

I still don't believe that is what you want it to do. Let me fix your indentation, so it reads how it is coded:

if (Line1Array.GetValue(1) ==null)Response.Write("Array 1 is empty");elsefor (int i = 0; i < Line1Array.Length; i++)SqlDataSource1.InsertParameters.Clear();SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text);SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text);SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text);if (OPSLabel !=null){SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text);SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text);}SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text);SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text);SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text);SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString());SqlDataSource1.InsertParameters.Add("Day", strCalDateDay);SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek);SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth);SqlDataSource1.InsertParameters.Add("Year", strCalDateYear);if (OPSLabel !=null){SqlDataSource1.Insert();}

Because your for loop and else clause is not enclosed in braces {}, only the first statement is being done.

In this case if Line1Array is not null, you are clearing the parameters from the SqlDataSource1 once for each element.

Then you add a bunch of parameters (always, and always only once).

Then you do an insert if OPSLabel isn't null.

This begs the questions.. Why are you repeatedly clearing the SqlDataSource parameters in a loop? That's probably not what you meant.

Why are you going through the whole process of clearing, and setting a bunch of values, only to not do anything with them at the end?

(Why check if OPSLabel is null at the end instead of at the beginning?)

|||

You are right.. I have fixed that now. Another problem with the if's and else's -- I need to verify if another array block is empty, but just before the insert, not just skipping by the whole line:

if (Line1Array.GetValue(3) ==null) { Response.Write("<script language='javascript'>alert('null');</script>"); }else {if (Line1Array.GetValue(6) ==null) { Label1.Text = Line1Array.GetValue(3).ToString(); Response.Write("<script language='javascript'>alert('You are missing an entry for Actual Operators on ROW 1');</script>"); }else { SqlDataSource1.InsertParameters.Add("Shift", ShiftDDL.Text); SqlDataSource1.InsertParameters.Add("Line", LinesDDL.Text); SqlDataSource1.InsertParameters.Add("Product", ProductsDDL.Text); SqlDataSource1.InsertParameters.Add("OPProfile", OPSLabel.Text); SqlDataSource1.InsertParameters.Add("OPAsstProfile", AsstLabel.Text); SqlDataSource1.InsertParameters.Add("OPActual", OPActualTextBox.Text); SqlDataSource1.InsertParameters.Add("OPAsstActual", OPAsstActualTextBox.Text); SqlDataSource1.InsertParameters.Add("Notes", NotesTextBox.Text); SqlDataSource1.InsertParameters.Add("Date", Calendar1.SelectedDate.ToString()); SqlDataSource1.InsertParameters.Add("Day", strCalDateDay); SqlDataSource1.InsertParameters.Add("Week", strCalDateWeek); SqlDataSource1.InsertParameters.Add("Month", strCalDateMonth); SqlDataSource1.InsertParameters.Add("Year", strCalDateYear);//SqlDataSource1.Insert(); SqlDataSource1.InsertParameters.Clear(); } }

but this doesn't work! Value(6) is null, but is passes on and tries to insert. I know the javascript works, verified outside of the statement.

Any ideas?

Thanks

|||

I'm afraid I can't answer that one for you. I'm not well versed enough in C# to give you the answer you seek. However, in your response.write, the first less than is being sent out as <, and I'm pretty sure that isn't valid.

sql

IF ELSE statement in WHERE clause

actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.

i have try to use:

if @.IssueUserID = ' ' Select...(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help....

================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID

WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @.DateFrom, 103) AND CONVERT(datetime, @.DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @.IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeIDHi,

A trick I have used in this case would be to code:

...
AND (tbl_Receipt.IssueUserID =
ISNULL(@.IssueUserID, tbl_Receipt.IssueUserID )
)
...

In other words, if @.IssueUserId is not present, you join the column to itself, which gets all the data...

I've done this in stored procedure reports where I can default the passed parameters, e.g. :

create proc myproc (... , @.IssueUserID int = NULL, ...)

HTH,

Bill|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(

so @.IssueUserID is a character data type, and when it's "blank" you don't want it to be considered in the where clause...?

try

AND ( (@.IssueUserID = ' ') OR (@.IssueUserID != ' ' AND table.column = @.IssueUserID))

HTH

Bill|||;) actually i really appreciate your help. the way u teach is working when i try in SQL Query Analyzer. When comes to the MS.NET reporting, the query is not working..so sad. :cool:|||Very confusing here whether you are checking for NULLs, zero-length strings ('') or values with just a single space (' '). Your MS.NET reporting layer may be interpreting things differently than Query Analyzer.

Your code indicates a single space, but that is an unusual value to be submitted by an interface to indicate the absence of data.

This code handles both NULL values and zero-length strings:

AND (nullif(@.IssueUserID, '') is null OR (nullif(@.IssueUserID, '') is not null AND table.column = @.IssueUserID))

If Else Statement in a Update Trigger

I am trying to build an update trigger to check the condition if checkbox is true then add 1 year, else add 3 years. The code works before i added the if checkbox = true.

Original Working code:

IF NOT UPDATE (EDITED)

UPDATE drvisit

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Code I am trying to use with an IF Statement:

IF NOT UPDATE (EDITED)

UPDATE drvisit

IF Checkbox = True

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Else

Set nextvisit = dateadd (yy, 3, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Use a case statement instead of "IF"
This example assumes "checkbox" is a bit field...

IF NOT UPDATE (EDITED)

UPDATE drvisit
SET nextvisit = case when checkbox = 1 then dateadd (yy, 1, lastaudio) else dateadd (yy, 3, lastaudio) end
FROM Apt
WHERE rec# IN (SELECT rec# FROM inserted)

|||Sweet thanks.. If i did want to do and if statement how would I? On a few occasions I tried it and could never get it to work.|||

Glad to be of help.

"IF" is really only for controlling the flow of execution of your SQL script. It can't be used within a single SQL statement (like UPDATE) to conditionally apply a value.

IF ELSE in a select statement

Hello friends
Is there any possible to use a if.. else in a select statement?
for example
select ,field_a
,field_b
, if field_a like 'TEST' then field_c else field_d
from table_abc
I know that this is not working but I need somethink like this...
Can anyone help me please?You have to use a "case expression". You can not use flow control as part of
the select statement.
select ,field_a
,field_b
, case when field_a = 'TEST' then field_c else field_d end as c1
from table_abc
AMB
"Bruno" wrote:

> Hello friends
> Is there any possible to use a if.. else in a select statement?
> for example
> select ,field_a
> ,field_b
> , if field_a like 'TEST' then field_c else field_d
> from table_abc
> I know that this is not working but I need somethink like this...
> Can anyone help me please?
>|||You need a CASE expression...
select ,field_a
,field_b
, CASE
WHEN field_a like 'TEST' then field_c
ELSE field_d
END AS testField
from table_abc
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Bruno" <Bruno@.discussions.microsoft.com> wrote in message
news:489487A4-D2AB-4DEE-87D7-7A5B52F2A821@.microsoft.com...
> Hello friends
> Is there any possible to use a if.. else in a select statement?
> for example
> select ,field_a
> ,field_b
> , if field_a like 'TEST' then field_c else field_d
> from table_abc
> I know that this is not working but I need somethink like this...
> Can anyone help me please?
>|||What you want is CASE. Check out CASE in BooksOnLine for details.
Andrew J. Kelly SQL MVP
"Bruno" <Bruno@.discussions.microsoft.com> wrote in message
news:489487A4-D2AB-4DEE-87D7-7A5B52F2A821@.microsoft.com...
> Hello friends
> Is there any possible to use a if.. else in a select statement?
> for example
> select ,field_a
> ,field_b
> , if field_a like 'TEST' then field_c else field_d
> from table_abc
> I know that this is not working but I need somethink like this...
> Can anyone help me please?
>|||hi
you can use CASE statement instead
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Bruno" wrote:

> Hello friends
> Is there any possible to use a if.. else in a select statement?
> for example
> select ,field_a
> ,field_b
> , if field_a like 'TEST' then field_c else field_d
> from table_abc
> I know that this is not working but I need somethink like this...
> Can anyone help me please?
>|||Thanks to everyone
I appreciate a lot
"Andrew J. Kelly" wrote:

> What you want is CASE. Check out CASE in BooksOnLine for details.
> --
> Andrew J. Kelly SQL MVP
>
> "Bruno" <Bruno@.discussions.microsoft.com> wrote in message
> news:489487A4-D2AB-4DEE-87D7-7A5B52F2A821@.microsoft.com...
>
>

If Else If condition failing

This is only the second stored procedure that I've written, and I'm
having some issues with a conditional statement that I can't figure
out. The statement has a conditional If statement with two Else If's
that checks for a passed parameter's value (an integer I pass when
executing the SP via ASP). Here is basically what I have:
-- @.SET STATUS has 3 valid values:
-- 1 - Don't set status
-- 2 - Set inactive
-- 3 - Set active
CREATE PROCEDURE dbo.sp_SomeProcedure
@.SET_STATUS int
AS
SET NOCOUNT ON
IF @.SET_STATUS = 1
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE
BEGIN
INSERT STATEMENT
END
ELSE IF @.SET_STATUS = 2
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE IF @.SET_STATUS = 3
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
GO
When @.SET_STATUS is set to either 1 or 2, the sequel statements run
fine and the corresponding row gets updated or inserted accordingly.
If however the @.SET_STATUS is passed as 3, the procedure executes fine,
but the update statement is not run.
I've manually plugged in the "exists" condition and the update
statement for this part of the procedure and they fire off correctly
when run in QA. I'm at a loss. I've pretty much determined that it is
failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
why since it passes the syntax check.
Am I missing something here? Thanks in advance!Here's how I recommend the structure:
IF @.SET_STATUS = 1
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 2
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 3
BEGIN
.. do stuff ...
END
There's no need for ELSE, and you should always wrap the result of an IF
statement in BEGIN/END.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||The ELSE IF @.SET_STATUS = 3 is the else of the IF EXISTS() under
@.SET_STATUS = 2
So it's never actually getting to the IF @.SET_STATUS = 3 statement.
Add BEGIN..END around the IF EXISTS() under each ELSE IF
e.g.
ELSE IF @.SET_STATUS = 2
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
ELSE IF @.SET_STATUS = 3
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
tsigler@.gmail.com wrote:
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||Aaron, you're a rock star! It must not have liked the second "else if"
and after updating like you suggested, everything works like a champ.
Thanks!!!|||Thanks Trey ;)|||Depending on what you're doing in the 3 updates, you may be able to combine
these into 1 statement.
> IF @.SET_STATUS = 1 AND NOT EXISTS (SELECT STATEMENT)
> BEGIN
> INSERT STATEMENT
> END
ELSE
> BEGIN
> UPDATE STATEMENT
> END
Explain what the 3 updates do in your procedure and maybe we can suggest a
better way.
In this part:
> ELSE IF @.SET_STATUS = 2 (or 3)
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
I don't think that you need to check IF EXISTS unless you are performing an
action if this is FALSE.
Your update statement should affect 0 rows if it doesn't exist.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>

If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql

if condition with stored procedure

i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like
INSERT INTO table1 if id = @.id or order = @.fdfdfd.

i think it will return no of rows affected with insert statement. am i right?
any help will be appreciated.First of all, i am not clear with your question.

If you want to insert based on some condition, then specify the insert statment inside an if statement.

Regards
Ravi

If Condition In Select Statement...

Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.

SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROM tblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId

Thanks,
Rahul JhaIt might help if you checked for the correct syntax in Books Online...
Use a CASE statement int he SELECT clause:

SELECT tblCustomer.Customer_LegalName,
case tblCustomer.IsNRACustomer
when TRUE then tblCustomer.Customer_PassportNo
else ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)
end AS TAXID,
...

...but you are still going to have to define what "TRUE" is. What datatype is IsNRACustomer?|||Thanks Blindman :-)

IF @paramter <> something

I'm using this statement in my sp to check a paramter value:
IF @.MESSAGE_CODE <> 2
The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value and
it;s for example 1 but it never gets into the preceding block. 1<> 2 ,right?
but it dosen't work.
Any suggestions,
ThanksHow can we reproduce the problem?
If @.MESSAGE_CODE is null, then it is not considered as diff.
Example:
if null != 2 print 'hello!'
else print 'hi!'
AMB
"Ray5531" wrote:

> I'm using this statement in my sp to check a paramter value:
> IF @.MESSAGE_CODE <> 2
> The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value and
> it;s for example 1 but it never gets into the preceding block. 1<> 2 ,righ
t?
> but it dosen't work.
> Any suggestions,
> Thanks
>
>|||Ray said: ,I print the value and it;s for example 1
It's not null:-) I'm printing the paramtere.
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C872E8D0-B347-4FC1-A1CE-D0C43134CF93@.microsoft.com...
> How can we reproduce the problem?
> If @.MESSAGE_CODE is null, then it is not considered as diff.
> Example:
> if null != 2 print 'hello!'
> else print 'hi!'
>
> AMB
> "Ray5531" wrote:
>|||Well, your post is incomplete, It's probably some syntax error, but without
looking at SP Code, it's impossible to guess...
Post the complete code...
"Ray5531" wrote:

> I'm using this statement in my sp to check a paramter value:
> IF @.MESSAGE_CODE <> 2
> The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value and
> it;s for example 1 but it never gets into the preceding block. 1<> 2 ,righ
t?
> but it dosen't work.
> Any suggestions,
> Thanks
>
>|||Ray5531 wrote:
> I'm using this statement in my sp to check a paramter value:
> IF @.MESSAGE_CODE <> 2
> The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value
> and it;s for example 1 but it never gets into the preceding block.
> 1<> 2 ,right? but it dosen't work.
> Any suggestions,
> Thanks
Must be a syntax issue as others have stated:
declare @.n numeric
set @.n = 1
print @.n
if @.n <> 2
print 'not equal'
Make sure you have a Begin / End after the IF block if you are using
more than one statement after the IF (good idea to use them in any
case).
David Gugick
Imceda Software
www.imceda.com|||here is the complete sp code:
CREATE PROCEDURE [dbo].[IF_InsertLog]
@.INTERNAL_FILEID NUMERIC,
@.AFFILIATE_ID NUMERIC=NULL,
@.CLIENT_ID VARCHAR(10)=NULL,
@.LINE_NUMBER NUMERIC,
@.COLUMN_ORDER NUMERIC,
@.MESSAGE_CODE NUMERIC,
@.MESSAGE VARCHAR(500),
@.CREATED_BY_USER VARCHAR(30),
@.C1STATUS_CODE VARCHAR(6)=NULL
AS
SET NOCOUNT ON
DECLARE @.NEW_LOGENTRY VARCHAR(1)
DECLARE @.C4ENROLLMENT_ID NUMERIC(9)
DECLARE @.C4ENROLMENT_WITHERROR_COUNT NUMERIC(9)
SET @.C4ENROLLMENT_ID= (SELECT CAST(ENROLLMENT_ID AS NUMERIC(9)) FROM
dbo.IF_C4TRANSFORM WHERE FILEID=1355 AND
LINE_NUMBER=5)
IF @.MESSAGE_CODE <> 2
IF @.CREATED_BY_USER='C1_DTS'
IF EXISTS (SELECT LOG_ID FROM IF_LOG WHERE
AFFILIATE_ID=@.AFFILIATE_ID AND CLIENT_ID= @.CLIENT_ID AND
COLUMN_ORDER=@.COLUMN_ORDER AND CREATED_BY_USER=@.CREATED_BY_USER )
SET @.NEW_LOGENTRY='N'
ELSE
SET @.NEW_LOGENTRY='Y'
ELSE IF (@.CREATED_BY_USER='C4_DTS')
BEGIN
SELECT @.C4ENROLLMENT_ID=CAST(ENROLLMENT_ID AS NUMERIC(9)) FROM
dbo.IF_C4TRANSFORM WHERE FILEID=@.INTERNAL_FILEID AND
LINE_NUMBER=@.LINE_NUMBER
SELECT @.C4ENROLMENT_WITHERROR_COUNT = COUNT(*) FROM dbo.IF_C4TRANSFORM
T1 INNER JOIN dbo.IF_C4TRANSFORM T2 ON
T1.ENROLLMENT_ID = T2.ENROLLMENT_ID AND
T1.ERROR_STATUS = T2.ERROR_STATUS
IF @.C4ENROLMENT_WITHERROR_COUNT > 0
SET @.NEW_LOGENTRY='N'
ELSE
SET @.NEW_LOGENTRY='Y'
END
ELSE IF (@.CREATED_BY_USER='C4_DTS_HISTORY')
IF EXISTS (SELECT LOG_ID FROM IF_LOG WHERE
AFFILIATE_ID=@.AFFILIATE_ID AND CLIENT_ID= @.CLIENT_ID AND
COLUMN_ORDER=@.COLUMN_ORDER AND CREATED_BY_USER=@.CREATED_BY_USER )
SET @.NEW_LOGENTRY='N'
ELSE
SET @.NEW_LOGENTRY='Y'
ELSE
SET @.NEW_LOGENTRY='U'
ELSE
PRINT 'A Warning has been reported and there is no need for New/OLD
detection'
SET @.NEW_LOGENTRY=@.MESSAGE_CODE
INSERT INTO DBO.IF_LOG (INTERNAL_FILEID,
AFFILIATE_ID,
CLIENT_ID,
LINE_NUMBER,
COLUMN_ORDER,
MESSAGE_CODE,
MESSAGE,
CREATED_BY_USER,
NEW_LOGENTRY,
C1STATUS_CODE,
C4ENROLLMENT_ID)
VALUES(@.INTERNAL_FILEID,
@.AFFILIATE_ID,
@.CLIENT_ID,
@.LINE_NUMBER,
@.COLUMN_ORDER,
@.MESSAGE_CODE,
@.MESSAGE,
@.CREATED_BY_USER,
@.NEW_LOGENTRY,
@.C1STATUS_CODE,
@.C4ENROLLMENT_ID)
GO|||Use BEGIN and END statements in all blocks and see if the problem still
occurs. Also, why have you chosen to use the NUMERIC data type? Why not
using something like an INT instead?
David Gugick
Imceda Software
www.imceda.com|||What's wrong with numeric?
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23$jgDwnUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Use BEGIN and END statements in all blocks and see if the problem still
> occurs. Also, why have you chosen to use the NUMERIC data type? Why not
> using something like an INT instead?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||They store more information and so take up more space. If you do not need
to keep decimal places (very rare with IDs), you're better off with INT, and
I typically use SMALLINT or TINYINT in finite data sets.
Even the smallest NUMERIC declaration takes up one more byte than a full
INT. And the default (18,0) takes more than double (9 bytes).
http://www.aspfaq.com/2503
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ray5531" <RayAll@.microsft.com> wrote in message
news:OfF6x0nUFHA.580@.TK2MSFTNGP15.phx.gbl...
> What's wrong with numeric?|||Apparently there was some problem using INT and thet's what DBA forces us to
do.I don;t know why and I don't care.I've told not to use INT:-) I know
that's funny but you know....
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:O2fC$QoUFHA.3572@.TK2MSFTNGP12.phx.gbl...
> They store more information and so take up more space. If you do not need
> to keep decimal places (very rare with IDs), you're better off with INT,
> and I typically use SMALLINT or TINYINT in finite data sets.
> Even the smallest NUMERIC declaration takes up one more byte than a full
> INT. And the default (18,0) takes more than double (9 bytes).
> http://www.aspfaq.com/2503
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:OfF6x0nUFHA.580@.TK2MSFTNGP15.phx.gbl...
>sql

If / Else Stored Proc -- What Am I doing wrong?

Bellow is the stored procedure. I wanted to do an If / Else statement. I'm getting a syntax error that something is wrong around my Begin / Else statements. If anyone knows what is wrong I would greatly appriciate it.

Thanks in advance as always.

RB

<code>

Create Proc UpdateFundsAndTotals
AS
IF (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPledges.fundID AND FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)
Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
END
ELSE (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPayments.fundID AND FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)
Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPayments = TotalPayments.TotalPayments
END
Else
END

</code>

what you have is pseudo code..the syntax is :

IF (Select somecolumnfrom sometablewhere condition2 <>select anothercolumnfrom anothertablewhere condition2)BeginUpdate sometable1set somecol= somevaluewhere theconditionEndelsebeginUpdate sometablesset somecol= somevaluewhere theconditionendNote that when you are checking for A <> B in the IF statement A and B should have only 1 values each..you cannot have a result set comparing to another result set.

|||

Are you sure you can do <code>if <condition> else <condition></code> ? I think you need an extra "if" block

|||

This isn't valid SQL:

Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
END

That's one issue. You seem to be trying to piggy-back onto the selects in the IF statement. They're separate statements; that doesn't work.

|||I don't 100% understand your IF/ELSE needs, but try something like this:
CREATE PROC UpdateFundsAndTotals
AS
IFEXISTS(SELECT NULLFROM FundsAndTotals INNER JOINTotalPledges ONFundsAndTotals.fundID = TotalPledges.fundID WHERE FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)

BEGIN

UPDATE
FundsAndTotals
SET
FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
FROM
FundsAndTotals
INNER JOIN
TotalPledges ONFundsAndTotals.fundID = TotalPledges.fundID
WHERE
FundsAndTotals.TotalPledges != TotalPledges.TotalPledges

END

ELSE

BEGIN

IFEXISTS(SELECT NULLFROM FundsAndTotals INNER JOINTotalPayments ONFundsAndTotals.fundID = TotalPayments.fundID WHERE FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)

BEGIN

UPDATE
FundsAndTotals
SET
FundsAndTotals.TotalPayments = TotalPayments.TotalPayments
FROM
FundsAndTotals
INNER JOIN
TotalPayments ONFundsAndTotals.fundID = TotalPayments.fundID
WHERE
FundsAndTotals.TotalPayments != TotalPayments.TotalPayments

END

END

|||wow Terri you have tremendous patience..you go to exceptional levels to understand a user's requirements..I always admire you for that.. Good job.|||

Thanks a lot Terri just wanted to post that your suggestion did the trick.

Thanks again.

RB

|||I am glad that helped! It seems to me that you don't really need the IF(EXISTS) parts of that since the WHERE condition in the UPDATEstatement should take care of the filtering.