如何在我的MS-SQL存储过程中使用union [英] How to use union in my MS-SQL stored procedure

问题描述

今天我开始知道如何使用UNION来连接表。感谢CHill60。如何在我的存储过程中使用该联合？我会在这里清楚地解释我的问题。我正在写一个手机信号塔注册申请。我们将给予该人一些金额，如15000.他是第三方注册人，如电池公司和网站所有者之间的调解员。公司将从业主处获取租赁网站。那个调解员将像租赁和所有人一样进行工作。为此，他将收取3000这样的费用。有些网站可以注册，有些则无法注册。计算该调解员的帐户副本，例如他花了多少金额，以及他对他所收费的网站收取多少费用，以及我必须展示的平衡。为此，我写了一些实现的商店程序。首先，我写了一个商店程序来获得结果。但没有来，所以我写了7个商店的程序。 
 1表示该人的期初余额，1表示登记，1表示损失（如果无法登记，他将承担费用），1表示开户登记计数，1表示开户损坏网站计数，1表示我们给予的金额那个人和多少。具有开始和结束日期的所有存储过程。 
 
使用union可能会减少程序数量。 
 
这里我怀疑我可以在我的商店程序中使用union，其中where条款
 
以下我给出了我的一个商店程序。在这里，@ searchparam我用于约会
 
 USE [registrationDB] 
 GO 
 / ******对象：StoredProcedure [dbo]。[repaccopy1data]脚本日期：11-02-2019 03:17:26 PM ****** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [dbo]。[repaccopy1data] 
 @searchparam VARCHAR（MAX）=''
 
 AS 
 SET NOCOUNT ON 
 
 DECLARE @SqlString VARCHAR（MAX）
 set @ SqlString ='select paymentDB.personid 
，paymentDB.pname 
，sum（paymentDB.pamount）as [amount] 
，regstDB.siteid 
，regstDB.rdate 
 
，regstDB.rpid 
，regstDB.totamt 
，regstDB.rperson 
 
来自paymentDB with（NOLOCK）内部联接personDB on paymentDB.personid = personDB.pid'
 
如果LTRIM（RTRIM（@searchparam））<>''
 
开始
 
 exec（@SqlString +'where'+ @ searchparam +'group by paymentDB.personid，paymentDB.pname，regstDB.siteid，r egstDB.rdate，regstDB.rpid，regstDB.totamt，regstDB.rperson'）
 
 end 
 
 else 
 
 begin 
 EXEC（ @sqlString +'group by paymentDB.personid，paymentDB.pname，regstDB.siteid，regstDB.rdate，regstDB.rpid，regstDB.totamt，regstDB.rperson'）
 PRINT（@ SqlString +'group by paymentDB.personid，paymentDB .pname，regstDB.siteid，regstDB.rdate，regstDB.rpid，regstDB.totamt，regstDB.rperson'）
 end 
 
 
 print @SqlString +'where'+ @ searchparam +'group by paymentDB.personid，paymentDB.pname，regstDB.siteid，regstDB.rdate，regstDB.rpid，regstDB.totamt，regstDB.rperson'

我尝试了什么：



我用谷歌搜索但没有得到合适的结果

解决方案

Google提供了大量有用的信息： sql union - Google搜索 [< a href =https://www.google.com/search?q=sql+union\"target =_ blanktitle =新窗口> ^ ]

Today I came to know how to use UNION to join tables. Thanks to CHill60. How to use that union in my stored procedure? I will explain my Problem here clearly. I am writing one application for cellphone towers registrations. There some amount we will give to that person like 15000. he is third party registration person like mediator between cell company and site owner. company will take lease site from owner. that mediator person will do the work like registration that lease and all. For that he will charge 3000 like that. some sites are able to register and some not. to calculate the account copy of that mediator like how much amount he took and how much he charged for which site he charged and hoe much balance is like that I have to show. for that I wrote some store procedures to achieve. first I wrote one store procedure to get that result. but not came so I wrote total 7 store procedures for that.
1 for opening balance of that person and 1 for registration and 1 for damage(if registration is not possible he will take expenses) and 1 for opening registrations count and 1 for opening damages site count and one for amount when we gave to that person and how much . all stored procedures with starting and ending dates.

Using union maybe I can reduce that count of procedures.

here my doubt is where I can use union in my store procedure with "where" clause

below I am giving my one store procedure. Here that "@searchparam" I used for dates

USE [registrationDB]
GO
/****** Object:  StoredProcedure [dbo].[repaccopy1data]    Script Date: 11-02-2019 03:17:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[repaccopy1data] 
@searchparam		VARCHAR(MAX) =''

AS     
SET NOCOUNT ON  

DECLARE @SqlString  VARCHAR(MAX)  
set @SqlString='select paymentDB.personid
				,paymentDB.pname
				,sum(paymentDB.pamount) as [amount]
				,regstDB.siteid
				,regstDB.rdate
				
				,regstDB.rpid
				,regstDB.totamt
				,regstDB.rperson
				
				from paymentDB  with (NOLOCK) inner join personDB on paymentDB.personid=personDB.pid '

if LTRIM ( RTRIM (@searchparam))<>''

begin
	
	exec (@SqlString + ' where  '+   @searchparam+' group by paymentDB.personid,paymentDB.pname,regstDB.siteid,regstDB.rdate,regstDB.rpid,regstDB.totamt,regstDB.rperson' )

end

else

begin
		EXEC (@SqlString+' group by paymentDB.personid,paymentDB.pname,regstDB.siteid,regstDB.rdate,regstDB.rpid,regstDB.totamt,regstDB.rperson')
		PRINT (@SqlString+' group by paymentDB.personid,paymentDB.pname,regstDB.siteid,regstDB.rdate,regstDB.rpid,regstDB.totamt,regstDB.rperson')
end


print @SqlString + ' where ' + @searchparam+' group by paymentDB.personid,paymentDB.pname,regstDB.siteid,regstDB.rdate,regstDB.rpid,regstDB.totamt,regstDB.rperson'   

What I have tried:

I googled but not came with suitable result

解决方案

Google has lots of useful information: sql union - Google Search[^]

这篇关于如何在我的MS-SQL存储过程中使用union的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！