Multiple IF statements

I am getting syntax errors on the following SQL. Can anyone help? Thanks.
IF COALESCE(dbo.People.Address1,'') = ''
BEGIN
FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) +
COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
ELSE
IF COALESCE(dbo.People.Address2,'') = ''
BEGIN
FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
ELSE
BEGIN
FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo.People.City,'') +
N', ' + COALESCE(dbo.People.State,'') + N' ' +
COALESCE(dbo.People.ZipCode,'')
END,
DavidThere's no context for the IF statement. It refers to dbo.People.Address1, b
ut with not SELECT
statement. For which row do you want to perform these operations?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:eQ4GrdEGFHA.3336@.TK2MSFTNGP10.phx
.gbl...
>I am getting syntax errors on the following SQL. Can anyone help? Thanks.
> IF COALESCE(dbo.People.Address1,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) + COALESCE(dbo
.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> IF COALESCE(dbo.People.Address2,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo
.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo
.People.Address1,'') +
> Chr(13) + COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State
,'') + N' ' +
> COALESCE(dbo.People.ZipCode,'')
> END,
> David
>|||David C wrote:
> I am getting syntax errors on the following SQL. Can anyone help? Thanks
.
> IF COALESCE(dbo.People.Address1,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) +
> COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N'
'
> + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> IF COALESCE(dbo.People.Address2,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
> COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N'
'
> + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
> COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo.People.City,'')
+
> N', ' + COALESCE(dbo.People.State,'') + N' ' +
> COALESCE(dbo.People.ZipCode,'')
> END,
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
This is in the SELECT clause of a query? In that case use a CASE
instead of the If..Else:
CASE WHEN COALESCE(People.Address1,'') = ''
THEN FullAddress = COALESCE(People.Address2,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
WHEN COALESCE(People.Address2,'') = ''
THEN FullAddress = COALESCE(People.Address1,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
ELSE FullAddress = COALESCE(People.Address1,'') + Chr(13)
+ COALESCE(People.Address2,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
END,
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQho7HIechKqOuFEgEQKg9wCfe3HzCvdQbhvK
e0SY8QfpdGvXj10AoKzV
QM1G41rQNsUIxqDkCYbAxIza
=J+7B
--END PGP SIGNATURE--|||I just wanted the IF statement to refer to a returned field named
FullAddress.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Your code failed but code below worked:
FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
WHEN COALESCE(dbo.People.Address2,'') = ''
THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David wrote:

> FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
> THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> WHEN COALESCE(dbo.People.Address2,'') = ''
> THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> END
or simply
FullAddress =
COALESCE(dbo.People.Address1 + Char(13) + Char(10),'')
+ COALESCE(dbo.People.Address2 + Char(13) + Char(10),'')
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
Dieter|||On Mon, 21 Feb 2005 13:00:35 -0800, David wrote:

>Your code failed but code below worked:
>FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
> THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> WHEN COALESCE(dbo.People.Address2,'') = ''
> THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
>END
Hi David,
You can simplify this:
FullAddress = COALESCE(dbo.People.Address1 + Char(13) + Char(10), '')
+ COALESCE(dbo.People.Address2 + Char(13) + Char(10), '')
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
By the way: if city is NULL, the last line will look like this:
", IL 12345"
If State is NULL, the last line will look like this:
"Smallville, 12345" (note the two spaces)
I'm not sure if that is really what you intend...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I don't think the single line will work because it will always bring
back something from 1st 2 lines and I don't want that. I think your
examples will always bring back Char(13) + Char(10) if either Address1
or Address2 is Null.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||On Mon, 21 Feb 2005 14:41:58 -0800, David wrote:

>I don't think the single line will work because it will always bring
>back something from 1st 2 lines and I don't want that. I think your
>examples will always bring back Char(13) + Char(10) if either Address1
>or Address2 is Null.
Hi David,
No, it won't (unless you have changed your settings to non-ANSI-standard
NULL handling). Did you try it?
There's a big difference between
(a) COALESCE (columnname + char(13) + char(10), '')
and
(b) COALESCE (columnname, '') + char(13) + char(10)
In (a), the CrLf (Carriage Return [char(13)] + Line Feed [char(10)]) will
be concatenated to the column's value first, then the result is checked
against NULL and if it is, it's replaced by an empty string. Since
concatenation of CrLf to a NULL string results in a NULL string, the end
result of (a) will be the empty string if the column holds a NULL.
In (b), the column's value is first checked against NULL and replaced by
the empty string, then CrLf gets added. Concatenation of CrLf to the empty
string will result in a string holding just CrLf.
This being said, I must add that there will be a difference if your data
actually holds rows where Address1 or Address2 is filled with an empty
string. If that's the case, I'd strongly suggest you to change that - you
should represent unknown or missing data in one consistent way, not mix up
various ways. Decide to use either the empty string or NULL if an address
line is missing, then clean up data and introduce either a NOT NULL
constraint or a CHECK (Addres1 <> '') constraint to prevent future entry
of malformed data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple Dynamic Insert Statements in one stored procedure?

Hello, I'm having a little trouble, and need a little direction. I

have a stored procedure with a transaction that creates a contract for

a customer in a Contract table. After the contract record is created, I need

to create the contract items/products with their properties (price,

notes, etc), which belong in a linked table, so that they're created

all within the same transaction (the contract, and the products), so

that if either the contract or any of the items cannot be inserted into

their correct tables, it will rollback and undo the creation of the

contract. Now, I understand transactions and how they work, but my

trouble is that I'm not sure if I can use a dynamic insert

statement to create the contract items/products, within the same

transaction where the contract is created.

I'm new to sp_executesql, which seems to be the recommended method for dynamic sql in a stored proc, but if I understand it correctly, I'd have to call a stored proc for each item i wanted to insert something into the ContractItem table with a dynamic insert statement. Is there any way to have multiple dynamically created insert statements in one stored procedure? Help! I hope I explained my problem well enough. thanks in advance!

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Chris

|||

Chris Howarth wrote:

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

I understand this, I guess my problem really is this: the number of rows I will have to insert into the ContractItem table will vary each time this stored procedure to create a Contract and it's ContractItems is called. So how do I pass that (variable amount of) data into the stored prodedure to create the dynamic insert statements?

Chris Howarth wrote:

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Not really, I'm still sort of new to stored procedures though, if that is any kind of an excuse :) If this would be a better way to do it, then my plan should probably change slightly...

|||

How you choose to do this very much depends on your application's architecture.

If you have a web server or application server that is executing your stored procedures then it would probably make sense to create a single INSERT stored proc (designed to insert one row at a time) per table and then get your web/app server to execute the stored procedure once per row, passing in the parameters as required. Any iteration that needs to take place can then be performed by the web/app server.

You can start a transaction at the connection level, so as long as your web/app server uses the same connection to execute all stored procs that are part of the 'business transaction' then all of the changes will be rolled back should an error be raised.

Does this fit your scenario?

Chris

|||Chris,

This does fit my scenario... a transaction at the connection level sound just like what I need to get this to work properly. Now I just have to go dig up how to do that. Thank you so much for your help!

multiple datatables passed to one report?

Is it possible to pass multiple datatables to one report (within one dataset)?

My stored procedure would have two or more select statements.

Only one resultset is supported.

You could add a parameter to the stored procedure (or write a wrapper). The parameter determines which resultset is returned. You would then define multiple datasets in the RS report and call the stored procedure with different parameter values.

-- Robert

|||

Thanks, I guess I need to research multiple datasets in the report. I imagine there is a custom assembly involved?

|||

Using multiple datasets in a report has nothing to do with custom assemblies.

Multiple datasets just means you need to have multiple data regions (list, table, matrix, chart) in the report to show the data.

-- Robert

Multiple Contain Statements

I need some help with this syntax
Select * FROM PROJECTS
WHERE CONTAINS(Problem_Description, '"Invalid use of null"') And WHERE
CONTAINS(Problem_Status, '"Open"')
Can you have multiple CONTAINS Statements?
I am getting a syntax error with the above code snippet. What am I missing?
Thanks!You have an extra WHERE clause. CONTAINS is a predicate, and you can
logically combine predicates using AND or OR; you only need one WHERE
clause:
WHERE CONTAINS(x, 'abc') AND CONTAINS(y, 'def')
But in your case, I'm wondering if you really want to use full-text search
for your problem status column? Will the status really be more than one
word? You'd probably have much better luck using:
WHERE CONTAINS(Problem_Description, '"Invalid use of null"')
AND Problem_Status = 'Open'
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:95B2EA60-F534-46EA-A4F4-C39A447C5FC9@.microsoft.com...
>I need some help with this syntax
> Select * FROM PROJECTS
> WHERE CONTAINS(Problem_Description, '"Invalid use of null"') And WHERE
> CONTAINS(Problem_Status, '"Open"')
> Can you have multiple CONTAINS Statements?
> I am getting a syntax error with the above code snippet. What am I
> missing?
> Thanks!
>|||Thanks for your help!
"Adam Machanic" wrote:

> You have an extra WHERE clause. CONTAINS is a predicate, and you can
> logically combine predicates using AND or OR; you only need one WHERE
> clause:
> WHERE CONTAINS(x, 'abc') AND CONTAINS(y, 'def')
> But in your case, I'm wondering if you really want to use full-text search
> for your problem status column? Will the status really be more than one
> word? You'd probably have much better luck using:
> WHERE CONTAINS(Problem_Description, '"Invalid use of null"')
> AND Problem_Status = 'Open'
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:95B2EA60-F534-46EA-A4F4-C39A447C5FC9@.microsoft.com...
>
>