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
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