如何使用SQL查询加入StoredProcedure [英] How to Join StoredProcedure with SQL Query

问题描述

来自aspx页面

From aspx page

sqlcmd = new SqlCommand("select * from [RptAuditCrystal] where EditedBy='" + Session["userid"].ToString() + "' order by " + Request.QueryString["order"].ToString(), sqlcon);
                da = new SqlDataAdapter(sqlcmd);
                dt3.Clear();
                da.Fill(dt3);
                sqlcmd.Dispose();
            

            sqlcmd = new SqlCommand("rpt_closeaudituploaddata";, sqlcon);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.AddWithValue("@AuditPlanId", Request.QueryString["Id"].ToString());
            sqlcmd.Parameters.AddWithValue("@EditedBy", Session["userid"].ToString());
            da = new SqlDataAdapter(sqlcmd);
            dt4.Clear();
            da.Fill(dt4);
            sqlcmd.Parameters.Clear();
            sqlcmd.Dispose();

来自SQL存储过程 -

From SQL Stored Procedure -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_closeaudituploaddata] 
	@AuditPlanId varchar(30)
	,@EditedBy varchar(20)
	
AS

BEGIN TRY
	SET NOCOUNT ON;
	
	delete from RptTemplateData where EditedBy=@EditedBy
	
	Declare @QuestionID varchar(20)
	Declare @TemplateId varchar(20)
	Declare @sql varchar(max)
	Declare @coldata varchar(2000)
	Declare @colname varchar(2000)
	
	DECLARE @PKgetLevelID CURSOR
	SET @PKgetLevelID = CURSOR FOR select distinct TA.QuestionID,MT.TemplateId from TrnsAuditorUploadedData TA left join MstrUploadTemplate MT on MT.UploadId=TA.UploadId where TA.IsActive='Y' and AuditPlanId=@AuditPlanId order by TA.QuestionID
	OPEN @PKgetLevelID
	FETCH NEXT
	FROM @PKgetLevelID INTO @QuestionID,@TemplateId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		set @coldata = (select dbo.GetReportTemplate(@TemplateId))
		set @colname = (select dbo.GetReportTemplateColumns(@TemplateId))
		
		set @sql = 'INSERT INTO [dbo].[RptTemplateData] ([QuestionID],[QuestionDesc]
					,[RCol1],[RCol2],[RCol3],[RCol4],[RCol5],[RCol6],[RCol7]
					,[RColName1],[RColName2],[RColName3],[RColName4],[RColName5],[RColName6],[RColName7],[EditedBy]
					,[AuditorRM],[AuditorRMOn],[AuditorRMBy],[AuditeeRMSR],[AuditeeRMSROn],[AuditeeRMSRBy],[AuditeeRMCP],[AuditeeRMCPOn],[AuditeeRMCPBy]) 
					select MQ.QuestionID,MQ.QuestionDesc,' + @coldata + ',' + @colname + ', ''' + @EditedBy + '''
					,[AuditorRM],AuditorRMOn=CONVERT(varchar,CAST([AuditorRMOn] as datetime2(0)),109),[AuditorRMBy]
					,[AuditeeRMSR],AuditeeRMSROn=CONVERT(varchar,CAST([AuditeeRMSROn] as datetime2(0)),109),[AuditeeRMSRBy]
					,[AuditeeRMCP],AuditeeRMCPOn=CONVERT(varchar,CAST([AuditeeRMCPOn] as datetime2(0)),109),[AuditeeRMCPBy] 
					from TrnsAuditorUploadedData TA left join MstrQuestions MQ on MQ.QuestionId=TA.QuestionId 
					where AuditPlanId=''' + @AuditPlanId + ''' and TA.QuestionID=''' + @QuestionID + ''' '
		
		exec (@sql)
			
	FETCH NEXT
	FROM @PKgetLevelID INTO @QuestionID,@TemplateId
	END
	CLOSE @PKgetLevelID
	DEALLOCATE @PKgetLevelID
	
	select * from RptTemplateData where EditedBy=@EditedBy
	
END TRY

BEGIN CATCH
	
	DECLARE @errmsg NVARCHAR(4000)
	DECLARE @errseverity INT
	DECLARE @errstate INT
	
	SELECT @errmsg = ERROR_MESSAGE(), @errseverity = ERROR_SEVERITY(), @errstate = ERROR_STATE()

	RAISERROR(@errmsg, @errseverity, @errstate)

END CATCH

如何在一个DataTable中加入dt3结果和dt4结果​​？

How I can join "dt3" result and "dt4" result in one single DataTable?

推荐答案

在查询中包含文本框是危险的。您需要了解 Sql Injection [ ^ ]。br />


如果你想用sp加入查询结果，只需将查询移到sp。

Having text boxes in the query is dangerous. You need to read about Sql Injection[^].

If you want to join the results of the query with the sp, just move the query into the sp.

这篇关于如何使用SQL查询加入StoredProcedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！