November 12, 2013 at 9:14 am
Hi All...
I have a question regarding calling SP with optional parameters...
I have created a SP with definition like this
CREATE PROCEDURE [dbo].[GetPerson] @RecordNumber VARCHAR(16) = NULL ,
@PerNumber VARCHAR(9) = NULL ,
@PerType VARCHAR(2) = NULL ,
@ActiveOnly BIT =null ,
@ActiveDate datetime =null
I need to be able to call this SP in two ways
either by passing RecordNumber i.e. first parameter
or by passing PerNumber/PerType combination.. i.e. 2nd parameter and 3rd paramater
The last 2 paramaters are applicable in both calls
But when I call this SP like this
exec [dbo].[GetPerson] '123',1,'10/10/2013'
it does assignment like this
RecordNumber = '123'
PerNumber = 1
PerType = 10
instead it should be assigning like this:
RecordNumber = '123'
Active= 1
ActiveDate= 10/10/2013
I don't get any results....
Can anyone please suggest something on how can I call or take care of optional parameters so that i calls properly based on parameters passed....
CREATE TABLE [Person](
[RecNo] [char](16) NOT NULL,
[PerNo] [char](9) NOT NULL,
[Pertype] [char](2) NOT NULL,
[Active] [int] NULL,
[ActiveDate] [datetime] NULL
)
INSERT INTO [Person]
SELECT '123','11','01',1,'2013-10-10'
UNION ALL
SELECT '345','11','02',1,'2013-11-10'
UNION ALL
SELECT '456','12','01',1,'2013-9-10'
UNION ALL
SELECT '789','12','02',1,'2013-8-10'
UNION ALL
SELECT '234','13','01',1,'2013-9-9'
UNION ALL
SELECT '678','13','02',1,'2013-8-8'
UNION ALL
SELECT '1234','15','01',0,'2013-9-9'
UNION ALL
SELECT '1678','15','02',0,'2013-8-8'
select * from Person
here is SP create statement:
CREATE PROCEDURE [GetPerson] @RecordNumber VARCHAR(16) = NULL ,
@PersonNo VARCHAR(9) = NULL ,
@PersonType VARCHAR(2) = NULL ,
@ActiveOnly BIT = NULL ,
@ActiveDate datetime = NULL
AS
BEGIN
print 'hello'
SET @ActiveDate = ISNULL(@ActiveDate, convert(varchar(10), GETDATE(),101) )
SET @ActiveOnly = ISNULL(@ActiveOnly, 0)
IF (@RecordNumber IS NULL AND @PersonNo IS NULL AND @PersonType IS NULL)
OR (@PersonNo IS NULL AND @PersonType IS NOT NULL)
OR (@PersonNo IS NOT NULL AND @PersonType IS NULL)
BEGIN
print 'go ifff line'
--Get info
RETURN
END
ELSE
print 'go else line'
print '@RecordNumber = ' + @RecordNumber
print '@PersonNo = '+@PersonNo
print '@PersonType = '+@PersonType
print '@ActiveOnly = '+ cast(@ActiveOnly as varchar)
print '@ActiveDate = '+ cast(@ActiveDate as varchar)
-- this is how i am sing the 3 optinal paramaters to get data from person table
IF NOT EXISTS( SELECT 1
FROM Person
WHERE (@RecordNumber IS NULL OR RecNo = @RecordNumber)
AND (@PersonNo IS NULL OR PerNo = @PersonNo)
AND (@PersonType IS NULL OR Pertype = @PersonType)
AND Active = @ActiveOnly
AND ActiveDate=@ActiveDate
)
BEGIN
print 'go return'
RETURN
END
END
ANY help on this is Appreciated.....
Thanks
Thanks [/font]
November 12, 2013 at 9:32 am
You can call the procedure like this:
exec [dbo].[GetPerson] @RecordNumber = '123', @ActiveOnly = 1, @ActiveDate = '10/10/2013'
November 12, 2013 at 9:40 am
Hi,
No , I can't do this, as its been called from a DOT NET program and they are just passing parameters and calling it...
Any other way to handle the optional parameters inside...
Thanks
Thanks [/font]
November 12, 2013 at 10:09 am
You can include Nulls for the two parameters not being used when you call it.
If that's not possible either, then I think you'll need to write another stored procedure as a wrapper with just the parameters you want, and have that one call the original procedure adding in the null parameters or naming the parameters as above.
November 12, 2013 at 10:13 am
I suppose you could change your procedure to have local variables, and then try to add logic that would guess which parameters get mapped to which local variables based on the number of non-null parameters provided... but that's just yucky, unreliable, and a maintenance nightmare.
November 12, 2013 at 10:25 am
I have .NET pages that call stored procedures where I specify the parameter values by name and they work with no problems. Since some parameters are optional and have default values, you should specify the ones you want to pass by name and not rely on ordinal.
November 12, 2013 at 10:29 am
Looks like you need to test for the number of parameters that are NOT NULL and then redistribute the values based on that number (either 3 or 4 of them will be NOT NULL)
Note also that you need to "genericize" your parameters to be able to contain all data that might be in them. From the way I see it you need these parameters:
@Param1 VARCHAR(16) = NULL ,
@Param2 VARCHAR(9) = NULL ,
@Param3 VARCHAR(30) = NULL ,
@Param4 VARCHAR(30) = NULL
Note I have changed the size of Param3 and 4 because they MIGHT contain a long string that would be a DATETIME.
Inside your sproc you will need to declare and assign the 'actual' variables to be used by your logic:
@RecordNumber VARCHAR(16),
@PerNumber VARCHAR(9),
@PerType VARCHAR(2),
@ActiveOnly BIT ,
@ActiveDate datetime
If you have 3 NON-NULL input parameters then your assignments will go like this:
SELECT @RecordNumber = @Param1, @ActiveOnly = CAST(@Param2 as bit), @ActiveDate = CAST(@Param3 as datetime)
Otherwise this:
SELECT @PerNumber = @Param1, @PerType = @Param2, @ActiveOnly = CAST(@Param3 as bit), @ActiveDate = CAST(@Param4 as datetime)
Hopefully that makes sense! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 13, 2013 at 6:10 am
What Ed said above.
Check your values in your .net app. If present, add their corresponding params to the sproc call and that'll work.
Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply