conditional where statement

I have a stored procedure that performs a search function with params:

@.username nvarchar(50)
@.country nvarchar(50)
and like 10 more.

A user may provide values for these params optionally.
So when the @.username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)
Currently my statement is:

select username,country from myUsers where
username=@.username andcountry=@.country

With this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...

What can I do to solve this?

Thanks!

SELECTFROM YourTableWHERE (@.usernameISNULL OR UserName = @.username )AND (@.countryISNULL OR Country = @.country )

|||

Thanks, but in this case the username field would not be ignored.
If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username, the statement would be:

SELECT
FROM YourTable
WHERE (@.usernameISNULL OR UserName = '' )

In this case the user with name "peter" would not be found. If a webvisitor does NOT provide a username, I want to return all rows regardless of the value in the username field...
Im just hoping I've explained myself clearly now :)
Thanks!

|||

use WHERE (UserName = COALESCE(@.UserName, UserName)) AND (Country = COALESCE(@.Country, Country)) AND etc.

COALESCE (or ISNULL if you prefer) will return the first value in the parameter list that is not null, so if you pass a NULL value for, say, @.UserName, that part of the WHERE clause will resolve to "WHERE UserName = UserName", which of course, is always true.

|||

Peter Smith:

If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username

Can you explain what you mean by that? If there is a value in @.username, it will be searched against, else ignored. If there is a value provided and it does not exist in the table, obviously nothing will be returned. Incase the query doesnt work as expected, please provide some sample data, and sample scenarios and their expected outputs.

|||

Dinakar Nethi provided an excellent query for your issue. The key to implement is:

You need to set your input parameter to default NULL first.

@.UserName NVarchar(50) = NULL,

@.Country NVarchar(50) = NULL

|||

mmm, I see (now). I tested your query and it works :)
Thanks!

concatenation?

I have a field named "changes" it is nvarchar (150) it is in a table called changes_log.

A new row will be inserted everytime that an update statement occurs on another table and it should describe what was updated. I want to essential check if each new value is = to old value and if it is add that to a string that will become the value of "changes" for the new row.
It might contain 1 field name or many.

Here is the SQL code I am trying to work with inside a stored procedure:

INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
END

But it only adds "ROUTER_NAME" even when both are changed. Maybe I'm going about this all wrong, can anyone offer me and tips on how to log what fields were changed in the database, by who and when?Your UPDATE statments have no WHERE clause. Other than that, I am not clear on exactly what you are trying to do.|||You are right! I totally forgot and left that out!

INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
WHERE ITEM_NAME = @.NAME
END
select * from changes_log

output (using a different or changed name and serial_num so that both if statements would be true:

ITEM_NAME _DATE USER_ID CHANGES
------------------------------
NEW 10/17/2003 12:17:33 PM LOUMAS\NCL4504S ROUTER_NAME

Where Changes should contain "ROUTER_NAME SERIAL_NUM"

I might be approaching this all wrong but here is what the requirement is:

KEEP A LOG OF EACH ITEM THAT HAS BEEN EDITED, WHO EDITED IT, WHEN DID THEY DO IT, WHAT FIELDS WERE CHANGED.

NOTE: these items are always edited through a stored procedure. there many field unique for different types of items (from different tables) that could be edited. that is why I attempted to simply capture the field name of field that was changed.

Any ideas or help is welcome, I can change the structure of the Changes_Log table (above) if necessary. I can start from scratch if necesary.|||This is incorrect:

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

try...

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

|||thanks for the reply. I made your suggested change and the result is "changes" in the new row = NULL|||try...

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = IsNull(CHANGES,'') + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

I gather CHANGES defaults to NULL, and so NULL + anything is NULL. IsNull returns the leftmost non-null argument, and thus should work.|||If I understand correctly you want one record in changes_log per update with a concatenated list of changes in the changes field indicating which fields in another table have been edited.

Why don't you declare a @.Changed varchar and assemble that before making any inserts to changes_log? Do a single insert at the end instead of an insert and two updates.

Are you launching this from a before trigger?
Where does @.Router come from?

Glenn|||YES IT WORKED! THANKS SO MUCH!|||OK, you do understand what I am trying to do correctly.

I'm not sure what you are getting at with declaring a @.Changed varchar? Is this a temporary variable to do a single insert? If so that sounds like a good idea, I just wanted to get something to work first. Actually there will be way more than 2 updates when complete, unless I do it this way.

I am not using any triggers.

@.Router is a value passed to the stored procedure that identifies the item by it's original name even if the name has been changed. @.Name would be the new name if name has been changed, otherwise @.Name = @.Router.

Thanks.|||Ok I changed my code as you suggested and things were working fine, until after adding all the fields I realized that if the current value of a field is NULL the IF statement returns false. Logically I think

IF NOT (@.NEW_VALUE = (SELECT NAME FROM TABLE WHERE ID = @.ID) )

where the @.NEW_VALUE is not NULL and the NAME value returned is null, should return true but it must not compare the same for NULL or something because only those statements do no execute.

Here is my code: note that I tried checking if the value returned from select is null on some items but it still did not work.

ALTER PROCEDURE sp_UPDATE_ROUTER
(
@.ROUTER nvarchar(50),
@.NAME nvarchar(50) = @.ROUTER, /*IF NO NEW NAME IS PASSED DEFAULT = CURRENT ROUTER NAME*/
@.SERIAL nvarchar(25) = NULL,
@.MODEL nvarchar(30) = NULL,
@.IOS_VER nvarchar(15) = NULL,
@.IOS_BASED nvarchar(3) = NULL,
@.BOOT_VER nvarchar(15) = NULL,
@.NETWORK nvarchar(3) = NULL,
@.FIREWALL nvarchar(16)= NULL,
@.REACH nvarchar(60) = NULL,
@.DIAL nvarchar(30) = NULL,
@.FAC_ID nvarchar(20) = NULL,
@.FAC_NAME nvarchar(40) = NULL,
@.STREET nvarchar(60) = NULL,
@.CITY nvarchar(40) = NULL,
@.STATE nvarchar(2) = NULL,
@.ZIP nvarchar(10) = NULL,
@.CONTACT nvarchar(60) = NULL,
@.CONTACT2 nvarchar(60) = NULL,
@.PO nvarchar(20) = NULL,
@.MO nvarchar(20) = NULL,
@.COMMENTS nvarchar(50) = NULL,
@.USER_ID nvarchar(50)
)
AS
DECLARE @.TEMP nvarchar(300)
IF NOT @.NAME = @.ROUTER
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
IF NOT @.MODEL = (SELECT MODEL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MODEL '
END
IF NOT @.IOS_BASED = (SELECT IOS_BASED FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_BASED '
END
IF NOT @.IOS_VER = (SELECT IOS_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_VER '
END
IF NOT @.NETWORK = (SELECT NETWORK FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'NETWORK '
END
IF NOT @.FIREWALL = (SELECT FIREWALL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FIREWALL '
END
IF NOT (@.BOOT_VER = (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'BOOT_VER '
END
IF NOT (@.REACH = (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'REACHABLE_FROM '
END
IF NOT @.DIAL = (SELECT DIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'DIAL_NUM '
END
IF NOT @.FAC_ID = (SELECT FACILITY_ID FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY_ID '
END
IF (NOT @.FAC_NAME = (SELECT FACILITY_NAME FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STREET = (SELECT STREET FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.CITY = (SELECT CITY FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STATE = (SELECT STATE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.ZIP = (SELECT ZIP_CODE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER))
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY ADDR '
END
IF NOT @.CONTACT = (SELECT CONTACT FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT '
END
IF NOT @.CONTACT2 = (SELECT CONTACT2 FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT2 '
END
IF NOT @.PO = (SELECT PO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'PO '
END
IF NOT @.MO = (SELECT MO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MO '
END
IF NOT @.COMMENTS = (SELECT COMMENTS FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'COMMENTS '
END
INSERT INTO CHANGES_LOG (ITEM_NAME, _DATE, SERIAL_NUM, USER_ID, CHANGES) VALUES(@.NAME, GETDATE(), @.SERIAL, @.USER_ID, @.TEMP)
select * from changes_log
RETURN

THANKS|||What I meant with the @.Changed varchar was what you did with @.TEMP.|||If I understand your problem, you need to check the @.Parameters if they are null before comparing them with the previous value selected from ROUTERS.

A nested IF for each value would work.

IF NOT IsNull(@.SERIAL,'') = ''
BEGIN
 IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
END

Also I think you should be able to SET @.TEMP = '' right off, and eliminate the IsNull check in your existing code. This would go back to the simpler...

 SET @.TEMP = @.Temp + 'SERIAL_NUM '

I don't know if that will finish it, but it's another step...|||well I cna tell you just to test it I used 'xyz' for every value and none are null and none currently have a value of 'xyz'|||And?
What were your results? Your changelog should've shown a change for each field right? Did it work?|||The same as above, I posted that message with the datavalues that I described. I have already thought of all that.

concatenation uniqueidentifier into nvarchar

where t.[PortalID] = '

+convert(nvarchar(36), @.PortalID)+'

where t.[PortalID] ='+ convert(nvarchar(36), @.PortalID) +'

Im trying to concate a uniqueidentifier into a BIG nvarchar(4000) string so i can execute it at the end (

exec

sp_executesql @.sql)

But my problem is that i got an error saying that i can not do so or the first 5 digit of the uniqueidentifier object is not well formated.

any ideas ? thank you

try:

where t.[PortalID] = '''+convert(nvarchar(36), @.PortalID)+'''

|||

It does not work.

|||

Needs to be quoted as per my previous post. However, I believe the you are getting a second problem. Most likely that you are trying to pass the uniqueidentifier from .NET code to sql, and you've either assigned the wrong type, or you have use concatenation that mangled the guid.