SQL问题整理
1、在函数内对带副作用的运算符 'rand' 的使用无效。
可以创建视图把 rand() 封装进去
1> 创建视图
create view vTest as select rand() as Srand
2> 调用
create function dbo.f_Encryption( @Count int )
returns varchar(100)
as
begin
DECLARE @RANDOMSTR VARCHAR(100), @CHARPOOL VARCHAR(36)
DECLARE @I INTEGER, @counter INTEGER
SET @CHARPOOL = '0123456789abcdefghijklmnopqrstuvwxyz'
SET @I = 1
SET @RANDOMSTR = ''
WHILE @I <= @Count
BEGIN
HERE:
SET @counter = CAST((select Srand from vTest) * 100 / 2.75 AS INTEGER)
IF @counter < 1 GOTO HERE
SET @RANDOMSTR = @RANDOMSTR + SUBSTRING(@CHARPOOL, @counter ,1)
SET @I = @I + 1
END
return @RANDOMSTR
end
1.查询所有的存储过程
select 'drop procedure ['+OBJECT_SCHEMA_NAME(object_id)+'].['+OBJECT_NAME(object_id)+']' FROM sys.procedures;
2、SQL去除回车符，换行符，空格和水平制表符，参考下面语句
REPLACE(REPLACE(REPLACE(REPLACE([fieldName],CHAR(13),''),CHAR(10),''),CHAR(9),''),' ','')
