我想利用存储过程,实现参数化,如下:
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" 各项做成存储过程的参数,应该如何实现呢,请各位指点,谢谢!
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" 各项做成存储过程的参数,应该如何实现呢,请各位指点,谢谢!
如果是用存储过程。就把你要的参数设成变量。create sp ....
(
@ip varchar(20)
)
asexec ('sp_addlinkedserver @server=''abc'',@provider=''sqloledb'',@srvproduct=''abc'',@datasrc='''+@ip+'''' )
/****** Object: StoredProcedure [dbo].[Registry.Select] Script Date: 05/11/2009 17:54:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOalter PROCEDURE [dbo].[test]
@topnum varchar(2)
@ordername varchar(100)
as--exec ('sp_addlinkedserver
exec ('select top +@topnum+ * from xxx order by @ordername desc')为什么我执行后出错呢显示:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
加号附近有错误呀,@TOPNUM是INT型的怎么加呀,exec ('select top @topnum * from xxx order by @ordername desc')
exec 'sp_addlinkedserver @server=''abc'',@provider=''sqloledb'',@srvproduct=''abc'',@datasrc='''+@ip+''''
@topnum nvarchar(2),
@tjname nvarchar(10),
@tjtext nvarchar(10)
ASdeclare @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 不录入的话,那么 就不做条件查询,但实际情况却实现不了,请指点一下,谢谢!