IF/Else/Then/Insert/Len - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF/Else/Then/Insert/Len

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 08:56:39

I have a script to do a large number of inserts to a table. I would like to modify the script, if possible, to look at the length of one particular string for a particular column. If that string is greater than 20 characters, then have the Insert put that data into a different column and replace the original data with a hard-coded "See Comments" string. Below is one of those inserts with values. Any guidance as to how i can modify this to accomplish such? The column in this case that i want evaluated by Len is "CaseNo":

USE CIVIL
INSERT INTO CIVIL.dbo.RECEIPTS
(
RECEIPTNO,
RECEIVEDATE,
TYPEWRIT,
PLAINTIFF,
DEFENDANT,
COURTNAME,
CASENO,
COURTDATE,
DEPOSITAMOUNT,
PAYMENTTYPE,
CHECKNO,
REFUNDDATE,
REFUND,
REFUNDCHECKNO,
ATTORNEYID,
UNIQUEKEY,
ProcessTypeFKey,
Comments
)
VALUES
(
'GCSO1110000000000ENF',
'1/2/07',
'ORDER OF CONTEMPT AND WARRANT FOR COMMITMENT',
'FLORIDA COMMERCE CREDIT UNION',
'Jane Doe',
'COUNTY-LEON',
'2004 SC 2135',
'',
'20',
'2',
'35801',
NULL,
'0',
'35801',
'382',
SCADMIN.dbo.fnsc_GetUniqueID(NEWID()),
'3H4Lx74A10kfKsjyvc2Ymf'
''
);

Thanks

James

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-21 : 09:00:28

try using an instead of insert trigger...

http://msdn.microsoft.com/en-us/library/ms175089.aspx

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-10-21 : 09:05:03

DBAPBFL - WHAT?!?!?! Although you can use a trigger, what exactly is the point?

WJHamel - In your select, just use a case statement.

SELECT
...
, CASE WHEN LEN(CASENO) > 20 THEN 'See Comments' ELSE CASENO END AS CASENO
, ....
, CASE WHEN LEN(CASENO) > 20 THEN CASENO ELSE Comments END AS Comments
FROM
YourInput

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 09:12:01

quote:
Originally posted by DBAPBFL

try using an instead of insert trigger...

http://msdn.microsoft.com/en-us/library/ms175089.aspx

not worth adding it for this requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 09:17:23

Not just not worth it, but impossible in my case. This is all for a client's data conversion and i would not be able to put additional triggers on the client's system just for this purpose. I'll go with the case expression at this time.

thanks to all!

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 09:38:02

But further clarification on the case expression before i code it in:
The reason i need this is that my Caseno field is a Varchar(20), but our client has a number of "case numbers" that exceed this length. On the surface, it seems to me that the CE will need to look at the length of the string for Caseno AFTER it has been inserted. Since this is not going to happen in the case of a string longer than 20 chars, then nothing is ever going to fire off with the CE. So what i really need is the ability to look at the string while it is still in the query before it is inserted to Caseno then make the decision about where to insert that data. In theory, is that what this CE will do?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-10-21 : 12:18:26

Yes, the case looks at the data before attempting to do anything with it, such as inserting.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 12:35:51

So in my string of inserts, where should i place the case expression?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 12:39:39

[code]USE CIVIL
INSERT INTO CIVIL.dbo.RECEIPTS
(
RECEIPTNO,
RECEIVEDATE,
TYPEWRIT,
PLAINTIFF,
DEFENDANT,
COURTNAME,
CASENO,
COURTDATE,
DEPOSITAMOUNT,
PAYMENTTYPE,
CHECKNO,
REFUNDDATE,
REFUND,
REFUNDCHECKNO,
ATTORNEYID,
UNIQUEKEY,
ProcessTypeFKey,
Comments
)
SELECT
'GCSO1110000000000ENF',
'1/2/07',
'ORDER OF CONTEMPT AND WARRANT FOR COMMITMENT',
'FLORIDA COMMERCE CREDIT UNION',
'Jane Doe',
'COUNTY-LEON',
CASE WHEN LEN('2004 SC 2135')>20 THEN 'See Comments' ELSE '2004 SC 2135' END,
'',
'20',
'2',
'35801',
NULL,
'0',
'35801',
'382',
SCADMIN.dbo.fnsc_GetUniqueID(NEWID()),
'3H4Lx74A10kfKsjyvc2Ymf',
CASE WHEN LEN('2004 SC 2135')>20 THEN '2004 SC 2135' ELSE '' END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 12:57:37

Got it and THANK you!

But indulge me for a moment and help me understand something. This is the second time in doing these sorts of inserts (which i do frequently), where the advice given involved changing my "Values" statement in the insert to an "Select" statement. What's the reason that this has to be "Select" instead of "Values" and what does it allow me to do that "Values" does not?

Just curious.

thanks again

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-21 : 13:35:17

They both do similar things. Select allows you to, of all things, also select from a table. If you are dealing with literal strings, there doesn't seem to be a reason to use case expressions as you know what the values are. But, you can use a CASE expression with a VALUES clause or a SELECT statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources