尝试创建存储过程 [英] Trying to create a stored procedure

问题描述

嘿伙计我需要你的帮助，我正在尝试创建一个存储过程，但我不知道为什么MSQl Management Studio会抱怨这个查询。



错误消息：消息102，级别15，状态1，过程Consolidate_Time_From_THQ，第1行

'Consolidate_Time_From_THQ'附近的语法不正确。

Hey guys i need your help, i am trying to create a stored procedure, but i dont know why MSQl Management Studio is complaining about this query.

Error Msg:Msg 102, Level 15, State 1, Procedure Consolidate_Time_From_THQ, Line 1
Incorrect syntax near 'Consolidate_Time_From_THQ'.

CREATE Procedure Consolidate_Time_From_THQ



DECLARE @start DATE,  @end DATE
DECLARE @tbl TABLE 
([userid] uniqueidentifier
,[userObjname] nvarchar(255)
,[chargedToid] uniqueidentifier
,[chargedToObjtype] tinyint
,[chargeToObjname] nvarchar(255)
,[chargedToProjectid] uniqueidentifier
,[totalBillableSeconds] int
,[totalNonBillableSeconds] int
,[totalSeconds] int
,[effectiveDate] datetime)
INSERT @tbl


SELECT 
dbo.timesheet.userid, 
dbo.THQ_Objects.objname AS userObjname, 
dbo.timesheetDetail.chargedToid, 
THQ_Objects_1.objtype AS chargedToObjtype, 
THQ_Objects_1.objname AS chargeToObjname, 
CASE THQ_Objects_1.objtype 
WHEN 1 THEN dbo.timesheetDetail.chargedToid 
WHEN 2 THEN
(SELECT projectid
FROM dbo.task
WHERE objid = dbo.timesheetDetail.chargedToid) 
ELSE NULL 
END AS chargedToProjectid, 
SUM(dbo.timesheetDetail.totalBillableSeconds) AS totalBillableSeconds, 
SUM(dbo.timesheetDetail.totalNonBillableSeconds) AS totalNonBillableSeconds, 
SUM(dbo.timesheetDetail.totalSeconds) 
AS totalSeconds, MIN(dbo.timesheetDetail.effectiveDate) AS effectiveDate
FROM dbo.timesheetDetail INNER JOIN
dbo.timesheet ON dbo.timesheetDetail.sheetid = dbo.timesheet.objid INNER JOIN
dbo.THQ_Objects ON dbo.timesheet.userid = dbo.THQ_Objects.objid INNER JOIN
dbo.THQ_Objects AS THQ_Objects_1 ON dbo.timesheetDetail.chargedToid = THQ_Objects_1.objid
WHERE (dbo.THQ_Objects.deleted = 0) AND (THQ_Objects_1.deleted = 0)

GROUP BY dbo.timesheetDetail.chargedToid, dbo.timesheet.userid, dbo.THQ_Objects.objname, THQ_Objects_1.objname, THQ_Objects_1.objtype
order by effectiveDate desc

SELECT UserObjname as 'Resource', EffectiveDate, (SELECT objname FROM thq_Objects o WHERE o.objid = result.chargedToProjectid) as 'ProjectName', (SELECT objname FROM thq_Objects o WHERE o.objid = result.chargedToid) as 'Project/Task Name', TotalBillableSeconds, TotalNonBillableSeconds, TotalSeconds
FROM @tbl result
WHERE effectiveDate BETWEEN @start AND @end
Order By UserObjname, EffectiveDate
revert

感谢您的帮助。

Thanks for the help.

推荐答案

如果您有SMS，只需右键单击存储过程并选择新建。你会得到这样的模板：

If you have SMS just right-click Stored Procedures and choose New. You'll get the template like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
CREATE PROCEDURE <procedure_name,> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <datatype_for_param1,> = <default_value_for_param1,>, 
	<@Param2, sysname, @p2> <datatype_for_param2,> = <default_value_for_param2,>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO</create>

对于初学者，您缺少AS关键字。

For starters, you are missing the AS keyword.

这篇关于尝试创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！