帮助动态Sql。

问题描述

嘿伙计们，我有点卡住了，周五大脑的严重剂量:)，无论如何我需要创建一个动态的sql语句而且我有点卡住。

Hey Guys, im kinda stuck at the minute, serious dose of friday brain:), Anyway I need to create a dynamic sql statement and im kinda stuck.

Declare  @string VARCHAR(MAX) = 'Test,Test,Test',
@delimiter CHAR(1) = ',',
@Sql NVARCHAR(MAX)

SET @Sql = 'Select DestinationId from dbo.Destination where '

    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

        WHILE @start < LEN(@string) + 1 BEGIN
        
        IF @end = 0 
            SET @end = LEN(@string) + 1
			
			SET @Sql = @Sql + ' DestinationName like ''' + @string + '%' + ''
        
               
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END

PRINT @Sql

我希望结果查询从dbo.Destination中选择DestinationId，其中DestinationName类似于''Test％''

和DestinationName类似''Test％''

和DestinationName类似''测试％''



任何帮助都非常苛刻。



谢谢

I want the resulting query to say Select DestinationId from dbo.Destination where DestinationName like ''Test%''
And DestinationName like ''Test%''
And DestinationName like ''Test%''

Any help is very much apreciated.

Thanks

推荐答案

Declare  @string VARCHAR(MAX)
set @string = 'Test1,Test2,Test3'
declare @delimiter CHAR(1)
set @delimiter = ','
declare @Sql NVARCHAR(MAX)

declare @remstring varchar(MAX)

SET @Sql = 'Select DestinationId from dbo.Destination where '

    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE LEN(@string) > 0 and @end > 0
    BEGIN
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
        if @end > 0  BEGIN
            SET @remstring = SUBSTRING(@string, 1, @end - 1)
        END
        else BEGIN
            SET @remstring = @string
        END

        SET @Sql = @Sql + 'DestinationName like ''' + @remstring + '%' + ''' and '
        set @string = substring(@string, @end + 1, len(@string) )

    END
SET @Sql = SUBSTRING(@Sql, 1, len(@Sql) - 5) -- remove the last and
PRINT @Sql

--First Crate a function
 
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
 
declare @slice varchar(8000)
 
select @idx = 1
if len(@String)<1 or @String is null return
 
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
 
if(len(@slice)>0)
begin
insert into @temptable(Items) values(@slice)
 
end
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
 

--This is the query (Finally you will get the required query in the variable named @Sql)
Declare @string VARCHAR(MAX) = ''Test,Test,Test'',
@delimiter CHAR(1) = '','',
@Sql NVARCHAR(MAX)
declare @parameter nvarchar(max)
create table #splittedValues(QParameter nvarchar(max))
 
INSERT INTO #splittedValues (QParameter)
SELECT items
FROM Split(@string, '','')

select *from #splittedValues

DECLARE splitcursor CURSOR READ_ONLY
FOR SELECT QParameter
FROM #splittedValues
OPEN splitcursor
SET @Sql = ''Select DestinationId from dbo.Destination where ''
FETCH NEXT FROM splitcursor
INTO @parameter
WHILE @@FETCH_STATUS = 0
BEGIN
 
SET @Sql = @Sql + '' DestinationName like '''''' + @parameter + ''%'' + '''''' and ''
FETCH NEXT FROM splitcursor
INTO @parameter
 
END
 
CLOSE splitcursor
DEALLOCATE splitcursor
drop table #splittedValues
SELECT SUBSTRING(@Sql,0,len(@Sql)-3)
set @Sql = SUBSTRING(@Sql,0,len(@Sql)-3)
exec(@Sql) -- Here You can execute

这篇关于帮助动态Sql。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！