Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003

    Unanswered: problem with SQLSTATE

    create function
    "DBO".sf_GetStateID( @Abbr char(2))
    returns integer
    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

    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.


  2. #2
    Join Date
    Feb 2002
    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).
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts