Update and Insert statement help 1

[johnc83]

Hi all, could someone help me create a procedure to accomplish the following please..

Lets say I have 5 job records in my tblJobs

JobNo, CustomerCode, Price, Status, InvoiceNo,
1, A000, £100.00, Completed, NULL
2, A000, £100.00, Completed, NULL
3, A000, £200.00, Completed, NULL
4, B000, £50.00, Completed, NULL
5, B000, £50.00, Completed, NULL

I need a statement which inserts one new record in my tblInvoices for each different customer in the tblJobs...

Using the above jobs as an example, the following records would be inserted into my tblInvoices.

InvoiceNo, CustomerCode, TotalPrice
1, A000, £500.00
2, B000, £100.00

After inserting these records, it would then update the Status and InvoiceNo fields in the tblJobs so they look like this..

JobNo, CustomerCode, Price, Status, InvoiceNo,
1, A000, £100.00, Invoiced, 1
2, A000, £100.00, Invoiced, 1
3, A000, £200.00, Invoiced, 1
4, B000, £50.00, Invoiced, 2
5, B000, £50.00, Invoiced, 2

Any help would be greatly appreciated. If i knew where to start I would have a go and come back with the problems but I don't even know where to start!

Cheers

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[DjangMan]

For the first step, assuming the invoice number is autogenerated, you're probably doing something like:

insert into tblInvoices select customercode, sum(price) from tblJobs group by customercode

Google "Insert Into" for more info.

Sometimes you can't do everything in once step in SQL. I'd be interested in creating a batch number field in the tblJobs. Create a new batch number and set all of the invoices with NULL in the Invoice Number column to that batch number.

Then I would update the tblInvoices table and add in the batch number. Then I can update the tblJobs table from the tblInvoices table based on Customer and BatchID.

There are other ways, of course, but this might give you some direction to start with.

 

[memdiscount]

Hi;

I would try this logic, I could not try but I hope it would work:

Assumptions:
1- In tblInvoices column invoiceno is Identity field
2- You have only two columns in tblInvoice table
3- In tblJobs invoiceno column is always NULL for new records

-- ******** To insert data into tblInvoices in one query ***

Insert into tblInvoices
Select CustomerCode , Sum(Price) from tblJobs
Where InvoiceNo is null
Group by CustomerCode

-- ******** To update data into tblJobs in one query *******

Update A
Set A.InvoiceNo = B.InvoiceNo
From tblJobs A
Inner join tblInvoices B ON A.CustomerCode = B.CustomerCode
Where A.InvoiceNo is null

-- ***********

Let me know if it works for you!

Thanks

 

[johnc83]

Hi ppl, thanks for the replies..

Your info was just what I needed to get started so thank you very much..

Obviously, my example was simplified just to get going and now I am looking at the fuller code, I have one question (hopefully simple)...

How do i write the code which selects all customer codes or just one (if given).

Example, suppose I want all the details from the tblCustomers for just one customer or all if no account number is given?

Select * From tblCustomers 
Where CustomerCode = @Param1 or all customers

Thank you so much for the help.

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[DjangMan]

What is your front end? How is the user entering the request?

 

[gmmastros]

Like this...

Select * 
From   tblCustomers
Where  (@Param1 = '' Or CustomerCode = @Param1)

This assumes that you pass an empty string when you want all rows. If CustomerCode is an integer data type, you could use 0 instead. Alternatively, you could pass NULL to represent the situation where you want all rows.

Where (@Param1 Is NULL Or CustomerCode = @Param1)

If you have additional where clause conditions, make sure you use parenthesis. In fact, I have a personal rule that whenever I use an OR condition in a where clause, I always use parenthesis. If you follow this rule too, it will help prevent unintentional mistakes later.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnc83]

Excellent George, works perfect. cheers

DjangMan - Using windows form (VB.NET).

Thanks again

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[DjangMan]

Kudos to George - nice trick. My only concern would be that OR conditions are harder to optimize. If you can, from your windows form, adjust the query based on the user's selection, then you can simple remove the WHERE clause if the user wants all records.

 

[gmmastros]

DjangMan,

You're right. With the OR condition, you will get an index scan, which is slower than an index seek.

However....

1. If you don't specify a customer code, the best you can hope for is a scan anyway.

2. If there are other where clause conditions that cause a seek, then there will be no performance difference using the 'OR Trick'.

3. I would never recommend that a programmer put dynamic SQL in the front end. There are several problems with this. The query optimizer engine will need to create the execution plan when the query is executed. There are ways to get plan re-use from dynamic sql, so this can be a weak argument. Another concern is SQL Injection. Since this is a VB.net app, the chances of SQL Injection are minimized, but not completely removed.

A bigger concern, in my opinion, is the fact that the application is controlling the query. The problem with this (at least from my perspective) is that it is very difficult and time consuming to test/debug/retest/debug some more/release an application. If the query is in a stored procedure, all you need to do is to fix it, test this single procedure, and then deploy it (re-create the procedure in the production environment). When your SQL code is in the database, it is MUCH easier to maintain in the long run.

Let me re-phrase this.

Dynamic SQL can be bad because...

1. Performance
2. SQL Injection
3. Permissions
4. Code maintenance

In my opinion, it is not too difficult to handle the first three. But the 4th (Code Maintenance) is much harder with dynamic sql.

If this were my app, and I couldn't get the 'OR Trick' to use an index seek, I would write another stored procedure just for that condition. I wouldn't use dynamic SQL.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DjangMan]

Stored procedures are the 'cats meow'. Thanks for the pointer on SQL, George. Cheers.

 

[deejayAr]

try this

Select *
From tblCustomers
Where (@Param1 is null Or @Param1 = CustomerCode )

 

[johnc83]

hi again, just so you know - this is a stored procedure i am working on and thanks to the help ive got its pretty much done.

Fortunately, i dont understand how to do dynamic sql and ive heard that there are problems with using it but at the moment i am doing everything ok without it.

thanks again, john

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[ESquared]

George,

To be a purist, I would argue for a data access layer that does the work of converting the parameters into a smooth query without the use of OR. This gives the best of both worlds where the client doesn't have full database access, but the queries submitted to the server are optimized. But that's probably not necessary for most situations.

So next, I would compare performance. If your query pattern is such that avoiding many ORs really improves performance, then use the client app to build the queries. Just pay very careful attention to security. Technically, building SQL strings in the client application is not dynamic SQL. It's only when the SQL Server is itself building strings that are then executed.

A proper framework in the client or in the business layer or in the data access layer could make all this possible. You'd test the framework and once it is working then the impact of changes would be isolated, which is the exact benefit that stored procedures give you.

In terms of compilation and query plan reuse between SPs and ad-hoc SQL, I believe that in SQL 2000 it is not so radically different and in SQL 2005 it is even more improved, to the point where SPs in many cases may not really offer a performance advantage (though still holding their own in encapsulation and security).

 

[gmmastros]

Technically, building SQL strings in the client application is not dynamic SQL. It's only when the SQL Server is itself building strings that are then executed.

http://www.sommarskog.se/dynamic_sql.html

In this article I will discuss the use of dynamic SQL [blue]in stored procedures[/blue] and to a minor extent [green]from client languages[/green].

This implies that there are various types of dynamic sql. Issues that cause problems from within stored procedures also exist when the dynamic sql is client side. Specifically, performance (to a lesser extent), SQL Injection, Permissions, and code maintenance. When comparing the source of SQL Injection, the details may be different, but the same (or similar) problems exist.

If your query pattern is such that avoiding many ORs really improves performance, then use the client app to build the queries.

I disagree. I don't think the app should ever build queries. If you cannot get acceptable performance by using a generic stored procedure, then you should create a stored procedure specifically for it. In this case, if the OR is causing scans instead of seeks, then you should build a stored procedure to handle the case where a seek can be used, and another for the general purpose situation where a scan is used.

A proper framework in the client or in the business layer or in the data access layer could make all this possible. You'd test the framework and once it is working then the impact of changes would be isolated, which is the exact benefit that stored procedures give you.

Of course, having a multi-tier app helps isolate bugs and makes distributing apps easier. However, it is my opinion that the 'more isolated' the code is, the easier it is to fix and to distribute those bug fixes. A bug in a single stored procedure can be distributed simply by replacing that one single procedure. In my experience, n-tier apps are built with a set of dll's representing each tier. To safely distribute a new dll, every function contained within the dll should be tested. This is likely to be more time consuming than testing a single stored procedure because there's usually more than one function in a dll.

In terms of compilation and query plan reuse between SPs and ad-hoc SQL, I believe that in SQL 2000 it is not so radically different and in SQL 2005 it is even more improved, to the point where SPs in many cases may not really offer a performance advantage

There are ways to get plan re-use from dynamic sql, so this can be a weak argument.

I think we are essentially saying the same thing,

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[memdiscount]

Hi;

I use COALESCE Function for that purpose. It returns first not null value, so if the parameter is null then it will match with its own value and if there is a value for parameter then it will match with parameter.

Try this:

IF @Param1 = ''
SET @Param1 = NULL

Select *
From tblCustomers
Where CustomerCode = COALESCE(@Param1, CustomerCode)

Thanks

 

[gmmastros]

memdiscount,

There is a potential problem with your query. In your example, if CustomerCode can be null and there are rows with a null value for customer code, that row will NOT be returned because nulls do not compare properly to other nulls.

Here is an example that demonstrates the problem.

[COLOR=green]-- Create some test data
[/color][COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Hello World'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]''[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)

[COLOR=blue]Declare[/color] @Param [COLOR=blue]VarChar[/color](20)

[COLOR=green]-- Test with parameter = NULL
[/color][COLOR=green]-- Note, this version does not return all the data
[/color][COLOR=blue]Set[/color] @Param = NULL

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  Data = [COLOR=#FF00FF]Coalesce[/color](@Param, Data)

[COLOR=green]-- Test with parameter = 'Hello World'
[/color][COLOR=green]-- This does properly filter the data
[/color][COLOR=blue]Set[/color] @Param = [COLOR=red]'Hello World'[/color]

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  Data = [COLOR=#FF00FF]Coalesce[/color](@Param, Data)

[COLOR=green]-- Test with parameter = NULL
[/color][COLOR=green]-- This does properly return all the data
[/color][COLOR=blue]Set[/color] @Param = NULL

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  (@Param [COLOR=blue]Is[/color] NULL Or Data = @Param)

[COLOR=green]-- Test with parameter = 'Hello World'
[/color][COLOR=green]-- This does properly filter the data
[/color][COLOR=blue]Set[/color] @Param = [COLOR=red]'Hello World'[/color]

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  (@Param [COLOR=blue]Is[/color] NULL Or Data = @Param)

If your column does not allow nulls, then your method works as expected. Just be careful when using this method.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

George,

Re dynamic SQL, I'll give you that one. Just remember that even when you are using parameterized SPs, a piece of software (the DB access library) is dynamically building a SQL query to submit. Your simple "EXEC MySP" is often presented to the server as something else such as "exec sp_executsql N'EXEC MySp @P1, @P2, @P3', N'@P1 int, @P2 varchar(10), @P3 datetime', 5, 'abc', '2008-05-28'". By the same argument, this is dynamic SQL.

SQL Injection, permissions, and code maintenance do matter, and while I didn't make it clear, I lean strongly toward use of SPs.

But in the case of a query like this one:

SELECT <columns>
FROM <blah>
WHERE
   (@a is NULL OR a = @a)
   AND (@b is NULL OR b = @b)
   AND (@c is NULL OR c = @c)
   AND (@d is NULL OR d = @d)
   AND (@e is NULL OR e = @e)
   AND (@f is NULL OR f = @f)
   AND (@g is NULL OR g = @g)
   AND (@h is NULL OR h = @h)
   AND (@i is NULL OR i = @i)
   AND (@j is NULL OR j = @j)

It can be very impractical to create an SP for each way that this can be used. Queries like this are sometimes necessary. If it's a big table and performance is suffering, then in my mind the least bad solution is to build the correct SQL that has only the parameters involved in THIS current request. Whether that's done in the client, or a middle tier, or even on the server is up for debate.

Your point regarding DLL hell is a good one. However, the advantages offered by building a framework can outweigh this. The idea is that you make the framework so data-driven that it does not have to change just to alter the logic of your data access. Alex was telling me the other day about a company whose front-end application has no forms or events or anything: it all is described in the database and handed to the front-end as xml, and it just does what it's told.

My ideas for this particular problem run along the lines of creating a stored procedure fragment (whether saved in a real SP or as text in a table) which is read by the framework and modified as necessary based on what's presented to it. So you could still maintain your final query access in the database, but get your dynamic SQL optimization anyway. Something like:

SELECT <columns>
FROM <blah>
WHERE
   {begin#operator:AND,defaultnovalue=NULL}
   @a = a
   @b = b
   @c = c
   {end}
   ...

This could be a template that the framework uses to construct its queries for these special cases. You might even get it to read the original query above with comments at the end of each line to control behavior, so that the SP could work as-is for testing purposes.

The framework building would be done quickly up front, rarely changed, and used over and over.

 

[memdiscount]

Hi gmmastros;

Thanks for reply. I have encountered that potential problem with it and there is a fix for that in your sample code.

But, I agree other method is more simple !!! I just want to share the fix of that potential problem if someone is using COALESCE function already for his solutions....The use of ISNULL function with COALESCE function would resolve that problem. For strings I would use isnull(data,'') and for integer values I would use isnull(data,0)...

*************************

-- Create some test data
Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('Hello World')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

Declare @Param VarChar(20)

-- Test with parameter = NULL
-- Note, now this version does return all the data
Set @Param = NULL

Select *
From @Temp
Where Isnull(Data,'') = Coalesce(@Param, isnull(Data,''))

-- Test with parameter = 'Hello World'
-- This does properly filter the data
Set @Param = 'Hello World'

Select *
From @Temp
Where Isnull(Data,'') = Coalesce(@Param, isnull(Data,''))

-- Test with parameter = NULL
-- This does properly return all the data
Set @Param = NULL

Select *
From @Temp
Where (@Param Is NULL Or Data = @Param)

-- Test with parameter = 'Hello World'
-- This does properly filter the data
Set @Param = 'Hello World'

Select *
From @Temp
Where (@Param Is NULL Or Data = @Param)

********************************************

Thanks

 

[ESquared]

Select *
From   @Temp
Where  Isnull(Data,'') = Coalesce(@Param, isnull(Data,''))

This is going to be higher CPU utilization, you know, and always force a scan because of performing a calculation on every row...