Stored Procedure + Parameter list
-
Is there a way to have a stored procedure with any number of parameters, where the 1st parameter would be required and then the following being optional and if so how would I declare the optional parameters?
-
Arthur
Just set the rest of the parameters to default to NULL - and include logic within your procedure to deal with this. Look up CREATE PROCEDURE in Books Online for the syntax.
John
-
I guess I missed a part, that when the stored procedure runs there is a select statement that utilizes an IN clause such as
Create Proc spname
@i1 int,
@i2 int,
@i3 int
AS
Select name from tablename WHere i1 in (@i1, @i2, @i3, etc).
So your saying set like
@i2 = NULL,
@i3 = Null
and so forth?
-
Something like this?
DECLARE @Parm1 int
, @Parm2 int
, @Parm3 int
, @Parm4 int
SET @Parm1 = 1
SET @Parm2 = 2
SET @Parm3 = 3
DECLARE @Wherever TABLE (ID int, RowName varchar(50))
DECLARE @joined TABLE (Parm int)
INSERT INTO @Wherever values (1, 'Bob')
INSERT INTO @Wherever values (2, 'Ted')
INSERT INTO @Wherever values (3, 'Fred')
INSERT INTO @joined VALUES (@Parm1)
IF (@Parm2 IS NOT NULL)
BEGIN
INSERT INTO @joined VALUES (@Parm2)
END
IF (@Parm3 IS NOT NULL)
BEGIN
INSERT INTO @joined VALUES (@Parm3)
END
IF (@Parm4 IS NOT NULL)
BEGIN
INSERT INTO @joined VALUES (@Parm4)
END
SELECT a.ID, a.RowName FROM @Wherever AS a
JOIN @joined AS b
ON a.ID = b.Parm
------------------------------------------------------------
@joined is a table variable that is defined in your proc. The parameters to your proc are @Parm1, @Parm2, etc. Insert their values into @joined, then join with the base table.
Luck, Dave
There is no "i" in team, but idiot has two. -
What you need is a SP with single parameter. You'll supply all your values as delimited string to this SP.
Inside of SP you need to use special function to convert delimited string to table and join that table to your static table.
SELECT
FROM TableName T
INNER JOIN dbo.List_CharValues (@ListPArameter) L ON T.ColName = L.Val
Here is the function:
ALTER FUNCTION dbo.List_CharValues (
@List ntext, -- A delimiter-separated list of integer values, this parameter may be up to 2GB in length
@Delim nvarchar(20)
)
RETURNS @val TABLE (No int IDENTITY(1,1), Val nvarchar(400) )
AS
BEGIN
DECLARE @One tinyint SET @One = 1
DECLARE @TL int
SET @TL = DATALENGTH(@List) / 2
DECLARE @dl tinyint
SET @dl = DATALENGTH(@Delim) / 2
DECLARE @LeftPointer int, @RightPointer int, @SubStr varchar(50)
SET @LeftPointer = 0
WHILE @RightPointer < @TL OR @RightPointer IS NULL
BEGIN
SELECT @RightPointer = CHARINDEX(@Delim, SUBSTRING(@List, @LeftPointer+@One, @TL) )
+ @LeftPointer - @One
IF @RightPointer <= @LeftPointer
SELECT @RightPointer = @TL
SELECT @SubStr = SUBSTRING(@List, @LeftPointer + @One, @RightPointer-@LeftPointer)
INSERT INTO @val (Val)
SELECT @SubStr
SELECT @LeftPointer = @RightPointer + @dl
END
RETURN
END
GO
_____________
Code for TallyGenerator -
Nicely done... although I do hope no one actually tries to pass a billion characters worth of parameters
By the way... you hardcoded the 2's
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
1) They tried. The reason I was asked for "ntext" function because "varchar" option did not hold the whole string supplied from application.
Surprisingly performance was quite good.
2) I put up with this mistake because I believe next service pack will not change number of bytes in single unicode character.
_____________
Code for TallyGenerator -
Hi Arthur Lorenzini
U just use the following way for passing the optional parameter in the stored procedures..
CREATE PROC Procedure_Name
@iSQL INT = 0,
@dtWorkingDate DATETIME ,
-
True enough
, Serqiy, but if you want to name variables for flexibility, it is an error to name them after the actual value they contain rather than the value they represent."Feel the difference"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply