Click here to Skip to main content
13,863,336 members
Rate this:
Please Sign up or sign in to vote.
See more:
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]
/****** Object:  StoredProcedure [dbo].[repaccopy1data]    Script Date: 11-02-2019 03:17:26 PM ******/

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


set @SqlString='select paymentDB.personid
				,sum(paymentDB.pamount) as [amount]
				from paymentDB  with (NOLOCK) inner join personDB on '

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

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



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

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
Updated 11-Feb-19 2:00am
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

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Google has lots of useful information: sql union - Google Search[^]
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

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.

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.
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.
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();

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190214.1 | Last Updated 11 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100