views:

59

answers:

2

Please help me with this:

This code works fine except for cases in which the control goes inside the IF block of "NOT EXSISTS", then any query run after the execution of this block causes the sql connection to forcibly get closed, although the results from the running of this code block are correct. I can not run other queries after running it.

IF(@Mode='Get')
BEGIN
    IF(@Field='manager')
    BEGIN
        DECLARE @UserUserName NVARCHAR(250)
        DECLARE @UserID AS VARCHAR(50)
        SELECT @UserUserName=CAST(Value AS NVARCHAR(250)) FROM dbo.UserProperties WHERE [Key]=@Key AND Field='manager'
        IF(NOT EXISTS(SELECT * FROM dbo.Users WHERE UserUsername=@UserUserName) OR @UserUserName IS NULL )
        BEGIN
            SELECT  @UserID = dbo.fnGetManagerId(CAST(@Key AS INT)) -- numeric
            SELECT @UserUserName=UserUsername FROM dbo.Users WHERE UserID=@UserID
        END 
        SELECT UserName AS Value FROM users WHERE UserUsername=@UserUserName
    END 
    ELSE
    BEGIN
        SELECT Value FROM dbo.UserProperties WHERE [Key]=@Key AND Field=@Field
    END 
END
+1  A: 

I believe that your IF statement is incorrect. Try:

IF NOT (EXISTS(SELECT * FROM dbo.Users WHERE UserUsername=@UserUserName) OR @UserUserName IS NULL )
sbenderli
Why would it cause the connection to crash though?
Martin Smith
@Martin: I'm not sure.@Puneet: Can you paste the ERRORLOG of the SQL Instance you're working on?
sbenderli
+2  A: 

I seem to remember seeing kb articles about bugs with scalar UDFs in SQL2000 that could cause access violations maybe you've hit one of those? I'd first separate the cast from the UDF parameter list and do it in another line

Replace

SELECT  @UserID = dbo.fnGetManagerId(CAST(@Key AS INT)) -- numeric

With

DECLARE @K int
SET @K = CAST(@Key AS INT)
SELECT  @UserID = dbo.fnGetManagerId(@K)

then if the problem persists comment out a line at a time until you find the culprit.

I'd look in the SQL Server Error Logs. Also you can use SQL Profiler to trace user error messages. A high enough severity error would close the connection automatically.

Martin Smith
Thanks, I think this will occur only in Sql 2000, passing the parameter using cast inline in the parameter list crashes the server. Doing it in a separate line just works fine.
Puneet Dudeja