Problem with Case statement

not sure what's missing here but it's not liking my select with parameter check. The syntax is wrong here but not sure what.

CASE @.BKYChapter

WHEN 7 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK07', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '992', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

WHEN 13 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK13', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '993', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

[

ELSE SET @.Error = 'Chapter not valid'

]

END

CASE is not a control of flow statement. It is an expression. You have to use if...else to perform control of flow.|||thanks much!

Problem with case statement

With the syntax below, why is field1a not "A" if field1 does not
contain "_"

SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
field1)) ELSE 'A' END, field1
FROM [Table1]<chudson007@.hotmail.com> wrote in message
news:1125938525.701908.180610@.f14g2000cwb.googlegr oups.com...
> With the syntax below, why is field1a not "A" if field1 does not
> contain "_"
>
> SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
> field1)) ELSE 'A' END, field1
> FROM [Table1]

See LIKE and "Pattern Matching in Search Conditions" in Books Online - an
underscore is a wildcard for any single character, so you need to escape it
for a literal match:

LIKE '%[_]%'

Another issue is that CASE is an expression, so it can only return a single
data type - as you've written it, it could return either an int or a char,
so you would get a data type conversion error. See "Result Types" under CASE
in Books Online - you'll need to decide on a single return type, or perhaps
CAST the integer to a character type:

SELECT
Field1a = CASE
WHEN (field1 LIKE '%[_]%') THEN cast((charindex('_',field1)) as char(2))
ELSE 'A' END,
field1
FROM [Table1]

Simon|||cheers simon|||(chudson007@.hotmail.com) writes:
> With the syntax below, why is field1a not "A" if field1 does not
> contain "_"
>
> SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
> field1)) ELSE 'A' END, field1
> FROM [Table1]

In SQL _ is a wildcard for exactly one occurrance of one characater. Thus
%_% matches anything but the empty string. Change to %[_]% to get what you
want.

...by the way, CASE is an expression, not a statement, in SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

problem with an xpath parameter to StoredProc

Hey,
I am getting a parse error on this SP expression. I'd assumed this work
work.
error is "Incorrect syntax near the keyword 'exists'."
CREATE PROCEDURE dbo.sp_ListTemplates
(
@.PropXPath varchar(1024),
)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SELECT fileName, docProps, version
FROM Template
WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
END
GO
Chris Harrington
Active Interface, Inc.
http://www.activeinterface.comNever mind - "exist" not "exists"
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Hey,
> I am getting a parse error on this SP expression. I'd assumed this work
> work.
> error is "Incorrect syntax near the keyword 'exists'."
> CREATE PROCEDURE dbo.sp_ListTemplates
> (
> @.PropXPath varchar(1024),
> )
> AS
> BEGIN
> SET XACT_ABORT ON
> SET NOCOUNT ON
> SELECT fileName, docProps, version
> FROM Template
> WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
> END
> GO
> Chris Harrington
> Active Interface, Inc.
> http://www.activeinterface.com
>|||Seems it doesn't work after all. SP compiles but when I pass in an XPath
expression, it returns all records - regardless of the XPath I pass in:
EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
GO
-- returns all records, not just those which match xpath
Obviously I don't fully understand the use of sql:variable() here.
Anyone got a solution for passing a string param which is used as an XPath
expression?
Chris
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Hey,
> I am getting a parse error on this SP expression. I'd assumed this work
> work.
> error is "Incorrect syntax near the keyword 'exists'."
> CREATE PROCEDURE dbo.sp_ListTemplates
> (
> @.PropXPath varchar(1024),
> )
> AS
> BEGIN
> SET XACT_ABORT ON
> SET NOCOUNT ON
> SELECT fileName, docProps, version
> FROM Template
> WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
> END
> GO
> Chris Harrington
> Active Interface, Inc.
> http://www.activeinterface.com
>|||Chris,
The behavior you're experiencing is by design. When you use sql:variable()
the way you do you construct a text node, and therefore the exist method wil
l
always return one since the expression didn't return the empty sequence. The
contents of the variable are in no way interpreted as an XPath expression.
Currently there is no way to parameterize the expression used by the exist
method. You can however construct the whole T-SQL statement in a string vari
able
and run it using sp_executesql.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:uTDXOEKlGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Seems it doesn't work after all. SP compiles but when I pass in an XPath
> expression, it returns all records - regardless of the XPath I pass in:
> EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
> GO
> -- returns all records, not just those which match xpath
> Obviously I don't fully understand the use of sql:variable() here.
> Anyone got a solution for passing a string param which is used as an XPath
> expression?
> Chris
>
> "ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
> news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
>

problem with ALTER TABLE syntax

I finally found a way to "deploy" my local SqlServerExpress (SSE) database to the remote Sql2K server... In VisualWebDeveloper (VWD) I can create the table definition and then save the creation sql script and use that in SSE Express Manager while connected to my remote DB. I am describing this because I'm so surprised no one has had problems with this as thousands of developers, some very unexperienced (as me maybe!), are trying the same situation now that some are offering 2.0 hosting... Well I thought this was a great idea until the Sql2K server is returning error messages on the script VWD created. So please could you help since I'm not that good at complex sql scripting. It seems the error comes from the ALTER TABLE syntax:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table2
(
prID int NOT NULL IDENTITY (1, 1),
DateInserted datetime NOT NULL,
Title nvarchar(100) NOT NULL,
Description nvarchar(MAX) NULL,
CategoryID smallint NOT NULL,
DateLastUpdated datetime NULL,
Price int NOT NULL,
SpecialPrice int NULL,
ImgSuffix nvarchar(5) NULL,
ImgCustomWidth smallint NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
prID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Exactly what error message are you getting?
Tryst
|||

Msg 170... Incorrect syntax near '('.
I've found out if I remove "WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)"
it works...

Problem while trying to login to admin

Server Error in '/' Application.

---

Syntax error converting character string to smalldatetime data type.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Syntax error converting character string to smalldatetime data type.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Syntax error converting character string to smalldatetime data type.]
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +180
 LEInternational.admin.login.btnLogin_Click(Object sender, EventArgs e) in W:\le-international\admin\login.aspx.cs:87
 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

 System.Web.UI.Page.ProcessRequestMain() +1292

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2407; ASP.NET Version:1.1.4322.2407

Try to identify the T-SQL/stored proc that is causing this erros and post it here.

|||

I agree, it would help to see some code... It may be that you have some bad data in your database that won't convert to a smalldatetime. But it's impossible to know if the problem is in the stored procedure or your data without seeing the SP