求助，sql存储过程调用函数，无法执行，只能返回sql语句

[Persistence_x]

USE [DAMP]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_SinopecCrudeBuild]    Script Date: 2018/6/21 11:32:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_SinopecCrudeBuild]
(
  @str  nvarchar(100)
 )
 returns  varchar(200)
as
 begin
 declare @result varchar(200)
 declare @sqlwhere  varchar(200)
 declare @sqlfirst varchar(200)
 set @sqlwhere =' WHERE 1=1 '
 set @sqlfirst = 'SELECT YYPZ,JZJG,BHSJ,HSJ,FBSJ from ZLS_HGYYJG '
 if(dbo.Get_StrArrayStrOfIndex(@str,',',1) is not null)
   set @sqlwhere = @sqlwhere + ' AND YYPZ = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',1) +'''';
 if(dbo.Get_StrArrayStrOfIndex(@str,',',2) is not null)
 begin
   set @sqlwhere = @sqlwhere + ' AND JZJG = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',2) +'''' ;
 end
 
 set @result =  @sqlfirst + @sqlwhere;

 return (@result)
 end

[code=sql]USE [DAMP]
GO

执行存储过程返回的是select 语句，但是没有执行啊，
请求大神帮忙看看，

[shinger126]

这个是函数,不是存储过程.函数内部是不允许执行exec的

[二月十六]

返回的是要执行的字符串，可以接收一下这个字符串在执行

[Persistence_x]

引用 5 楼 sinat_28984567 的回复:

返回的是要执行的字符串，可以接收一下这个字符串在执行

as
 begin 
  declare @sqlwhere  varchar(200)
  declare @sqlfirst varchar(200)
  set @sqlwhere =' WHERE 1=1 '
  set @sqlfirst = 'SELECT YYPZ,JZJG,BHSJ,HSJ,FBSJ from ZLS_HGYYJG '
	 if(dbo.Get_StrArrayStrOfIndex(@str,',',1) is not null)
	   set @sqlwhere = @sqlwhere + ' AND YYPZ = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',1) +'''';
	 if(dbo.Get_StrArrayStrOfIndex(@str,',',2) is not null)
	 begin
	   set @sqlwhere = @sqlwhere + ' AND JZJG = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',2) +'''' ;
	 end
 
  INSERT INTO @RETURNVAL(YYPZ,JZJG,BHSJ,HSJ) + @sqlfirst + @sqlwhere;
 return  
 end

返回表的 ，我怎么拼接这个字符串

[Persistence_x]

引用 5 楼 sinat_28984567 的回复:

返回的是要执行的字符串，可以接收一下这个字符串在执行

看到了， 应该是

 returns @RETURNVAL TABLE(
 YYPZ NVARCHAR(200),
 JZJG NVARCHAR(200),
 HSJ NVARCHAR(200),
 BHSJ NVARCHAR(200)
 )
as
 begin 
  declare @sqlwhere  varchar(200)
  declare @sqlfirst varchar(200)
  set @sqlwhere =' WHERE 1=1 '
  set @sqlfirst = 'SELECT YYPZ,JZJG,BHSJ,HSJ,FBSJ from ZLS_HGYYJG '
	 if(dbo.Get_StrArrayStrOfIndex(@str,',',1) is not null)
	   set @sqlwhere = @sqlwhere + ' AND YYPZ = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',1) +'''';
	 if(dbo.Get_StrArrayStrOfIndex(@str,',',2) is not null)
	 begin
	   set @sqlwhere = @sqlwhere + ' AND JZJG = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',2) +'''' ;
	 end
 
  INSERT INTO @RETURNVAL(YYPZ,JZJG,BHSJ,HSJ)  + @sqlfirst + @sqlwhere;
 return  
 end

[shinger126]

引用 7 楼 xiaohuaidan1988 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:]
返回的是要执行的字符串，可以接收一下这个字符串在执行

看到了， 应该是

 returns @RETURNVAL TABLE(
 YYPZ NVARCHAR(200),
 JZJG NVARCHAR(200),
 HSJ NVARCHAR(200),
 BHSJ NVARCHAR(200)
 )
as
 begin 
  declare @sqlwhere  varchar(200)
  declare @sqlfirst varchar(200)
  set @sqlwhere =' WHERE 1=1 '
  set @sqlfirst = 'SELECT YYPZ,JZJG,BHSJ,HSJ,FBSJ from ZLS_HGYYJG '
	 if(dbo.Get_StrArrayStrOfIndex(@str,',',1) is not null)
	   set @sqlwhere = @sqlwhere + ' AND YYPZ = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',1) +'''';
	 if(dbo.Get_StrArrayStrOfIndex(@str,',',2) is not null)
	 begin
	   set @sqlwhere = @sqlwhere + ' AND JZJG = ' +''''+ dbo.Get_StrArrayStrOfIndex(@str,',',2) +'''' ;
	 end
 
  INSERT INTO @RETURNVAL(YYPZ,JZJG,BHSJ,HSJ)  + @sqlfirst + @sqlwhere;
 return  
 end

[/quote]
你这不会报错么?INSERT INTO @RETURNVAL(YYPZ,JZJG,BHSJ,HSJ) + @sqlfirst + @sqlwhere;这句

[shinger126]

ALTER proc [dbo].[VisitDataProc]
as
declare @str varchar(max)
SELECT @str varchar= [dbo].[fn_SinopecCrudeBuild] ('外购春光原油,423.00');
exec(@str)

这样

[RINK_1]

#9方法不错。

[Persistence_x]

USE [DAMP]
GO
/****** Object:  StoredProcedure [dbo].[VisitDataProc]    Script Date: 2018/6/21 11:38:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[VisitDataProc]
as
SELECT [dbo].[fn_SinopecCrudeBuild] ('外购春光原油,423.00');

[Persistence_x]

[丰云]

return (@result) 。。。。。。。。

明明自己代码写的就是要返回sql语句，
程序按你的要求返回了，你还想怎么样呢？