Help with syntax plase - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with syntax plase

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-07 : 09:09:43

I need help to put the symbol % either side of the variable Deptstr in the following. Deptstr is a string variable -

SQLstr = SQLstr + "Dept = '" + Deptstr + "'"

I want the line to read.

SQLstr = SQLstr + "Dept Like '" + % Deptstr % + "'"

Many thanks

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-03-07 : 09:15:55

is that you want

SQLstr = SQLstr + "Dept Like ' %" + Deptstr + "%'"

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-07 : 09:16:13

Oye...

Dynamice SQL

Where are you doing this from....

How many possible combinations in the predicate do you have?

Brett

8-)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-07 : 09:28:38

I have 2 dropdownlists and 5 textboxes on a web page that serve as search criteria. So far I have this in the search button click event in my asp.code and I am passing the WHERE clause to my sp. It seems to be working now though I haven't added all fields yet.

Dim SQLstr As String
Dim Deptstr As String
Dim Divstr As String
Dim Sectstr As String
Dim Surnamestr As String
Dim Firstnamestr As String

If Len(Me.DeptDDL.SelectedValue) > 0 Then
Deptstr = Me.DeptDDL.SelectedValue
SQLstr = SQLstr + "Dept Like '" + "%" + Deptstr + "%" + "'"
If Len(Me.DivisionDDL.SelectedValue) > 0 Then
Divstr = Me.DivisionDDL.SelectedValue
SQLstr = SQLstr + " and Division Like '" + "%" + Divstr + "%" + "'"
If Len(Me.SectDDL.SelectedValue) > 0 Then
Sectstr = Me.SectDDL.SelectedValue
SQLstr = SQLstr + " and Sect Like '" + "%" + Sectstr + "%" + "'"
End If
If Len(Me.txtSurname.Text) > 0 Then
Surnamestr = Me.txtSurname.Text
SQLstr = SQLstr + " and Surname Like '" + "%" + Surnamestr + "%" + "'"
End If
If Len(Me.txtFirstnames.Text) > 0 Then
Firstnamestr = Me.txtFirstnames.Text
SQLstr = SQLstr + " and Firstnames Like '" + "%" + Firstnamestr + "%" + "'"
End If
End If
End If

here's the sp

CREATE PROCEDURE MyProc (@WHEREClause varchar(1000))
AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1000)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM tblFileRequests WHERE "
+ @WHEREClause

-- Execute the SQL statement
EXEC(@SQLStatement)

GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-07 : 09:44:08

It's really not a good idea to pass a where clause to an SP. Is there a reason you're not passing all the search values individually as input parameters to the SP and letting your SP control the statemnt?

Be One with the Optimizer
TG

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-07 : 09:51:38

Well, I couldn't work that one out (see my post earlier re Wildcards). Despite the suggestions I couldn't get the sp to run. My user may not enter a value in every search textbox and I was running into problems with zero lenth strings and null values. I've amended my code to reflect this now.

Dim SQLstr As String
Dim ArgCount As Integer
Dim Deptstr As String
Dim Divstr As String
Dim Sectstr As String
Dim Surnamestr As String
Dim Firstnamestr As String

If Len(Me.DeptDDL.SelectedValue) > 0 Then
If ArgCount > 0 Then
SQLstr = SQLstr & " and "
End If
Deptstr = Me.DeptDDL.SelectedValue
SQLstr = SQLstr + "Dept Like '" + "%" + Deptstr + "%" + "'"
ArgCount = ArgCount + 1
End If
If Len(Me.DivisionDDL.SelectedValue) > 0 Then
If ArgCount > 0 Then
SQLstr = SQLstr & " and "
End If
Divstr = Me.DivisionDDL.SelectedValue
SQLstr = SQLstr + "Division Like '" + "%" + Divstr + "%" + "'"
ArgCount = ArgCount = 1
End If
If Len(Me.SectDDL.SelectedValue) > 0 Then
If ArgCount > 0 Then
SQLstr = SQLstr & " and "
End If
Sectstr = Me.SectDDL.SelectedValue
SQLstr = SQLstr + "Sect Like '" + "%" + Sectstr + "%" + "'"
ArgCount = ArgCount + 1
End If
If Len(Me.txtSurname.Text) > 0 Then
If ArgCount > 0 Then
SQLstr = SQLstr & " and "
End If
Surnamestr = Me.txtSurname.Text
SQLstr = SQLstr + "Surname Like '" + "%" + Surnamestr + "%" + "'"
ArgCount = ArgCount = 1
End If
If Len(Me.txtFirstnames.Text) > 0 Then
If ArgCount > 0 Then
SQLstr = SQLstr & " and "
End If
Firstnamestr = Me.txtFirstnames.Text
SQLstr = SQLstr + "Firstnames Like '" + "%" + Firstnamestr + "%" + "'"
ArgCount = ArgCount = 1
End If

If SQLstr = "" Then
Exit Sub
End If

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources