NULL parameters 2

[JimmyFo]

Hi, I'd like to make a stored procedure that checks the list of parameters and only compares the ones that are not null. I assume that means that I set the default parameters to NULL, or that when I call the stored procedure I pass in null values. How do I set the select statement then? I have something like this:

CREATE PROCEDURE SELECT_PROJECTS
	-- Add the parameters for the stored procedure here
	@p1 int = null, 
	@p2 int = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from tblProject where int_fk_PortfolioID = @p1
END

but you can see, it doesn't compare for the idea that if it is null, ignore that parameter and select them all.

Any ideas?
Thanks,
James

 

[gmmastros]

Take a look at this where clause.

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] SELECT_PROJECTS
    [COLOR=green]-- Add the parameters for the stored procedure here
[/color]    @p1 [COLOR=blue]int[/color] = null, 
    @p2 [COLOR=blue]int[/color] = null
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
    [COLOR=green]-- SET NOCOUNT ON added to prevent extra result sets from
[/color]    [COLOR=green]-- interfering with SELECT statements.
[/color]    [COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color];

    [COLOR=green]-- Insert statements for procedure here
[/color]    [COLOR=blue]SELECT[/color] * 
    [COLOR=blue]from[/color]   tblProject 
    [COLOR=blue]where[/color]  (@P1 [COLOR=blue]Is[/color] NULL Or int_fk_PortfolioID = @p1)
[COLOR=blue]END[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JimmyFo]

Very cool, it works! Thank you -

 

[JimmyFo]

Rather than start a new thread, I'd like to ask another question here:

In my table, I have a number of foreign keys that point to the same table, a user table. I need to get the user names for each of these columns.

So if I'm going to have one row returned, that row will have 7 columns in it that are ints that point to the user table (it's their user ids). I need to get the names for each of these ints from the user table and join it to the results. This is the way I'm heading, but it's the wrong direction I think....

 SELECT 
		P.strProjectName 'Name',
		P.strProjectDescription 'Desc',
		P.strProjectNotes 'Notes',
		U.strUserFirstName + U.strUserLastName 'Name'
    FROM   tblProject as P, tblUser as U
    WHERE  (@p1 Is NULL Or P.int_fk_PortfolioID = @p1)
		and P.bitProjectIsActive = 'True'
		and U.intUserID = P.int_fk_Manager
.......

It doesn't take into account the multiple user IDs that can be for the multiple other user ID columns (not shown).

Ideas?

Thanks!
James

 

[gmmastros]

First, you really should be posting your questions in the correct forum. You are obviously using Microsoft SQL Server, so the correct forum would be: forum183

I see a couple things about your query that bothers me.

1. Don't use single quotes around column aliases. SQL server prefers square brackets. Obviously, this won't cause your query to fail, but it's a good habit to get in to.
[tt][blue]P.strProjectName [!][[/!]Name[!]][/!][/blue][/tt]

2. You should start using ANSI style joins in your where clause. For example, you have...

[tt][blue]FROM tblProject as P, tblUser as U
WHERE U.intUserID = P.int_fk_Manager[/blue][/tt]

Instead, you should use...

[tt][blue]
FROM tblProject as P
Inner Join tblUser as U
On U.intUserID = P.int_fk_Manager[/blue][/tt]

3. It appears as though bitProjectIsActive is a bit field. To compare bit fields within T-SQL, you should use the values 1 or 2. Like this...

[tt][blue]and P.bitProjectIsActive = [!]1[/!][/blue][/tt]


Now, with that being said, I should point out that storing multiple userid's in multiple columns within a table is NOT normalized. Instead, you should create another table to store the id's. Think about it. Right now you are storing 7 id's with 7 different columns. What would happen if your customer wanted to store an 8th id? You would have to add another column and then change a bunch of code to accomodate the new column. If, instead, you created another table to store the id's, it would be as simple as adding another record to the table.

I do realize that often times people have no control over the structure of the database, so, the solution to your problem, with it's current structure would be...

[COLOR=blue]SELECT[/color]  P.strProjectName [[COLOR=blue]Name[/color]],
        P.strProjectDescription [[COLOR=#FF00FF]Desc[/color]],
        P.strProjectNotes [Notes],
        U.strUserFirstName + U.strUserLastName [[COLOR=blue]Name[/color]],
        User1.strUserFirstName + User1.strUserLastName [[COLOR=blue]Name[/color] 1],
        User2.strUserFirstName + User2.strUserLastName [[COLOR=blue]Name[/color] 2],
        User3.strUserFirstName + User3.strUserLastName [[COLOR=blue]Name[/color] 3]
[COLOR=blue]FROM[/color]    tblProject [COLOR=blue]as[/color] P
        [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] tblUser [COLOR=blue]as[/color] U
          [COLOR=blue]On[/color] U.intUserID = P.int_fk_Manager
        [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] tblUser [COLOR=blue]as[/color] User1
          [COLOR=blue]On[/color] P.UserId1 = User1.intUserId
        [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] tblUser [COLOR=blue]as[/color] User2
          [COLOR=blue]On[/color] P.UserId2 = User2.intUserId
        [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] tblUser [COLOR=blue]as[/color] User3
          [COLOR=blue]On[/color] P.UserId3 = User3.intUserId
[COLOR=blue]WHERE[/color]  (@p1 [COLOR=blue]Is[/color] NULL Or P.int_fk_PortfolioID = @p1)
        and P.bitProjectIsActive = 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

You have to use one different instance of tblUser for each foreign key:
SELECT
P.strProjectName 'Name',
P.strProjectDescription 'Desc',
P.strProjectNotes 'Notes',
U1.strUserFirstName + U1.strUserLastName 'Manager',
U2.strUserFirstName + U2.strUserLastName 'Another',
FROM tblProject as P, tblUser as U1, tblUser as U2
WHERE (@p1 Is NULL Or P.int_fk_PortfolioID = @p1)
and P.bitProjectIsActive = 'True'
and U1.intUserID = P.int_fk_Manager
and U2.intUserID = P.int_fk_Another

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JimmyFo]

Thank you both! I've cleaned it up and modified it, works perfectly!

 

[JarlH]

One comment about "ANSI joins":
[tt]
FROM tblProject as P, tblUser as U
WHERE U.intUserID = P.int_fk_Manager[/tt]

is ANSI compliant (since SQL-89), and
[tt]
FROM tblProject as P
Inner Join tblUser as U
On U.intUserID = P.int_fk_Manager[/tt]

is also ANSI compliant (since SQL-92).

They mean the same, they produce the same result. (But some DBMS products *may* work faster/slower depending on which one you chose.)