存储过程,参数是一序列数据,问题?

[kingmax54212008]

对于表:
Jobid JobName
1 New Hire - Job not specified
2 Chief Executive Officer
3 Business Operations Manager
4 Chief Financial Officier
5 Publisher
6 Managing Editor
7 Marketing Manager
8 Public Relations Manager
9 Acquisitions Manager
10 Productions Manager
11 Operations Manager
12 Editor
13 Sales Representative
14 Designer


写一个存储过程SP_GetJobs:

例如: 运行 SP_GetJobs 1,2,3

运行结果是: New Hire - Job not specified,Chief Executive Officer,Business Operations Manager


对于这序列参数,怎么处理?是变参数么?

[SQL77]

CREATE PROC P(@JOBID1 INT,@JOBID2 INT,@JOBID3 INT)
AS 
   DECLARE @STR NVARCHAR(4000)
   SELECT @STR=ISNULL(@STR+' ','')+JOBNAME FROM TB 
   WHERE JOBID IN(@JOBID1 ,@JOBID2 ,@JOBID3)

SELECT @STR

[sdhdy]

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+jobname from tb where jobid in (1,2,3)
print @sql

[7761098]

create procedure SP_GetJobs
@jobids varchar(100),
@result varchar(4000) output
as 
begin
set @result = ''
select @result=@result+JobName  from 表 where charindex(','+@jobids+',', ','+Jobid+',')>0
end

[--小F--]

....

[dawugui]

create table tb(Jobid int,col varchar(50))
insert into tb values(1  ,'New Hire - Job not specified') 
insert into tb values(2  ,'Chief Executive Officer') 
insert into tb values(3  ,'Business Operations Manager') 
insert into tb values(4  ,'Chief Financial Officier') 
insert into tb values(5  ,'Publisher') 
insert into tb values(6  ,'Managing Editor') 
insert into tb values(7  ,'Marketing Manager') 
insert into tb values(8  ,'Public Relations Manager') 
insert into tb values(9  ,'Acquisitions Manager') 
insert into tb values(10 ,'Productions Manager') 
insert into tb values(11 ,'Operations Manager') 
insert into tb values(12 ,'Editor') 
insert into tb values(13 ,'Sales Representative') 
insert into tb values(14 ,'Designer')
go

--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)
/*
result                                                                                                                                                                                                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
New Hire - Job not specified,Chief Executive Officer,Business Operations Manager,Chief Financial Officier,Publisher,Managing Editor,Marketing Manager,Public Relations Manager,Acquisitions Manager,Productions Manager,Operations Manager,Editor,Sales Represen

*/

--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
New Hire - Job not specified,Chief Executive Officer,Business Operations Manager,Chief Financial Officier,Publisher,Managing Editor,Marketing Manager,Public Relations Manager,Acquisitions Manager,Productions Manager,Operations Manager,Editor,Sales Represen

*/
drop table tb 

[xiequan2]

http://topic.csdn.net/u/20091013/15/9f058df7-4d29-47bf-a338-b63fcab2abc0.html?51371

[--小F--]

DECLARE @STR VARCHAR(8000)

SELECT @STR=ISNULL(@STR+',','')+JobName FROM (SELECT DISTINCT JobName FROM tb)AS T

print @STR

[华夏小卒]

if object_id('tb') is not null drop table tb
go 
create table tb(Jobid int, JobName varchar(50))
insert tb select
1, 'New Hire - Job not specified'  union all select
2, 'Chief Executive Officer' union all select
3, 'Business Operations Manager' union all select
4, 'Chief Financial Officier' union all select
5, 'Publisher' union all select
6, 'Managing Editor' union all select
7, 'Marketing Manager' union all select
8, 'Public Relations Manager' union all select
9, 'Acquisitions Manager 'union all select
10,'Productions Manager 'union all select
11, 'Operations Manager 'union all select
12, 'Editor 'union all select
13, 'Sales Representative 'union all select
14, 'Designer '

if object_id('SP_GetJobs') is not null drop proc SP_GetJobs
go 

create proc SP_GetJobs
@s varchar(10)
as

	declare @str varchar(200)

	select @str=isnull(@str+',','')+JobName from tb 
	where charindex(','+ltrim(Jobid)+',',','+@s+',')>0
	
	select @str
go 


exec SP_GetJobs '1,2,3'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
New Hire - Job not specified,Chief Executive Officer,Business Operations Manager

(1 行受影响)

[taoistong]

create table tb(Jobid int,col varchar(50))
insert into tb values(1  ,'New Hire - Job not specified') 
insert into tb values(2  ,'Chief Executive Officer') 
insert into tb values(3  ,'Business Operations Manager') 
insert into tb values(4  ,'Chief Financial Officier') 
insert into tb values(5  ,'Publisher') 
insert into tb values(6  ,'Managing Editor') 
insert into tb values(7  ,'Marketing Manager') 
insert into tb values(8  ,'Public Relations Manager') 
insert into tb values(9  ,'Acquisitions Manager') 
insert into tb values(10 ,'Productions Manager') 
insert into tb values(11 ,'Operations Manager') 
insert into tb values(12 ,'Editor') 
insert into tb values(13 ,'Sales Representative') 
insert into tb values(14 ,'Designer')
go




create proc taoistong
as 

declare @taoistong varchar(100) 
set @taoistong=''
select @taoistong=@taoistong +','+col from tb 
select @taoistong


exec taoistong 

[--小F--]

create table tb(Jobid int,col varchar(50))
insert into tb values(1  ,'New Hire - Job not specified') 
insert into tb values(2  ,'Chief Executive Officer') 
insert into tb values(3  ,'Business Operations Manager') 
insert into tb values(4  ,'Chief Financial Officier') 
insert into tb values(5  ,'Publisher') 
insert into tb values(6  ,'Managing Editor') 
insert into tb values(7  ,'Marketing Manager') 
insert into tb values(8  ,'Public Relations Manager') 
insert into tb values(9  ,'Acquisitions Manager') 
insert into tb values(10 ,'Productions Manager') 
insert into tb values(11 ,'Operations Manager') 
insert into tb values(12 ,'Editor') 
insert into tb values(13 ,'Sales Representative') 
insert into tb values(14 ,'Designer')
go

DECLARE @STR VARCHAR(8000)

SELECT @STR=ISNULL(@STR+',','')+col FROM (SELECT DISTINCT col FROM tb)AS T

print @STR

drop table tb
/*
Acquisitions Manager,Business Operations Manager,Chief Executive Officer,Chief Financial Officier,Designer,Editor,Managing Editor,Marketing Manager,New Hire - Job not specified,Operations Manager,Productions Manager,Public Relations Manager,Publisher,Sales Representative
*/

[kingmax54212008]

如果这个存储过程参数运行,期望把所有参数做为字符, 如六楼的参数为 @jobids varchar(100).

事实上,SP_GetJobs 1,2,3 会包如下错误:

为过程或函数 SP_GetJobs指定的参数太多。

[kingmax54212008]

如果这个存储过程参数运行,期望把所有参数做为字符, 如六楼的参数为 @jobids varchar(100).

事实上,SP_GetJobs 1,2,3 会包如下错误:

为过程或函数 SP_GetJobs指定的参数太多。

[kingmax54212008]

jobid 是int 类型,不是字符类型.

[Jerry_黄]

up

[kingmax54212008]

to #4楼,

从题目要求: Write a storedProcedure that gets a list of jobid and returns a list of the matching job names.

应该属于可变参数吧?

运行时,可能是 SP_GetJobs 1,2,3 也有可能是 SP_GetJobs 2,3 , 或 SP_GetJobs 12 , 甚至更多参数.

[kingmax54212008]

SP_GetJobs '1,2'
与

SP_GetJobs 1,2

是有区别的.

为过程或函数 SP_GetJobs指定的参数太多。


对于后者,应该属于可变参数吧? SQL 存储过程有可变参数么?

[SQL77]

[Quote=引用 18 楼 kingmax54212008 的回复:]
to #4楼,

从题目要求: Write a storedProcedure that gets a list of jobid and returns a list of the matching job names.

应该属于可变参数吧?

运行时,可能是 SP_GetJobs 1,2,3 也有可能是 SP_GetJobs 2,3 , 或 SP_GetJobs 12 , 甚至更多参数.
[/Quote]
那你把ID连接作为一个参数吧

[kingmax54212008]

to #11楼,

你的方法有问题.
你运行一下, 这个用例: SP_GetJobs '12,17,15,16,18'

[SQL77]

[Quote=引用 21 楼 kingmax54212008 的回复:]
to #11楼,

你的方法有问题.
你运行一下, 这个用例: SP_GetJobs '12,17,15,16,18'
[/Quote]
长度弄长点就行了,他才定义了10

[kingmax54212008]

To 楼上,

对于#11楼的,参数varchar变成varchar(100)就行了.


另外,确实用可变参数怎么处理, 比如确实要的是: SP_GetJobs 12,17,15,16,18 而不是 SP_GetJobs '12,17,15,16,18'

多谢!