时间戳

首页 > 解决方案 > 传递变量时用户定义的函数确实有效

sql - 传递变量时用户定义的函数确实有效

问题描述

Camel 标量值函数在使用变量时不起作用

declare @primary_supervisor_name varchar(2000)
declare @lastname varchar(2000)
declare @containComma int
declare @position int
declare @result varchar(200)
DECLARE ts_cursor CURSOR FOR 
select primary_supervisor_name from Test
OPEN ts_cursor
FETCH NEXT FROM ts_cursor INTO  @primary_supervisor_name
WHILE @@FETCH_STATUS = 0
BEGIN                      
    set @containComma = CHARINDEX(',', @primary_supervisor_name)
    Begin
    IF (@containComma = 0)                                       
        set @position = CHARINDEX(' ' , @primary_supervisor_name)
        set @lastName = substring(@primary_supervisor_name, @position + 1, len(@primary_supervisor_name))
        set @lastName = LOWER(@lastName)
        print  dbo.CamelCase('test')  -- WORKS and PRINTs "Test"
        print  dbo.CamelCase(@lastName) -- does not do anything..no seeing any print output in console.
    end
    FETCH NEXT FROM ts_cursor INTO @primary_supervisor_name   
END
CLOSE ts_cursor
DEALLOCATE ts_cursor

骆驼功能不适用于@lastName. 使用https://www.sqlservercentral.com/scripts/function-returns-string-in-proper-case-camelcase中的 Camel 函数

标签： sqlsql-servertsql

解决方案

我认为问题可能出在您的程序结构上。你的 BEGIN 和 END 看起来有点奇怪。

WHILE @@FETCH_STATUS = 0
BEGIN                      
    set @containComma = CHARINDEX(',', @primary_supervisor_name)
    Begin
    ...
    IF (@containComma = 0)   
    end
    FETCH NEXT FROM ts_cursor INTO @primary_supervisor_name   
END

我想你会选择下面的代码块

WHILE @@FETCH_STATUS = 0
BEGIN                      
    set @containComma = CHARINDEX(',', @primary_supervisor_name)
    IF (@containComma = 0) BEGIN  -- <--- Begin on this line?
    ...
    end
    FETCH NEXT FROM ts_cursor INTO @primary_supervisor_name   
END

推荐阅读