July 22, 2008 at 6:26 am
Hi Friends,
I am facing the problem in setting the parameter.
I have one parameter name INSerPackopt here i am passing the comparing operator(=,null,like).
I have one more parameter INSerPack here I have taken the output from Query and i wish that
the output that come from query will include these value also as explained in example
Eg.Suppose my query returns output
service pack 1,service pack 2 ,service pack 3
and I want that the output shown by textbox in the format
All
Custom(Like)
Service Pack 1
Service Pack 2
Service Pack 3
And when i pass 'ALL' all the data returned by the parameter
and when i select custom there opens a new text box from where i can match and retrieved data.
and when i select service pack 1 the value of only servic pack 1 will be retrieved
I have attached Two attachment and one more thing i have to do ll this things by Procedure only
Thanks
July 25, 2008 at 6:32 am
Hi Friends, I am facing the problem in setting the parameter. I have one parameter name INSerPackopt here i am passing the comparing operator(=,null,like). I have one more parameter INSerPack here I have taken the output from Query and i wish that the output that come from query will include these value also as explained in example
Eg.Suppose my query returns output
service pack 1,service pack 2 ,service pack 3
and I want that the output shown by textbox in the format
All
Custom(Like)
Service Pack 1
Service Pack 2
Service Pack 3
And when i pass 'ALL' all the data returned by the parameter, and when i select custom there opens a new text box from where i can match and retrieved data, and when i select service pack 1 the value of only servic pack 1 will be retrieved
I have attached Two attachment and one more thing i have to do ll this things by Procedure only
You will first need to modify your query that populates the first parameter, this query must return the additional option "Custom(Like)" along with the other options. For example, if you currently have a query that looks like this:
SELECT SP_Options FROM dbo.SP_Options_Table
Then you will probably have to change this to something like
SELECT 'Custom(Like)' AS ParamOption
UNION ALL
SELECT SP_Options AS ParamOption FROM dbo.SP_Options_Table
Now make sure that your parameter is a multi-value parameter and this will add in the option for All.
Second, you need to create a second parameter which is of string type and has a default of anything apart from NULL (for example N/A or * or Anything) mark this parameter as not allowing blanks or nulls.
Now on your main dataset stored procedure pass both of these parameters into the stored procedure.
Inside the stored procedure I would normally make a call to a function that will split the parameter string (remember that this is a multi-value and therefore will come into the stored procedure as one long string separated by commas).
My final select statement will have a WHERE clause at the end that will look something like:
WHERE (@Param1='Custom(Like)' AND Table.Column LIKE @Param2)
OR
(Table.Column IN (SELECT Element FROM dbo.utf_SplitString(@Param1,',')))
Just for completenes on this post, the following is the code I have used to create the UTF_SplitString function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[utf_SplitString]
(
@vchList varchar(8000) = '',
@vchDelimiter varchar(5) = ','
)
RETURNS @tblList TABLE (
IdentityID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Element nvarchar(255) NULL )
AS
BEGIN
DECLARE @intCurIdx int,
@intLastIdx int,
@intListLen int,
@vchValue nvarchar(255)
SELECT @intCurIdx = 1,
@intLastIdx = 1,
@intListLen = LEN(@vchList)
WHILE ( @intCurIdx BETWEEN 1 AND @intListLen )
BEGIN
SELECT @intCurIdx = CHARINDEX(@vchDelimiter,@vchList,@intLastIdx),
@intCurIdx = CASE WHEN @intCurIdx = 0
THEN @intListLen + 1 ELSE @intCurIdx END,
@vchValue = LEFT(SUBSTRING(@vchList,@intLastIdx,
@intCurIdx - @intLastIdx),255),
@vchValue = REPLACE(@vchValue,@vchdelimiter,'')
INSERT@tblList ( Element )
SELECT @vchValue
SET @intLastIdx = @intCurIdx + 1
END
RETURN
END
Now, if you don't want to use a multi-value parameter, you could do this just as well with a single select but by adding the ALL option into your original parameter query as follows:
SELECT 'ALL' AS ParamOption
UNION ALL
SELECT 'Custom(Like)'
UNION ALL
SELECT SP_Options AS ParamOption FROM dbo.SP_Options_Table
And then your WHERE Clause would look something like:
WHERE (@Param1='ALL')
OR
(@Param1='Custom(Like)' AND Table.Column LIKE @Param2)
OR
(Table.Column = @Param1)
Note that using this method will stop the end user being able to select more than one of the pre-defined values.
Sorry it's a bit long winded.
Good luck,
Nigel West
UK
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply