sqlstring - 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)

sqlstring

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2004-10-28 : 11:15:33

I have a sp where I am creating a 2 SQL String.
When I am executing the sp if first string return '0' I exec 2 string.
In my resultset I am getting to sets from string1 and string2. What do I need to do in order to get only 1 dataset back if I execute second string?
example:
...
EXEC sp_executesql @SQLString1
...
-- if exact match is not found
if @@rowcount = 0...EXEC sp_executesql @SQLString2

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 12:39:58

Could you build just one sqlstring ?
eg.

@SQLString1 = '
if exists( ... )
select ...'

EXEC sp_executesql @SQLString

rockmoose

sardinka
Posting Yak Master

142 Posts

Posted - 2004-10-28 : 13:29:05

Can you give me an example? I don't understand your asnwer.
Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 14:22:16

Simplified example...

declare @sqlstring nvarchar(4000)

-- returns no rows
set @sqlstring = N'
if exists( select 1 where 1 = 0 )
	select 1'
exec sp_executesql @sqlstring

-- returns rows
set @sqlstring = N'
if exists( select 1 where 1 = 1 )
	select 2'
exec sp_executesql @sqlstring

rockmoose

sardinka
Posting Yak Master

142 Posts

Posted - 2004-10-28 : 14:43:24

I am so sorry to bother your again... but I still don't understand...
Below is some sql from sp:
Declare @SQLString as varchar(1000)
SET @SQLString ='Select
[ID],
[Name],
[LName] ,
[FName]
SET @SQLString=@SQLString + 'FROM employees'
SET @WHEREclause = @WHEREclause + '(LName ='''+ @LName+ ''') and '.....
exec sp_executesql @sqlstring...
if @@rowcount>0
set @SQLString =''
set @SQLString ='Select
[ID],
[Name],
[LName] ,
[FName]
SET @SQLString=@SQLString + 'FROM table2
SET @WHEREclause = @WHEREclause + '(LName ='''+ @LName+ ''') and '.....
Begin
END

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-28 : 22:29:40

something doesn't seem right with your query.

quote:
Originally posted by sardinka

I am so sorry to bother your again... but I still don't understand...
Below is some sql from sp:
Declare @SQLString as varchar(1000)
SET @SQLString ='Select
[ID],
[Name],
[LName] ,
[FName]
SET @SQLString=@SQLString + ' FROM employees'
SET @WHEREclause = @WHEREclause + '(LName ='''+ @LName+ ''') and '.....

exec sp_executesql @sqlstring...
if @@rowcount>0
begin
set @SQLString =''
set @SQLString ='Select
[ID],
[Name],
[LName] ,
[FName]
SET @SQLString=@SQLString + 'FROM table2
SET @WHEREclause = @WHEREclause + '(LName ='''+ @LName+ ''') and '.....
end

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 06:40:38

Clearer ?

DECLARE @SQLString NVARCHAR(4000)
DECLARE @LName VARCHAR(35)
SET @LName = 'myLName'
SET @SQLString = '
IF EXISTS( 	SELECT * FROM employees
		WHERE LName = ' + @LName + '
		AND .... )
SELECT
	[ID],
	[Name], 
	[LName],
	[FName]
FROM
	table2
WHERE
	LName = ' + @LName + '
	AND ....'

PRINT @SQLString
-- EXEC sp_executesql @sqlstring

Only use dynamic SQL when absolutely necessary,
What is the problem you are trying to solve ?,
maybe you could write a stored proccedure that takes @LName, ... as parameter(s).

Give us some more details of you problem, there could be alternative solutions.

rockmoose

sardinka
Posting Yak Master

142 Posts

Posted - 2004-11-01 : 14:56:57

I am trying to build a search query based on entered or not entered params from user. Dependents of the first result if any rows for exact match show if not do search based on name.
Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-01 : 15:03:02

Post what you got, we might be able to help you further..

rockmoose

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-01 : 21:36:22

the BEGIN-END placement didn't work?

--------------------
keeping it simple...

sardinka
Posting Yak Master

142 Posts

Posted - 2004-11-02 : 08:41:13

Below is my sp. The way I wanted is if exists display data with DOB if not display without DOB.
-----------------------------
CREATE procedure dbo.sp_Seacrh
@LName varchar(20)=Null,
@FName varchar(17)=Null,
@ID varchar(10)=Null,
@TId varchar(20)=Null
AS
DECLARE @SQLString as nvarchar(4000)
DECLARE @WHEREClause as varchar(2000)
SET @SQLString=''
SET @WHEREClause=''
set @SQLString=''
SET @WHEREclause=''
SET @SQLString ='
Select
s.id ,
s.LName ,
s.FName ,
s.DateOfBirth '
SET @SQLString=@SQLString + 'FROM table1 s Join table2 g ON s.GID=g.GID'
IF RTrim(@TId) <> ''
IF RTRIM (left(@TId,1)) = 8
BEGIN
Set @SQLstring = @SQLstring + ' JOIN table3 e ON s.sid = e.sID
AND e.EID='+ @TId + ' '
END
IF RTRIM (left(@TID,1)) = 9
BEGIN
Set @SQLstring = @SQLstring + ' JOIN table4 e ON s.sid = e.sID
AND e.WID='+ @TID + ' '

END
SET @WHEREclause = @WHEREclause + '(s.LName ='''+ @LName+ ''') and '
SET @WHEREclause = @WHEREclause + '(s.FName ='''+ @FName+''')and '
SET @WHEREclause = @WHEREclause + '( s.sID='''+ @id+''')and '
IF @WHEREclause <> ''
BEGIN
SET @WHEREclause = ' WHERE ' + @WHEREclause
SET @SQLstring = @SQLstring + @WHEREclause
SET @SQLstring =@SQLstring + ' ( s.GID='''+ @GID+ ''')'

END
set @SQLString = '
IF EXISTS ( '+ @SQLstring + ')'
--Print @SQLstring

SET @SQLString=''
SET @WHEREClause=''
SET @SQLString ='Select
s.LName ,
s.FName
SET @SQLString=@SQLString + 'FROM table1 s '
IF RTRIM(@LName)<>''
BEGIN
IF RTrim(@WHEREclause) <> ''
BEGIN
SET @WHEREclause = @WHEREclause + ' AND '
END
SET @WHEREclause = @WHEREclause + '(s.LName like ''' + @LName + '%'')'
END
IF RTRIM(@FName)<>''
BEGIN
IF RTrim(@WHEREclause) <> ''
BEGIN
SET @WHEREclause = @WHEREclause + ' AND '
END
SET @WHEREclause = @WHEREclause + '(s.FName Like ''' + @FName + '%'')'
END

IF @WHEREclause <> ''
BEGIN
SET @WHEREclause = ' WHERE ' + @WHEREclause
SET @SQLstring = @SQLstring + @WHEREclause
SET @SQLstring =@SQLstring + ' and ( s.GID='''+ @GID+ ''')'

END

--Print @SQLstring
EXEC sp_executesql @SQLString

GO

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-02 : 09:16:22

Ok,

I have not implemented any of the join logic for the @TID parameter,
but maybe this will give you some ideas:

CREATE PROCEDURE dbo.sp_Seacrh_nodynamicsql
	@LName varchar(20) 	= null,
	@FName varchar(17) 	= null,
	@ID varchar(10) 	= null,
	@TId varchar(20) 	= null	-- Ok I have not bothered with this logic // rockmoose
AS


-- 1. retieve the records with DOB
Select
	s.id,
	s.LName,
	s.FName,
	s.DateOfBirth
from
	table1 s
where
	s.DateOfBirth <> ''	-- must have DOB registered
	and patindex(coalesce(@LName,'_')+'%',s.LName) > 0
	and patindex(coalesce(@FName,'_')+'%',s.FName) > 0
	and s.id = coalesce(@ID,s.id)

-- 2. retieve the records without DOB
Select
	s.id,
	s.LName,
	s.FName
from
	table1 s
where
	isnull(s.DateOfBirth,'') = ''	-- no DOB registered
	and patindex(coalesce(@LName,'_')+'%',s.LName) > 0
	and patindex(coalesce(@FName,'_')+'%',s.FName) > 0
	and s.id = coalesce(@ID,s.id)

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 09:52:58

this is definitely WAY overcomplicated. start with something as basic as this:


select 
  <what you need to return>
from 
  <your entire FROM clause here, plus joins>
where
  FName LIKE ISNULL(@FName,'') + '%' AND
  LName LIKE ISNULL(@LName,'') + '%' AND
  ID LIKE ISNULL(@ID,'') + '%'

That's it! build from that approach. when your data is VARCHAR that you are potentially filtering by, the LIKE operator is the way to go.

I have set up the WHERE clause with this condition:

1) leave a parameter NULL to ignore that column in the filter
2) put a value in the paramter, and it will match the first characters of that parameter

That seemed to be the logic you required.

- Jeff

sardinka
Posting Yak Master

142 Posts

Posted - 2004-11-02 : 10:42:37

I think I am confused every one. I need to retrive record with DOB if extaxt match found based on Lname,fname,tid if not found closer match based on like statement for Lname and fname and display without DOB.
Some times I may have a Tid and some times not.
based on what user entered

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-02 : 10:57:35

All you are talking about is ranking, why don't you just drop all of the sql-string mess and write a few sql statements. Something like:


Create Table #myTable 
(
	id int identity(1,1),
	fName varchar(100),
	lName varchar(100)
)

Insert Into #myTable
Select 'William', 'Thacker'
Union All Select 'Kelly', 'Conehead'
Union All Select 'Bill', 'Gates'
Union All Select 'Kelly', 'Johnson'

Declare @results table (id int, rank int)

Declare @id int,
	@fName varchar(100),
	@lName varchar(100)

Select @id = 3, @fName = null, @lName = null
--Select @id = null, @fName = 'Will%', @lName = null
--Select @id = null, @fName = 'kelly', @lName = null
--Select @id = null, @fName = 'Will%', @lName = 'Conehead'
--Select @id = null, @fName = 'Will%', @lName = 'Conehead'
--Select @id = 4, @fName = 'Will%', @lName = 'Conehead'

	Insert into @Results Select id, rank=100 From #myTable Where id = @id

	Insert into @Results Select id, rank=49 From #myTable Where fName = @fName
	Insert into @Results Select id, rank=49 From #myTable Where lName = @lName

	Insert into @Results Select id, rank=24 From #myTable Where fName like @fName
	Insert into @Results Select id, rank=24 From #myTable Where lName like @lName

Select 
	A.*
From #myTable A
Inner Join 
	(
	Select top 1 with ties id, rank = sum(rank) 
	From @results Group By Id Order By rank Desc
	) B
On A.id = B.id

Drop Table #myTable

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 11:33:51

Well, hopefully in all of the responses you've gotten, you've seen many different ways to do what you need to, without using dynamic SQL and all that.

Another way of doing what you need is like this:


select * from
(
  select 
    <what you need to return>,
    case when FName=@Fname then 1 else 0 end +
    case when LName=@LName then 1 else 0 end +
    case when ID=@ID then 1 else 0 end as MatchRank
  from 
    <your entire FROM clause here, plus joins>
  where
    FName LIKE ISNULL(@FName,'') + '%' AND
    LName LIKE ISNULL(@LName,'') + '%' AND
    ID LIKE ISNULL(@ID,'') + '%'
) a
order by MatchRank DESC

That will return all rows that match, depending on the paramters entered. Exact matches will be returned first, followed by partial matches.

Something like that should give you exactly what you need. Obviously you will need to tweak things a little.

Does all this help? Please don't overcomplicate things by building SELECT statements dynamically if you don't need to.

- Jeff

sardinka
Posting Yak Master

142 Posts

Posted - 2004-11-03 : 09:29:22

Jeff. I got the idea what about if I need to select different columnd if MatchRank value is different?
thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 10:15:48

quote:
Originally posted by sardinka

Jeff. I got the idea what about if I need to select different columnd if MatchRank value is different?
thanks

?? Select all the columns you need. Do not change this dynamcially in your SQL. if you need to display different columns to the user, you do that in your report or your web page or whatever you need.

if you did need to use sql, and return, say a single column called "Result" that has data potentially from different columns, you can use a CASE:

SELECT CASE @ShowColumn WHEN 1 THEN FName WHEN 2 THEN LName WHEN 3 THEN City END as Result
FROM ...

But do stuff like this in your presnetation layer, not your DB layer. The database's job is to return the data. the presnetation layer's job is to show it to the user however it needs to look (i.e., formatting, hiding columns, etc)

Does this make sense and/or help at all?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources