How can I avoid this redundancy? [Sybase tSQL]

I'm kind of a newbie to Sybase tSQL, so I can't seem to figure this out.

Unfortunately, I have to use a bunch of nested queries to get data from a database I didn't create. My stored procedure is become rather huge because of the requirements my client is giving me which involves pulling bits of data from all sorts of random tables. Anyway...

Is there a way to simplify this?

...<snip>...
'varSomeVariable1' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 1)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
END,
'varSomeVariable2' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 3)
END,
...<snip>...

Note that this is greatly simplified so as to not make it too confusing for everyone.

As you can see, the only difference between the nested SQL statements is the value of the 'borrower_no' in the WHERE clause... is there a better way to do this so I can avoid writing two complete SQL statements for each value I'm trying to get? I.e., can I have a conditional statment in a WHERE clause?See if the COALESCE keyword is in sybase and see if that will help you re-write the code. Using COALESCE along with LEFT JOINs have help me make query simpler.

Note without out the from and where clause, people can only guess on what could help. I mean that info is needed to know if the two tables are related to one another. If they are not related to one another I see no way to help you. And, I think that you are in trouble because a database poor design is harder to fix than stored proc bad design.

IN this case and most cases, knowing the Primary and Unique Keys would be great help to helping you.

Tim S|||Hi,
You can use decode in your where clause..

i.e
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = DECODE(varSomeVariable1,CARD_FILE.company_nm , 1,2)

Hope this helps you.|||Hi Shelva;

Isn't Decode() an Oracle function? I can't find it in the Sybase tSQL documentation :( I sure which I could use it 'cause I think that would solve my problem!|||DECODE is the same thing as a flattened-out case.

You can do the same thing with case, e.g. (I don't know if this solves the problem, but here is how to re-write the Oracle SQL):
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = CASE varSomeVariable1 WHEN CARD_FILE.company_nm THEN 1 ELSE 2 END|||Thanks MattR! That worked swell :cool: I didn't know you could use CASE in that way (i.e., in the WHERE clause.)sql

How can a user change password in SQL2005 via TSQL?

I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;

The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;

"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"

TOM will be setup with db roles as well

When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"

At this point I get an error:

RunTime error -2147217900 (80040e14)

ODBC SQL Server Driver][SQL Server] Cannot alter

the login 'TOM', becuase it does not exist or you do

not have permission.

Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?

Thanks in advance for your help.

CH

A user does not need any permissions to change his password, but he cannot change his password policy setting - that option is only settable by someone that has ALTER ANY LOGIN permission. See http://msdn2.microsoft.com/en-us/library/ms189828.aspx.

Thanks

Laurentiu

|||

If I have the users change password statement read; it works.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx' "

However, I am unable to test this on a Win2003 Server right now, so I wonder if the Check Policy will be enforced for user TOM.

Under no circumstances do I want to have Policy Checked since the front end of my app provides the password security.

Any ideas if this is possible?

CH

|||If you are using ADO.NET 2.0 you can also use the

SqlConnection.ChangePassword(ConnectionString, "MyNewSecretpassword");

For changing the password.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the reply.

The problem we have run into is the Windows security, which is set across the domain, maybe set differently than our app. So if the user of our app chooses a different policy that that of the domain (which is fine), the user cannot alter his login since he does not have Alter Any login rights. And we wouldn't want to give him any.

Do you see a way around this?

All I can think of now is to change the password via the Admin login through a separate connection to the database. Something like this;

Dim gConnTmp As New ADODB.Connection
Dim sConnect As String

With gConnTmp
sConnect = "DSN=" & SQLDataSource & ";"
sConnect = sConnect & "UID= 'ADMIN' ;"
sConnect = sConnect & "PWD=" & gsPassword & ";"
.Open sConnect
End With

'change the password here... ALTER LOGIN etc.

'kill connection

gConnTmp.Close
Set gConnTmp = Nothing

Do you see any issues with doing this? I think it looks ok.

CH

|||

If you want to enforce a custom password policy, then you should just create the login with CHECK_POLICY set to off; otherwise, the Windows password policy will be enforced for a password change (on Windows 2003).

Thanks

Laurentiu