How to use union in my MS-SQL stored procedure

Question

1.00/5 (1 vote)

See more:

SQL-Server-2016

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

Posted 10-Feb-19 22:55pm

vijay_bale

Updated 11-Feb-19 1:00am

v5

Add a Solution

Comments

Richard Deeming 12-Feb-19 11:00am

@SqlString + ' where  '+   @searchparam+' group by

A perfect example of why stored procedures don't render you immune to SQL Injection[^]!

To execute dynamic SQL, use sp_executesql[^], and pass in the parameters as parameters rather than stuffing them into the command text.

You will also need to update the calling code to pass separate parameters, rather than a complete WHERE clause.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2019-02-10T23:19:00

Solution 1

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

Posted 10-Feb-19 23:19pm

Richard MacCutchan

Comments

vijay_bale 11-Feb-19 6:17am

Thanks. But I think you didn't read my question total. In my question i asked where I can "Where" clause in my procedure?

Richard MacCutchan 11-Feb-19 6:31am

Yes, I did read it, but I just got more and more confused with all that information about different companies and their business practices. If you want a proper answer then you need to provide technical details of what your stored procedures are supposed to be doing, and what results you are trying to return.

vijay_bale 11-Feb-19 6:53am

above stored procedure for the payment how much we gave to that mediator person for registrations ( paymentDB) and how many sites he registered (regstDB) in some in between dates (for that I used searchparam as parameter)

above stored procedure showed some wrong results like he got paid sum of 15000 and registrations done 2 means 2*3000=6000 but that store procedure give results like 15000 2 times so I used other stored procedure for that regstDB(registrations) purpose. Here in this procedure I put in comments lines like --regstDB.siteid
--,regstDB.rdate
--,regstDB.rpid
--,regstDB.totamt
--,regstDB.rperson

now I am getting the correct results when I execute that 2 procedures. If I came to know where we can use "where" condition then I can use only one stored procedure maybe.

with one stored procedure i am getting only payments means how much we paid him. and second stored procedure only for registrations. so that we came to know how many site he registered.

Richard MacCutchan 11-Feb-19 7:28am

You can use a WHERE clause anywhere you like. It depends on what items of information you need to extract from the database.

MadMyche 11-Feb-19 7:18am

I agree; while some context can be helpful, what we really need to know is the tech side of this, and the Biz Intelligence you are using to choose what WHERE to use.

vijay_bale 11-Feb-19 8:09am

Above stored procedure is for opening balance of that person

I am calling that stored procedure from c#
// I am reading pername from dropdown list from that I am reading perid from personDB.
var searchachist = "personid=" + perid + " and pdate<'" + DBDate(acdt2) + "'";//taking the ending date and calculating the amount as opening

var accopy1 = new System.Data.DataTable();
using (var cmd = new SqlCommand("repaccopy1data", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@searchparam", SqlDbType.Text).Value = searchachist;
var da = new SqlDataAdapter(cmd);
accopy1 = new System.Data.DataTable();
da.Fill(accopy1);
con.Close();
}

How to use union in my MS-SQL stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

How to use union in my MS-SQL stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us