December 17, 2008 at 4:19 am
I am trying to pass a null value into a select stored procedure so in my .net page i can show the data from a table even if i dont pass a parameter into it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[select_NewRequest] @P1 int AS
if @P1 = Null
Begin
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
WHERE @P1 = UID
End
I have included the stored procedure.
Can anyone help me out here??
Mick
December 17, 2008 at 4:27 am
you should have this.
IF @P IS NULL
rather than
IF @P = NULL
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 4:36 am
This didnt work any other ideas?
Mick
December 17, 2008 at 4:37 am
NULL is an anknown value, so when ever you compare null to another value, the results will be falls. For example - if you'll run the script bellow, none of the select statements will be executed:
declare @i int
set @i = 1
if @i = NULL
select '@i = null'
if @i <> NULL
select '@i <> null'
if null=null
select 'null = null'
if null <> null
select 'null <> null'
In your code you have an if statement that compares your parameter to NULL. This if statement will always be evaluated as false, so the select statement will not be executed. Instead of using equal sign, you should use the IS NULL operator. By the way you should also change the part in the select statement and not just the part in the if statement.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2008 at 4:41 am
Hi
You dont need a where clause since you are passing null
if @P1 IS Null
Begin
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
End
Anyway your where clause is wrong - "WHERE @P1 = UID".
It should be "WHERE UID = @P1".
"Keep Trying"
December 17, 2008 at 4:42 am
michael.breen (12/17/2008)
This didnt work any other ideas?Mick
You didn't specify what didn't work. I guess that the select statement was executed, but you didn't get any results. You should also modify the select statement. Instead of WHERE @P1 = UID, you should use
WHERE UID IS NULL.
If this doesn't work please explain what isn't working - do you get an error message? do you get different results then you expected? etc'. Also post the code that you are using after the modification.
WHERE @P1 = UID
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2008 at 4:51 am
Adi
Sorry if I seam very stupid as this is maybe straight forward for you but i have made the changes:
USE [MobilePhones]
GO
/****** Object: StoredProcedure [dbo].[select_NewRequest] Script Date: 12/17/2008 10:51:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[select_NewRequest] @UID int AS
if @UID IS NULL
Begin
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
end
when I execute this it ask for a parameter for @UID and when I provide a parameter the result is "command(s) completed successfully"
December 17, 2008 at 5:07 am
michael.breen (12/17/2008)
Adiwhen I execute this it ask for a parameter for @UID and when I provide a parameter the result is "command(s) completed successfully"
Yes, only when you provide a parameter, the SELECT statement is executed, because of the IF @UID IS NULL. If you provide you want to display that one record (then it should be IF @UID IS NOT NULL), but what do you want when you leave the @UID blank? In this last case, the procedure should show nothing now.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 17, 2008 at 5:30 am
I have now made the changes as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[select_NewRequest] @UID int AS
if @UID IS NOT NULL
Begin
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
end
when I now execute the procedure inclusive of a parameter it returns all the records. When I dont include a parameter it asks for the parameter.
What i want is when the @UID has no parameter provided it returns all the records and when @UID is provided with a parameter it only returns that specific record.
Mick
December 17, 2008 at 5:34 am
here are two solutions
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[select_NewRequest] @UID int AS
if @UID IS NOT NULL
Begin
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
WHERE [UID] = @UID
end
if @UID IS NULL
Begin
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
end
GO
OR
ALTER PROCEDURE [dbo].[select_NewRequest] @UID int AS
SELECT [UID]
,[StaffNumber]
,[BusinessReason]
,[Requirements]
FROM [MobilePhones].[dbo].[tblPersonRequestDetails]
WHERE [UID] = @UID OR @UID IS NULL
end
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 6:03 am
You can use the OR construct or even COALESCE
WHERE SomeValue = COALESCE(@Param,SomeValue)
But the OR constract and the COALESCE construct aren't going to perform terribly well because either one will cause a table scan. As you get more data, you'll get slower and slower performance.
What you're saying is, you want one query when no parameters are passed and a different query when parameters are passed. I'd suggest making a wrapper proc that takes the parameters and then calls two other procs based on the value of the parameter:
CREATE PROCEDURE WrapperProc
@Param int
AS
IF @Param IS NULL
BEGIN
EXEC NullProc
END
ELSE
BEGIN
EXEC NotNullProc @Param
END
GO
This will allow each procedure to come up with it's own execution plan that is most viable and will perform the best.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 10:01 am
michael.breen (12/17/2008)
AdiSorry if I seam very stupid as this is maybe straight forward for you but i have made the changes:
You didn't seam stupid and I hope that you didn't take my remarks in a negative way. I hope that you'll conitnue to use this site and ask more questions. As for your original problem - I was away from my computer for few hours and noticed that you already got an answer.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2008 at 10:49 pm
Hi
The point is if you have declared a parameter for a stored proc then it needs to be passed from your application.
"Keep Trying"
December 18, 2008 at 4:48 am
Got the Stored Procedure working thanks to all for your help.
Now I need convert one of the fields the SP returns to show the text data rather than the int details of the record does any have any ideas of this?
Mick
December 18, 2008 at 4:58 am
I'm going a a limb here and assume that this data in held in another table. Hit F1 in enterprise manager and do a search for inner join. That will tell you all you need to know to make that work.
You might also want to read up on aliasing.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply