将查询转换为storedprocedure [英] Convert query to 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

推荐答案

找到Google搜索：



对此链接上的问题的逐步回答可能会有所帮助：将查询转换为存储过程 [ ^ ]

请点击以下链接：

创建存储过程 [ ^ ]

存储过程（数据库引擎） [ ^ ]



尝试自己转换！这很简单。

Follow below links:
Create a Stored Procedure[^]
Stored Procedures (Database Engine)[^]

Try to convert it by yourself! It's pretty easy.

这篇关于将查询转换为storedprocedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！