October 26, 2006 at 3:50 pm
Hello,
I'm trying to create a parameter for a proc in which the value of the parameter could be expressed in one of three ways. The parameter is intended to filter data based on a char(30) field called 'svcName'. The parameter value needs to determine how the WHERE clause will be built. The three expressions that could go into the parameter are:
1. A range of values (A-K, L-T)
2. A character string with no period at the end.The code would implement LIKE and a '%' wildcard.
3. A character string that implements a period at the end. The code would use this as a string terminator.
I have not done anything with Dynamic SQL, and I don't know if the path I am going down with this is the right way to go. Anyway, I need for the WHERE clause to be created based on how the parameter value is expressed.
The follow is my first attempt at this. This can be tested by setting the @SvcNameValue in one of the following sample ways; 'A-K', 'Art', 'Art.'
Declare @SvcNameValue varchar(40)
Declare @BeginningAlpha char(1)
Declare @EndAlpha char(1)
Declare @PartialString varchar(5)
Declare @TerminatedString varchar(100)
Declare @WhereExpression char(54)
Declare @WhereClause varchar(125)
SET @BeginningAlpha = '' --first letter in fixed range
SET @EndAlpha = '' --second letter in fixed range
SET @PartialString = '' --string does not end in '.', or does not contain '-' with three characters
SET @TerminatedString = '' --string ends with '.'
SET @WhereClause = ''
SET @WhereExpression = 'svcName FROM dbo.CD_MonthlyMCPurchTE WHERE svcName '
--Set the main parameter
SET @SvcNameValue = 'A'
--Test the main parameter and set variables
IF SUBSTRING (@SvcNameValue,2,1) = '-' AND LEN(@SvcNameValue) = 3
BEGIN
SET @BeginningAlpha = LEFT(@SvcNameValue,1)
SET @EndAlpha = RIGHT(@SvcNameValue,1)
END
ELSE IF RIGHT(@SvcNameValue,1) = '.'
BEGIN
--Remove the period from the end of @SvcNameValue
SET @TerminatedString = SUBSTRING(@SvcNameValue,1,(LEN(@SvcNameValue)-1))
END
ELSE
BEGIN
SET @PartialString = @SvcNameValue
END
--Build the WHERE clause based on the test results
IF NOT @BeginningAlpha = '' And NOT @EndAlpha = ''
BEGIN
SET @WhereClause = @WhereExpression + 'BETWEEN' + ' ' + @BeginningAlpha + ' ' + 'AND' + ' ' + @EndAlpha
END
ELSE IF NOT @PartialString = ''
BEGIN
SET @WhereClause = @WhereExpression + 'LIKE' + ' ' + @PartialString
END
ELSE IF NOT @TerminatedString = ''
BEGIN
SET @WhereClause = @WhereExpression + '=' + ' ' + @TerminatedString
END
--Use @WhereClause string in WHERE clause
SELECT
svcName
FROM
dbo.CD_MonthlyMCPurchTE
WHERE
svcName = (Select @WhereClause)
The Statement builds a string, but it does not work in the WHERE clause. I've tried to add in single quotes for the variables so that the syntax is correct, but that hasn't worked. I wonder if this kind of thing would be a candidate for dynamic sql, or if there would be some other way I should approach this.
Please share your thoughts, and thank you for your help.
CSDunn
October 26, 2006 at 4:19 pm
I don't know why you would want dynamic sql. I hope I understand ur question. Here is a code based on pubs database.
Declare @SvcNameValue varchar(40)
Declare @FromString varchar(50)
Declare @ToString varchar(50)
SET @FromString = '' --first letter in fixed range
SET @ToString = '' --second letter in fixed range
--Set the main parameter
--SET @SvcNameValue = 'AX-HX'
SET @SvcNameValue = 'Roulet.'
SET @SvcNameValue = 's'
If CharIndex('-',@SvcNameValue)> 0
Begin
select CharIndex('-',@SvcNameValue)
SET @FromString = LEFT(@SvcNameValue,CharIndex('-',@SvcNameValue)-1)
SET @ToString = RIGHT(@SvcNameValue,CharIndex('-',@SvcNameValue)-1)
select @SvcNameValue,@FromString,@ToString
Select * from pubs.dbo.employee where left(lname,CharIndex('-',@SvcNameValue)-1) between @FromString and @ToString
End
Else
Begin
/* Check for '.' and if dot then remove dot and do pefect match */
If RIGHT(@SvcNameValue,1)='.'
Begin
select left(@SvcNameValue,len(@SvcNameValue)-1)
Select * from pubs.dbo.employee where lname = left(@SvcNameValue,len(@SvcNameValue)-1)
End
Else
Begin /* Wild Card */
Select * from pubs.dbo.employee where left(ltrim(rtrim(lname)),len(ltrim(rtrim(@SvcNameValue)))) = ltrim(rtrim(@SvcNameValue))
End
End
Let me know if this is what u want.
Thanks
Sreejith
October 26, 2006 at 4:31 pm
Thank you for the assistance. It occured to me that I could probably get away with this:
Declare @SvcNameValue varchar(40)
Declare @BeginningAlpha char(1)
Declare @EndAlpha char(1)
Declare @PartialString varchar(5)
Declare @TerminatedString varchar(100)
SET @BeginningAlpha = '' --first letter in fixed range
SET @EndAlpha = '' --second letter in fixed range
SET @PartialString = '' --string does not end in '.', or does not contain '-' with three characters
SET @TerminatedString = '' --string ends with '.'
--Set the main parameter
SET @SvcNameValue = 'A-K'
--Test the main parameter and set variables
IF SUBSTRING (@SvcNameValue,2,1) = '-' AND LEN(@SvcNameValue) = 3
BEGIN
SET @BeginningAlpha = LEFT(@SvcNameValue,1)
SET @EndAlpha = RIGHT(@SvcNameValue,1)
END
ELSE IF RIGHT(@SvcNameValue,1) = '.'
BEGIN
--Remove the period from the end of @SvcNameValue
SET @TerminatedString = SUBSTRING(@SvcNameValue,1,(LEN(@SvcNameValue)-1))
END
ELSE
BEGIN
SET @PartialString = @SvcNameValue +'%'
END
SELECT
svcName
FROM
dbo.CD_MonthlyMCPurchTE
WHERE
LEFT(svcName,1) Between @BeginningAlpha AND @EndAlpha
OR
svcName Like @PartialString
OR
svcName = @TerminatedString
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply