如何在Sql Server 2008中检查值或如何调试存储过程 [英] How Do I Check The Values Or How To Debug The Stored Procedure In Sql Server 2008
问题描述
我有一个存储过程：
USE [XDMLite]
GO
/ ******对象：StoredProcedure [dbo ]。[usp_getDeviceDefinition]脚本日期：08/07/2014 23:43:15 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- 通过调试器提交的批处理：SQLQuery67.sql | 7 | 0 | C：\ Users \c5038671 \ AppData \\ \\ local \Temp \〜vs2722.sql
ALTER PROCEDURE [dbo]。[usp_getDeviceDefinition]
（
@deviceInfoId int
）
AS
SET NOCOUNT ON
SELECT'Settings'AS Section，ddsref.DeviceDefinitionID，ddsref.DeviceDefinitionSettingID，
ddsref.DeviceDefinitionCategoryID，dds.SettingName，dds.SettingDescription，
dds.IsInitialDeviceSetting，sdt.DefaultValue，sdt.MaximumLength，sdt.RegularExpression，
sdt .MinimumValue，sdt.MaximumValue，sdt.EnumValue，ddsv.DeviceDefinitionSettingValueID，
ddsv.HasBeenExportedOnce，ddsv.DeviceDefinitionSettingDefaultValue，dt.DataTypeName，
NULL AS DeviceCommunication，NULL AS UserAuthenticationType ，NULL AS SoftwareVersion，
NULL AS DeviceType，NULL AS模型，NULL AS制造商，NULL AS DeviceDefinitionCategoryType，
NULL AS DeviceDefinitionCategoryName，NULL AS DeviceDefinitionCategoryDescription，
NULL AS CreatedDate，dds.IsReadOnly，dds.SettingLabel，dds.DisplayOrder
FROM DeviceDefinitionSettingXref AS ddsref
INNER JOIN DeviceDefinitionSetting AS dds ON
dds.DeviceDefinitionSettingID = ddsref.DeviceDefinitionSettingID
INNER JOIN DeviceDefinitionSettingValues as ddsv ON
ddsv.DeviceDefinitionSettingXrefID = ddsref.DeviceDefinitionSettingXrefID AND
ddsv.DeviceDefinitionSettingId = dds.DeviceDefinitionSettingID AND
ddsv.DeviceInfoId=@deviceInfoId
INNER JOIN SettingDataType AS sdt ON
sdt.DeviceDefinitionSettingId = dds.DeviceDefinitionSettingID
INNER JOIN DataType AS dt ON dt.DataTypeID = sdt.DataTypeID
INNER JOIN DeviceDefinition AS dd ON
dd.DeviceDefinitionID = ddsref.DeviceDefinitionID
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND di.di_Manufacturer = dd.Manufacturer
WHERE （di.di_DeviceInfoId = @DeviceInfoId AND DeviceDefinitionCategoryID not in（'7E792E2F-B488-4C9F-A811-9E12501EA6A9'，'896FC890-A386-4D40-8051-1D0F3120F1C0'）
AND dd.DeviceDefinitionID = di .di_DeviceDefinitionId）
UNION
选择'类别'AS部分，ddsXref.DeviceDefinitionID，NULL AS DeviceDefinitionSettingID，
ddc.DeviceDefinitionCategoryID，NULL AS SettingName ，NULL AS SettingDescription，
NULL AS IsInitialDeviceSetting，NULL AS DefaultValue，NULL AS MaximumLength，
NULL AS RegularExpression，NULL AS MinimumValue，NULL AS MaximumValue，NULL AS EnumValue，
NULL as DeviceDefinitionSettingValueID，NULL为HasBeenExportedOnce，
NULL为DeviceDefinitionSettingDefaultValue，NULL AS DataTypeName，NULL AS DeviceCommunication，
NULL AS UserAuthenticationType，NULL AS SoftwareVersion，NULL AS DeviceType，NULL AS Model，
NULL AS制造商，ddc.DeviceDefinitionCategoryType，ddc.DeviceDefinitionCategoryName，
ddc.DeviceDefinitionCategoryDescription，NULL AS CreatedDate，NULL AS IsReadOnly，NULL AS SettingLabel，
NULL AS DisplayOrder
FROM DeviceDefinitionCategory AS ddc
INNER JOIN DeviceDefinitionSettingXref as ddsXref ON
ddsXref.DeviceDefinitionCategoryID = ddc.DeviceDefinitionCategoryID
INNER JOIN DeviceDefinition AS dd ON
dd。 DeviceDefinitionID = ddsXref.DeviceDefinitionID
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND
di.di_Manufacturer = dd.Manufacturer
WHERE（di.di_DeviceInfoId = @deviceInfoId AND ddc.DeviceDefinitionCategoryID not in（'7E792E2F-B488-4C9F-A811-9E12501EA6A9' ，'896FC890-A386-4D40-8051-1D0F3120F1C0'）
AND dd.DeviceDefinitionID = di.di_DevicedefinitionId）
UNION
SELECT'DeviceDefinition 'AS Section，dd.DeviceDefinitionID，NULL AS DeviceDefinitionSettingID，
NULL AS DeviceDefinitionCategoryID，NULL AS SettingName，NULL AS SettingDescription，
NULL AS IsInitialDeviceSetting，NULL AS DefaultValue，NULL AS MaximumLength，
NULL AS RegularExpression，NULL AS MinimumValue，NULL AS MaximumValue，NULL AS EnumValue，
NULL为DeviceDefinitionSettingValueID，NULL为HasBeenExportedOnce，
NULL as DeviceDefinitionSettingDefaultValue，NULL AS DataTypeName，dd.DeviceCommunication，
NULL AS DeviceDefinitionCategoryType，NULL AS DeviceDefinitionCategoryName，
NULL AS DeviceDefinitionCategoryDescription，dd.CreatedDate，NULL AS IsReadOnly，
NULL AS SettingLabel，NULL AS DisplayOrder
来自DeviceDefinition AS dd
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND
di.di_Manufacturer = dd.Manufacturer
WHERE（di.di_DeviceInfoId = @deviceInfoId
和dd.DeviceDefinitionID = di.di_DevicedefinitionId）
返回
这里有以下三个主要表格：
DeviceDefinition
DeviceInfo
DeviceDefinitionSettingXref
现在，这个存储过程返回与Device相关的信息。
问题是我在DeviceDefinition表中更新了DeviceDefinitionId，
数据也存在于DeviceDefinitionSettingXref和DeviceInfo表中。
但是，当我执行存储过程时，它没有给我所需的结果
我想进行快速调试，看看在执行此过程时有哪些值。我该怎么做.?
I have a stored procedure:
USE [XDMLite]
GO
/****** Object: StoredProcedure [dbo].[usp_getDeviceDefinition] Script Date: 08/07/2014 23:43:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery67.sql|7|0|C:\Users\c5038671\AppData\Local\Temp\~vs2722.sql
ALTER PROCEDURE [dbo].[usp_getDeviceDefinition]
(
@deviceInfoId int
)
AS
SET NOCOUNT ON
SELECT 'Settings' AS Section, ddsref.DeviceDefinitionID, ddsref.DeviceDefinitionSettingID,
ddsref.DeviceDefinitionCategoryID, dds.SettingName,dds.SettingDescription,
dds.IsInitialDeviceSetting, sdt.DefaultValue, sdt.MaximumLength, sdt.RegularExpression,
sdt.MinimumValue, sdt.MaximumValue,sdt.EnumValue,ddsv.DeviceDefinitionSettingValueID,
ddsv.HasBeenExportedOnce,ddsv.DeviceDefinitionSettingDefaultValue, dt.DataTypeName,
NULL AS DeviceCommunication, NULL AS UserAuthenticationType, NULL AS SoftwareVersion,
NULL AS DeviceType, NULL AS Model, NULL AS Manufacturer, NULL AS DeviceDefinitionCategoryType,
NULL AS DeviceDefinitionCategoryName, NULL AS DeviceDefinitionCategoryDescription,
NULL AS CreatedDate,dds.IsReadOnly,dds.SettingLabel,dds.DisplayOrder
FROM DeviceDefinitionSettingXref AS ddsref
INNER JOIN DeviceDefinitionSetting AS dds ON
dds.DeviceDefinitionSettingID = ddsref.DeviceDefinitionSettingID
INNER JOIN DeviceDefinitionSettingValues as ddsv ON
ddsv.DeviceDefinitionSettingXrefID=ddsref.DeviceDefinitionSettingXrefID AND
ddsv.DeviceDefinitionSettingId=dds.DeviceDefinitionSettingID AND
ddsv.DeviceInfoId=@deviceInfoId
INNER JOIN SettingDataType AS sdt ON
sdt.DeviceDefinitionSettingId = dds.DeviceDefinitionSettingID
INNER JOIN DataType AS dt ON dt.DataTypeID = sdt.DataTypeID
INNER JOIN DeviceDefinition AS dd ON
dd.DeviceDefinitionID = ddsref.DeviceDefinitionID
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND di.di_Manufacturer = dd.Manufacturer
WHERE (di.di_DeviceInfoId = @deviceInfoId AND DeviceDefinitionCategoryID not in('7E792E2F-B488-4C9F-A811-9E12501EA6A9','896FC890-A386-4D40-8051-1D0F3120F1C0')
AND dd.DeviceDefinitionID = di.di_DeviceDefinitionId)
UNION
SELECT 'Categories' AS Section, ddsXref.DeviceDefinitionID, NULL AS DeviceDefinitionSettingID,
ddc.DeviceDefinitionCategoryID, NULL AS SettingName, NULL AS SettingDescription,
NULL AS IsInitialDeviceSetting, NULL AS DefaultValue, NULL AS MaximumLength,
NULL AS RegularExpression, NULL AS MinimumValue, NULL AS MaximumValue, NULL AS EnumValue,
NULL as DeviceDefinitionSettingValueID,NULL as HasBeenExportedOnce,
NULL as DeviceDefinitionSettingDefaultValue,NULL AS DataTypeName, NULL AS DeviceCommunication,
NULL AS UserAuthenticationType, NULL AS SoftwareVersion, NULL AS DeviceType, NULL AS Model,
NULL AS Manufacturer, ddc.DeviceDefinitionCategoryType,ddc.DeviceDefinitionCategoryName,
ddc.DeviceDefinitionCategoryDescription, NULL AS CreatedDate,NULL AS IsReadOnly,NULL AS SettingLabel,
NULL AS DisplayOrder
FROM DeviceDefinitionCategory AS ddc
INNER JOIN DeviceDefinitionSettingXref as ddsXref ON
ddsXref.DeviceDefinitionCategoryID=ddc.DeviceDefinitionCategoryID
INNER JOIN DeviceDefinition AS dd ON
dd.DeviceDefinitionID = ddsXref.DeviceDefinitionID
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND
di.di_Manufacturer = dd.Manufacturer
WHERE (di.di_DeviceInfoId = @deviceInfoId AND ddc.DeviceDefinitionCategoryID not in('7E792E2F-B488-4C9F-A811-9E12501EA6A9','896FC890-A386-4D40-8051-1D0F3120F1C0')
AND dd.DeviceDefinitionID = di.di_DevicedefinitionId)
UNION
SELECT 'DeviceDefinition' AS Section, dd.DeviceDefinitionID, NULL AS DeviceDefinitionSettingID,
NULL AS DeviceDefinitionCategoryID, NULL AS SettingName, NULL AS SettingDescription,
NULL AS IsInitialDeviceSetting, NULL AS DefaultValue, NULL AS MaximumLength,
NULL AS RegularExpression, NULL AS MinimumValue, NULL AS MaximumValue, NULL AS EnumValue,
NULL as DeviceDefinitionSettingValueID,NULL as HasBeenExportedOnce,
NULL as DeviceDefinitionSettingDefaultValue, NULL AS DataTypeName, dd.DeviceCommunication,
dd.UserAuthenticationType,dd.SoftwareVersion, dd.DeviceType, dd.Model, dd.Manufacturer,
NULL AS DeviceDefinitionCategoryType, NULL AS DeviceDefinitionCategoryName,
NULL AS DeviceDefinitionCategoryDescription, dd.CreatedDate,NULL AS IsReadOnly,
NULL AS SettingLabel,NULL AS DisplayOrder
FROM DeviceDefinition AS dd
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND
di.di_Manufacturer = dd.Manufacturer
WHERE (di.di_DeviceInfoId = @deviceInfoId
and dd.DeviceDefinitionID = di.di_DevicedefinitionId)
RETURN
Here, there are following three major tables:
DeviceDefinition
DeviceInfo
DeviceDefinitionSettingXref
Now, this stored procedure returns the information associated with Device.
Problem is when i have updated the DeviceDefinitionId in DeviceDefinition table,
the data is also present in DeviceDefinitionSettingXref and in DeviceInfo table.
But, when i execute the stored procedure it does not give me the desired result
I wanted to do a quick debugging to see what values are there while this procedure is executed. How can i do that.?
推荐答案
您可以使用PRINT命令将信息发送到Management Studio中的消息窗口
You can use the PRINT command to send info to the message window in Management Studio
PRINT 'Test'
或
or
PRINT @deviceInfoId
您好，
首先尽可能删除select语句中的列数。
考虑它没有部分。然后考虑没有工会的第一部分。
例如
Hello ,
First of all remove the no of columns in select statement as far as possible.
Consider It in no of parts..Then consider without union the first part.
for example
SELECT 'Settings' AS Section, ddsref.DeviceDefinitionID, ddsref.DeviceDefinitionSettingID,
ddsref.DeviceDefinitionCategoryID, dds.SettingName,dds.SettingDescription,
dds.IsInitialDeviceSetting, sdt.DefaultValue, sdt.MaximumLength, sdt.RegularExpression,
sdt.MinimumValue, sdt.MaximumValue,sdt.EnumValue,ddsv.DeviceDefinitionSettingValueID,
ddsv.HasBeenExportedOnce,ddsv.DeviceDefinitionSettingDefaultValue, dt.DataTypeName,
NULL AS DeviceCommunication, NULL AS UserAuthenticationType, NULL AS SoftwareVersion,
NULL AS DeviceType, NULL AS Model, NULL AS Manufacturer, NULL AS DeviceDefinitionCategoryType,
NULL AS DeviceDefinitionCategoryName, NULL AS DeviceDefinitionCategoryDescription,
NULL AS CreatedDate,dds.IsReadOnly,dds.SettingLabel,dds.DisplayOrder
FROM DeviceDefinitionSettingXref AS ddsref
INNER JOIN DeviceDefinitionSetting AS dds ON
dds.DeviceDefinitionSettingID = ddsref.DeviceDefinitionSettingID
INNER JOIN DeviceDefinitionSettingValues as ddsv ON
ddsv.DeviceDefinitionSettingXrefID=ddsref.DeviceDefinitionSettingXrefID AND
ddsv.DeviceDefinitionSettingId=dds.DeviceDefinitionSettingID AND
ddsv.DeviceInfoId=@deviceInfoId
INNER JOIN SettingDataType AS sdt ON
sdt.DeviceDefinitionSettingId = dds.DeviceDefinitionSettingID
INNER JOIN DataType AS dt ON dt.DataTypeID = sdt.DataTypeID
INNER JOIN DeviceDefinition AS dd ON
dd.DeviceDefinitionID = ddsref.DeviceDefinitionID
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND di.di_Manufacturer = dd.Manufacturer
WHERE (di.di_DeviceInfoId = @deviceInfoId AND DeviceDefinitionCategoryID not in('7E792E2F-B488-4C9F-A811-9E12501EA6A9','896FC890-A386-4D40-8051-1D0F3120F1C0')
AND dd.DeviceDefinitionID = di.di_DeviceDefinitionId)
然后考虑第二部分。
then consider second part .
SELECT 'Categories' AS Section, ddsXref.DeviceDefinitionID, NULL AS DeviceDefinitionSettingID,
ddc.DeviceDefinitionCategoryID, NULL AS SettingName, NULL AS SettingDescription,
NULL AS IsInitialDeviceSetting, NULL AS DefaultValue, NULL AS MaximumLength,
NULL AS RegularExpression, NULL AS MinimumValue, NULL AS MaximumValue, NULL AS EnumValue,
NULL as DeviceDefinitionSettingValueID,NULL as HasBeenExportedOnce,
NULL as DeviceDefinitionSettingDefaultValue,NULL AS DataTypeName, NULL AS DeviceCommunication,
NULL AS UserAuthenticationType, NULL AS SoftwareVersion, NULL AS DeviceType, NULL AS Model,
NULL AS Manufacturer, ddc.DeviceDefinitionCategoryType,ddc.DeviceDefinitionCategoryName,
ddc.DeviceDefinitionCategoryDescription, NULL AS CreatedDate,NULL AS IsReadOnly,NULL AS SettingLabel,
NULL AS DisplayOrder
FROM DeviceDefinitionCategory AS ddc
INNER JOIN DeviceDefinitionSettingXref as ddsXref ON
ddsXref.DeviceDefinitionCategoryID=ddc.DeviceDefinitionCategoryID
INNER JOIN DeviceDefinition AS dd ON
dd.DeviceDefinitionID = ddsXref.DeviceDefinitionID
INNER JOIN DeviceInfo AS di ON
di.di_DeviceType = dd.DeviceType AND
di.di_Model = dd.Model AND
di.di_Manufacturer = dd.Manufacturer
WHERE (di.di_DeviceInfoId = @deviceInfoId AND ddc.DeviceDefinitionCategoryID not in('7E792E2F-B488-4C9F-A811-9E12501EA6A9','896FC890-A386-4D40-8051-1D0F3120F1C0')
AND dd.DeviceDefinitionID = di.di_DevicedefinitionId)
然后考虑第3部分。尝试结合第一部分和第二部分。然后是第3部分。这可能会让你更接近你想要的输出
如果没有获得，那么尝试使用较少数量的表来加入。
。在调试时保持耐心。 />
快乐编码： - ）
then consider 3rd part. Try to combine the first part & second part. then 3rd part .this may get you closer to your desired output
If Not Getting then try the join with less number of tables .
.keep patience while debugging.
Happy Coding :-)
这篇关于如何在Sql Server 2008中检查值或如何调试存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
