Dynamic query without dynamic SQl

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

Transact-SQL (2000)

Dynamic query without dynamic SQl

Author

Topic

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-05 : 10:56:43

I have a sp with a parameter with more values like
spTest '"Amsterdam","Paris","New York"'
i've created a temptable to solve this. But now the problem is when no parameters ar given (or wildcard)the query must give all locations. How do i realize that without the use of dynamic sql!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-05 : 11:29:49

Perhaps a count(*) on your temp table will help. If COUNT(*) = 0 then return all rows?

----------------
Shadow to Light

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-05 : 11:35:17

How do you mean, i can determine if the table is empty but how do i change the where statement?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-05 : 12:10:03

I am only guessing because I have no idea what your code looks like. Perhaps you could post the part of the script that's causing you a problem?

----------------
Shadow to Light

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 12:14:54

I think you want something like

CREATE PROC myPROC @x varchar(8000) = null
AS

IF ISNULL(@x,'TRUE') = 'TRUE'
BEGIN
SELECT * FROM myTable --No Where Clause
END
ELSE
BEGIN
-- All your work to decode your string
SELECT * FROM myTable l INNER JOIN codeTable r ON l.key = r.key
END

--Have a nice day....

Definetly NOT Tested

Brett

8-)

SELECT POST=NewId()

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-05 : 14:59:43

The problem in this solution is that there are more WHERE statements that uses variables(@y varchar(250),@Z varchar(250,)..,..,..) in this query and if i create it by the above example i have to write a lot of double code.
By the way it should even work on sql server 6.5

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 15:21:05

Stalknecht --

you gotta give us some more information. How about some examples? how about what you have now for a stored procedure ?

Is the user passing values as parameters, or WHERE clauses ?

Have you looked into accepting 1 parameter in which cities are enter as a comma-seperated string?

i.e.,

spTest 'Amserdam, Paris, NewYork' <-- all 1 parameter

??

Please give us some more information on what you are trying to accomplish. While you are at it, check this out:

http://www.sqlteam.com/item.asp?ItemID=11499

- Jeff

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-05 : 15:35:39

>>Is the user passing values as parameters, or WHERE clauses ?
The user is passing parameters that are used in the where clause
>>Have you looked into accepting 1 parameter in which cities are >>enter as a comma-seperated string?
Yes,

EXEC spMyProc 'Amsterdam, Paris, New York','','','Example,Second','','Single'

So the parameters could be an array of items or they are not given.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 16:05:24

So what do you want??????? Do you understand you are giving us NO information to work with?

Why are there multiple parameters? what are they for? are they all the same field? multiple fields? what does "Sample" refer to? are they all cities? are there other fields they can search?

(sorry .... had to let it out)

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-05 : 16:10:12

Brett's example answers the question. You need to pass the values in as one parameter, comma separated. Then set the one parameter to a default of NULL in your stored procedure, like in Brett's example, that way if the parameter isn't passed, it will use a NULL. Then in your stored procedure, you check if the parameter is NULL.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 16:49:52

I was going under the (mis?) assumption you were taking your string, parsing it, loading it in to a table variable or temp table, whatever, and joining to it.

[EDIT]

You should only use 1 string, unless of course it's for different predicates (it is, isn't it...tell him what he's just won johnnie!)

Is it bigger than a bread box?
[/EDIT]

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 16:50:56

quote:
Originally posted by jsmith8858

So what do you want??????? Do you understand you are giving us NO information to work with?

Why are there multiple parameters? what are they for? are they all the same field? multiple fields? what does "Sample" refer to? are they all cities? are there other fields they can search?

(sorry .... had to let it out)

- Jeff

Valium cocktail for Mr. Smith, please...

Brett

8-)

SELECT POST=NewId()

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-05 : 16:58:31

CREATE PROC myPROC
(
@x varchar(250) = null
@y varchar(250) = null
--And more
)
AS

--
--Code to create the temporary Tables here
--

if ISNULL(@x,'TRUE') = 'TRUE'
BEGIN
SELECT * FROM myTable
myTable.field2 in (select * from #tmpYTable2)
END
ELSE
BEGIN
SELECT
*
FROM
myTable
WHERE
myTable.field1 in (select * from #tmpXTable1) AND
myTable.field2 in (select * from #tmpYTable2)
End

--.........

--EXEC myPROC 'abc,efg,hij', '' , 'hello'

So the problem is what to do when no parameter is given. Brett's example does the trick but when the number of declared variables grows i have to write enormous double code in case statements

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 16:58:58

sorry ... got worked up .... I'm ok now ....

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-05 : 17:04:15

quote:
Originally posted by Stalknecht

So the problem is what to do when no parameter is given. Brett's example does the trick but when the number of declared variables grows i have to write enormous double code in case statements

You should not accept numerous parameters. That is the point that we are trying to make. You need to pass them into the stored procedure into ONE variable and have the values be comma separated. Otherwise, how are you going to know how many variables that you will need. If you go the multiple variable way, then you need to setup the variables so that they ALL accept NULLs.

Tara

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-05 : 17:16:50

Trying to simplify the question: I build an application where the user can select values from a number of "multiple-select comboboxes" i need to give these parameters for narrowing the query when they are selected and when not selected they must show all those records. How can i accomplish this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 17:23:51

1. put @String into temp table #t
2. put @String2 into temp table #t2
..etc ..

then:

select *
from
YourTable
WHERE (@String is null OR (f1 in (select f1 from #t))) AND
(@String2 is null OR (f2 in (select f2 from #t2))) and
(@String3 is null OR (f3 in (select f3 from #t3)))
..etc....

hopefully that makes sense.

- Jeff

Stalknecht
Starting Member

22 Posts

Posted - 2003-08-06 : 03:59:03

Thanks, Jeff.
youre example works!

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 09:09:15