Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

Batch T-SQL Scripts

In Horacle (thanks to someone else for the apt pseudonym), I am able to run a PL/SQL script saved as a file by simply typing in @. plus the path and file name. So, if I have a script called E:\CreateTable.sql, I can simply enter @.CreateTable.sql from the SqlPlus command line and Oracle will attempt to run all the commands in the E:\CreateTable.sql file (there may be one or more commands).

Can I do the same thing in Query Analyzer (or even osql)? If so, what is the correct syntax?

Thanks,

hmscottI don't know of a way to do it from a script in either Query Analyzer or OSQL, but you can open a new file in Query Analyzer and execute it that way and you can use the ED command in OSQL to allow you to incorporate the file (via the editor) there too.

-PatP|||Not that I've seen...the closest is

File>Open>filename.sql

[CTRL]+E

And Whore-acle is so much more painful in SQL+...I guess each has it's own pluses...

Did you ever use mask.sql?

Oh, and osql could run in a command line pretty easily...just make sure you redirect the output...

But why bother, unless you're releasing a script to production...|||Thnx guys. Just wishful thinking...

Basics of T-Sql and XQuery

Pl let me know
1)the different between those two.
2)when to use?
and
3)anyone is Microsoft specific?

4) for and open xml can be used only in xquery?

1) XQuery is for querying XML fragments and T-SQL is a language that augments SQL for querying relational data.

2) You should use SQL for querying relation data and XQuery for querying XML within your applcation.

3) T-SQL has constructs that are SQL Server specific (i.e declaring a variable, while loops). The SQL part of the language has various Microsoft specific stuff, but we do try to align to the standard.

4) FOR XML and OPENXML are not part of XQuery. These were SQL Server 2000 technologies (still in 2005) used for composing and shredding xml to and from relational data.

Please see Books Online for more information: http://msdn2.microsoft.com/en-us/library/ms187875.aspx.

Regards,

Galex

Basic SQL: Multiple AND/OR nightmare in SELECT statement

This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...

I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.

SELECT *
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
ParentID1 = COALESCE(@.taxparent, ParentID3) OR
ParentID2 = COALESCE(@.taxparent, ParentID2) OR
ParentID3 = COALESCE(@.taxparent, ParentID3) OR
ParentID4 = COALESCE(@.taxparent, ParentID4) OR
ParentID5 = COALESCE(@.taxparent, ParentID5) OR
ParentID6 = COALESCE(@.taxparent, ParentID6) OR
ParentID7 = COALESCE(@.taxparent, ParentID7) OR
ParentID8 = COALESCE(@.taxparent, ParentID8)

The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.

Has anyone got a good tip to help me resolve this puzzle?

Hi

You can also use IN and NOT IN as well as Having caluse to verify condition. As ( and ) paranthesis can also help you to verify condition on a condition,

|||Hi Akbar,

Sorry, but that is not really helpful.

As far as I understood, IN is used to test multiple values against a single column. I am testing a single value against multiple columns.

HAVING is used with aggregate values. I am not using those.

As I already wrote, using parenthesis does not work for me, or I do not know how to properly apply them in this particular case.

I am still with my hands in my hair on finding a solution to this, so I would appreciate any help.|||All right, fair enough, after some study, I was able to simplify the code using 'IN', that -new to me- could also be used for testing a single value against multiple columns. But I am stuck with the same problem that it won't combine with the rest of the conditions in order to yield the desired results!

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' OR
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me too many results and

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me no results....

|||OK, another discovery! When I comment out:

ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%'

It does actually succesfully combine the different criteria!

This must mean something goes wrong with those lines only...

Here is the entire code of the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [petrander].[DynamicQuery]
@.taxparent int = NULL,
@.museum int = NULL,
@.collection int = NULL,
@.binomen Nvarchar(254) = NULL,
@.locality Nvarchar(254) = NULL
AS
SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE 'N%' + @.binomen + '%' AND
Locality LIKE 'N%' + @.locality + '%' AND
@.taxparent IN (ParentID1,
ParentID2,
ParentID3,
ParentID4,
ParentID5,
ParentID6,
ParentID7,
ParentID8)

Could the problem lie in combining null values with the LIKE 'N%' + statements?|||Problem lay somewhere else and solution can be seen in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620363&SiteID=1