ALTER PROCEDURE [dbo].[queryexaminee]
@topnum nvarchar(2),
@tjname nvarchar(10),
@tjtext nvarchar(10)
AS declare @sqlstring nvarchar(100) IF @tjname IS NULL
select @sqlstring = N'select top '+ @topnum + ' * from xxx'
else
select @sqlstring = N'select top '+ @topnum + ' * from xxx where '+ @tjname + ' = '''+ @tjtext+ '''' exec(@sqlstring) 我想做到如果tjname 不录入的话,那么 就不做条件查询,但实际情况却实现不了,请指点一下,谢谢!执行时显示内容如下:
Msg 201, Level 16, State 4, Procedure queryexaminee, Line 0
Procedure or function 'queryexaminee' expects parameter '@tjtext', which was not supplied.
@topnum nvarchar(2),
@tjname nvarchar(10),
@tjtext nvarchar(10)
AS declare @sqlstring nvarchar(100) IF @tjname IS NULL
select @sqlstring = N'select top '+ @topnum + ' * from xxx'
else
select @sqlstring = N'select top '+ @topnum + ' * from xxx where '+ @tjname + ' = '''+ @tjtext+ '''' exec(@sqlstring) 我想做到如果tjname 不录入的话,那么 就不做条件查询,但实际情况却实现不了,请指点一下,谢谢!执行时显示内容如下:
Msg 201, Level 16, State 4, Procedure queryexaminee, Line 0
Procedure or function 'queryexaminee' expects parameter '@tjtext', which was not supplied.
@topnum nvarchar(2),
@tjname nvarchar(10)=null,
@tjtext nvarchar(10)
AS declare @sqlstring nvarchar(100) IF @tjname IS NULL or len(@tjname)=0
select @sqlstring = N'select top '+ @topnum + ' * from xxx'
else
select @sqlstring = N'select top '+ @topnum + ' * from xxx where '+ @tjname + ' = '''+ @tjtext+ '''' exec(@sqlstring)
exec sp_addlinkedserver @server='abc',@provider='sqloledb',@srvproduct='abc',@datasrc='192.168.1.2'
GO exec sp_addlinkedsrvlogin @rmtsrvname='abc',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='sa'
GO select top 100 * from ABC.TEST.DBO.TEXT where name='xxx' order by id desc
GO exec sp_droplinkedsrvlogin 'abc' ,'sa'
GO exec sp_dropserver 'abc'
GO
我想将上述sql中的 "192.168.1.2" 、"100" , "id" , "name" 和 "id" 各项做成存储过程的参数,应该如何实现呢,请各位指点
问题已解决,谢谢各位
GO
/****** Object: StoredProcedure [dbo].[FNT_Query] Script Date: 05/12/2009 16:04:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOalter PROCEDURE [dbo].[FNT_Query]
@topnum nvarchar(2),
@tjname nvarchar(10),
@tjtext nvarchar(10),
@outputflag nvarchar(1)ASdeclare @sqlstring nvarchar(300)
declare @outputname nvarchar(200) if @outputflag='0'
select @outputname = ' * '
else
select @outputname = ' fttype,samecity,fromaccnum,fromaccnum,frommppsmobile,toaccnum,amount,status,returncode,lastcalltime '
IF @tjname IS NULL --or len(@tjname)=0 select @sqlstring = N'select top '+ @topnum + @outputname + 'from xxx order by id desc'
else
select @sqlstring = N'select top '+ @topnum + @outputname + 'from xxx where '+ @tjname + ' = '''+ @tjtext+ ''' order by id desc'
exec(@sqlstring)