动态与光标
问题描述:
我在陈述一个动态光标和在包含字符串的集合:动态与光标
DECLARE @DBs nvarchar(4000)
SET @DBs="'aaa','bbb','ccc','ddd'"
DECLARE CompanyDBCursor CURSOR FOR
SELECT Interid
FROM tableName
WHERE interid in (@DBs)
,但它会出现一个错误 - 无效的ColumnName
答
试试这个:
DECLARE @DBs varchar(8000)
SET @DBs='aaa,bbb,ccc,ddd'
DECLARE CompanyDBCursor CURSOR FOR
SELECT Interid
FROM tableName
WHERE interid in (SELECT * FROM dbo.SPLIT(@DBs,','))
以下是表值函数:
CREATE FUNCTION [dbo].[SPLIT] (
@str_in VARCHAR(8000),
@separator VARCHAR(4)
)
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN
DECLARE @Occurrences INT, @Counter INT, @tmpStr VARCHAR(8000)
SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
SET @str_in = @str_in + @separator
SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr = @str_in
WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable VALUES (SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))
SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
IF DATALENGTH(@tmpStr) = 0
BREAK
END
RETURN
END
+0
真正的开箱即用:) – 2010-12-10 17:56:36
请发布您试图从中选择的表的CREATE TABLE语句。 – 2010-12-10 17:15:13
CREATE TABLE [dbo]。[tableName](................,[INTERID] [char](5)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,........ ..) – 2010-12-10 17:19:46
从@DBs =“...开始引用双引号,然后在文本内部使用单引号进行转义 – 2010-12-10 17:56:58