总结最近遇到的SQL Server的一些问题

1. sql server 如何输出超长字符串。解决Print不全问题

SELECT @SQL FOR XML PATH(’’)

双击之后可呈现超长字符串

https://blog.csdn.net/qq_33391499/article/details/85799028

2. SqlServer中存储过程中将Exec的执行结果赋值给变量输出

declare @sql nvarchar(2000)
declare @cou int
declare @id varchar(20)
set @id='1'
set @sql='select @count=count(*) from emp where id=@id'
exec sp_executesql @sql, N'@count int out,@id varchar(20)', @cou out
,@id
print @cou

https://blog.csdn.net/hisense20112784/article/details/72884516/

exec (@sql)  ---无法传入传出参数

3. SQL语句拼接时，使用函数quotename给变量加上双引号

SET @unionsql = 'SELECT APP_ID as appId,' + quotename(@tname, '''') +
                        ' AS object,COUNT(distinct BATCH_NB) AS batch#, COUNT(1) AS row#, MIN(ADD_DATE) AS MinDATE, MAX(ADD_DATE) AS MaxDATE FROM '
                        + @tname + ' WHERE APP_ID = ' + quotename(@APP_ID, '''') + ' GROUP BY APP_ID';

用两个单引号给拼接SQL

SET @transactionsql = 'select APP_ID, COUNT(1) ERR from TableName where STATUS=''ERR'' and APP_ID=' +
                        quotename(@APP_ID, '''') + 'group by APP_ID for json path'

4. 产生json数据

'select * from (' + @unionsql + ') t for json path'    产生的json最外面带[];

'select * from (' + @unionsql + ') t without_array_wrapper'  产生的json最外面不带[];

5. 存储字符串

Microsoft SQL Server 2005 中引入了 max 说明符。此说明符增强了 varchar、nvarchar 和 varbinary 数据类型的存储能力。varchar(max)、nvarchar(max) 和 varbinary(max) 统称为大值数据类型。您可以使用大值数据类型来存储最大为 2^31-1 个字节的数据。

DECLARE @unionsql NVARCHAR(MAX) = '';  len(@unionsql) = 0

DECLARE @unionsql NVARCHAR(MAX) ;  //len(@unionsql) != 0

6. 游标的使用

DECLARE cr CURSOR FOR CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
        SELECT DISTINCT (MAPPING_TABLE) AS tname
        FROM TableName
        WHERE charindex('.', MAPPING_TABLE) > 0

OPEN cr
FETCH NEXT FROM cr INTO @tname
WHILE (@@FETCH_STATUS = 0)
  BEGIN
    PRING @tname
    FETCH NEXT FROM cr INTO @tname
  END
CLOSE cr
DEALLOCATE cr