本文介绍了将查询转换为storedprocedure的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将此查询更改为存储过程?我需要一个相同的结果集
how to change this query in to stored procedure ? I need a same result set
DECLARE @DBName AS VARCHAR(100)
DECLARE @TotalUserTables INT
DECLARE @TotalStoredProcedures INT
DECLARE @TotalViews INT
CREATE TABLE #tblDBResults1
(
DatabaseName VARCHAR(100)
,Item VARCHAR(50)
,Total_count INT
)
DECLARE @SQL VARCHAR(1000)
/* basic select statement */
DECLARE @SQLWithDB VARCHAR(1000)
/* select statement with USE db added */
SET @TotalUserTables = 0
SET @TotalStoredProcedures = 0
SET @TotalViews = 0
SET @SQL = 'SELECT DB_NAME(),
[type]
,total_count = COUNT(*)
FROM (SELECT type = CASE WHEN type = ''U'' THEN ''User tables''
WHEN type = ''V'' THEN ''Views''
WHEN type = ''P'' THEN ''Stored procs''
WHEN type = ''FN'' THEN ''Functions''
END
FROM sys.objects
WHERE type IN (''P'', ''U'', ''V'',''FN'')
) s
GROUP BY type;'
DECLARE DatabaseResultsetCursor CURSOR
FOR
SELECT name
FROM sys.databases
WHERE Database_id > 4
AND sys.databases.state = 0
/* exclude system and offline databases */
OPEN DatabaseResultsetCursor
FETCH NEXT FROM DatabaseResultsetCursor INTO @DBName
WHILE @@fetch_status = 0
BEGIN
SET @SQLWithDB = 'USE [' + @DBName + '];' + CHAR(10) + CHAR(13)
+ @SQL
INSERT #tblDBResults1
EXEC (@SQLWithDB)
FETCH NEXT FROM DatabaseResultsetCursor INTO @DBName
END
CLOSE DatabaseResultsetCursor
DEALLOCATE DatabaseResultsetCursor
SELECT DatabaseName,
MAX(CASE WHEN Item = 'User tables' THEN Total_count ELSE '0' END) UserTables,
MAX(CASE WHEN Item = 'Stored procs' THEN Total_count ELSE '0' END) StoredProcedure,
MAX(CASE WHEN Item = 'Functions' THEN Total_count ELSE '0' END) Functions,
MAX(CASE WHEN Item = 'Views' THEN Total_count ELSE '0' END) Views
FROM #tblDBResults1
GROUP BY DatabaseName
ORDER BY DatabaseName
DROP TABLE #tblDBResults1
我尝试过:
实际上我手边不知道我是SQL的初学者
What I have tried:
Actually I have no idea in my hand I am a beginner of SQL
推荐答案
这篇关于将查询转换为storedprocedure的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!