problem with SQLSTATE

create function
"DBO".sf_GetStateID( @.Abbr char(2))
returns integer
begin
declare @.StateID integer;
set @.Abbr=UPPER(@.Abbr);
if @.Abbr is null
set @.Abbr=''
set @.StateID=53;

select MIN(lngStateID) into StateID from "DBA".States where strAbbr=@.Abbr;

if @.StateID is null
insert into States(strAbbr,strName) values(@.Abbr,@.Abbr)

if SQLSTATE = '00000'

set @.StateID=@.@.IDENTITY
return(@.StateID)
end

In this function, I am getting error at SQLSTATE = '00000'. I saw in books online and it says that SQLSTATE is a keyword. It is not recognizing SQLSTATE as a keyword in the function.
Can you help in that?

2nd Error: It is not accepting the statement
select MIN(lngStateID) into StateID from "DBO".States where strAbbr=@.Abbr;

I don't see any problem in the above statement. Do help in solving these two problems.

Tks
K.The 2 problems is that sqlstate is used for odbc/embedded sql - not for udfs. Also, you are returning a scalar in the udf and not a table (for the 2nd error).sql

Problem with SQL Update when a field is NULL

I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.

i.e. This does not work if something is NULL

UPDATE myTable SET something = @.something
WHERE ID = @.o_ID AND something = @.o_something

Thank You,
Jason

The reason is ANSI SQL NULL is an unknown so comparing NULL to NULL is not NULL but an unknown. Try the link below for work around. Hope this helps.

http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp

|||Will there be a huge performance impact if I use Coalesce() 144 times in a statement?|||

No I think you should try SET ANSI_NULLS OFF instead. Try the link below for details from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9rec.asp

|||It still does not work when I use set ANSI_NULLS OFF|||

I have two option there is a new tool that gives you intelisense in SQL Server Query Tools called SQL Prompt see if it can give you some idea of how to get the info. The other is a link with long code using COALESCE and ISNULL. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Problem with replication

Hi
Using SQL 7.0
I get this error :
[vbcol=seagreen]
when this SP is running :
{call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
Does anyone have an idea ?
Where is @.c1 ? What is in it ?
Thanks !
DonTry checking out the stored proc 'sp_addsynctriggers ' for '@.c1' (which is a
variable) and see what is going on in that area. One note, when SQL says
that an error is 'near' something, it means exactly that; somewhere in the
area of whatever it references (in this case '@.c1') it objects to something.
"Don" wrote:

> Hi
> Using SQL 7.0
> I get this error :
>
> when this SP is running :
> {call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
> N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
> N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
> N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
> Does anyone have an idea ?
> Where is @.c1 ? What is in it ?
> Thanks !
> Don
>

Problem with query

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksAleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>

Problem with query

I have the table persons with fields
lastname - varchar
id - numeric
i want to update last name to null if id and lastname are equal. i
tried the following query, but it not works
update persons set lastname=null where lastname like id;
I really appreciate you if you could help me.
Thanks in AdvanceTry this
update persons set lastname=null
where lastname = convert(varchar(100),id)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||No it doesn't work!
Please give someother solution|||please post DDL, sample data, and an explanation of what you mean by "it
doesn't work".
Why doesn't it work? Are you getting an error message? Are any rows
updated?
See this link. It explains what we need.
http://www.aspfaq.com/etiquette.asp?id=5006
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1148067610.329895.147730@.u72g2000cwu.googlegroups.com...
> No it doesn't work!
> Please give someother solution
>|||DDL
CREATE TABLE [PERSONS] (
[HONORIFIC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIRSTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MIDDLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NICKNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPOUSE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ADDRESS_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CITY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STAFF_REP] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL ,
[UPDATE_DATE] [datetime] NULL ,
[NEXT_DATE] [datetime] NULL ,
[REQ_SALARY] [float] NOT NULL ,
[RELOCATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRAVEL] [float] NULL ,
[PURGEDATE] [datetime] NULL ,
[SSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GNRL_COMMENTS] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PROF_SUMMARY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIRSTAVAILABLE] [datetime] NULL ,
[LASTAVAILABLE] [datetime] NULL ,
[CITIZENSHIP] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[LOCPREF] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FEETO] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FEETERMS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMADDRESS_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COMADDRESS_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COMCITY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMSTATE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMZIP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMCOUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SIC] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASSISTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[REPNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[START_SAL] [float] NULL ,
[END_SAL] [float] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[RESP_DESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEXCODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RACECODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MARITALSTATUS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EEOCODEID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EEOGROUPID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[EEOCODE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EEOGROUP] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EEOCAT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMAILADDR] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STDBILLRATE] [float] NULL ,
[STDPAYRATE] [float] NULL ,
[CURBILLRATE] [float] NULL ,
[CURPAYRATE] [float] NULL ,
[TYPE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLASS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STAFF_NEXT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CATEGORY] [int] NOT NULL ,
[OVERTIMEAFTER] [decimal](11, 4) NULL ,
[OVERTIMERATE] [decimal](11, 4) NULL ,
[CURGROSSMARGIN] [decimal](11, 4) NULL ,
[STDGROSSMARGIN] [decimal](11, 4) NULL ,
[OVERTIMEEXEMPT] [int] NULL ,
[DEDUCTIONS] [int] NULL ,
[PAYTYPE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALUTATION] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RESUMEUPDATED] [datetime] NULL ,
[SICCODEDESC] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ID] [decimal](12, 3) NOT NULL ,
[COMID] [decimal](12, 3) NULL ,
[REPID] [decimal](12, 3) NULL ,
[CONTACTOK] [int] NULL ,
[VERIFIED] [int] NULL ,
[HOMEPRIMARY] [int] NULL ,
[XDEDUCTIONS] [float] NULL ,
[ST_DEDUCTIONS] [int] NULL ,
[ST_XDEDUCTIONS] [float] NULL ,
[SUI] [int] NULL ,
[FEDTAX] [int] NULL ,
[FUTA] [int] NULL ,
[MCARE] [int] NULL ,
[SSEC] [int] NULL ,
[PAYPERIOD] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEDBY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATEDBY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSROWID] [timestamp] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I don't think sample data will be useful. I am not getting any error
messages but the field is not updated.

problem with OUTPUT params in Stored procedure

Hi all!
Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.
As one might guess I would like the result to be 1. What is wrong? I.e, why
wont the result of the SP come back to the caller?

CREATE PROCEDURE test
@.val INTEGER OUT
AS
SELECT @.val = 1
GO

DECLARE @.val INTEGER
EXEC test @.val
SELECT @.valEXEC test @.val OUTPUT

Simon