Is it possible to pass a dynamic query to 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)

Is it possible to pass a dynamic query to SQL

Author

Topic

bciarcia
Starting Member

28 Posts

Posted - 2003-07-29 : 15:43:38

Please forgive my ignorance. I am trying to create a filter for a search using ASP. uses the filter, it will dump the results into a temporary table so that he/she can perform another search based on the filtered results. I have created the pages to do the filter, but it seems slow. I would like to make this into a stored procedure or a view so that it runs more efficiently. Here is an example of a query.

SELECT recno, employer, durationfrom, durationto, expirationdate, county, title FROM dbo.teachers
where 1=1 and (county = 'Fairfield' or county = 'Hartford') ORDER BY employer DESC

everything up to the "where 1=1" will always be the same. Everything after, is created when the filter is submitted.

Is there a way to pass the last part as a parameter to a stored procedure? I tried to create one, but kept getting an error, when I went to parse it. Is there a different way of doing this? Thanks in advance..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-29 : 15:49:22

What you are trying to do is called dynamic sql. You have to build the select statement by concatenating strings together. Then you have to execute it. Here is an example:


CREATE PROC usp_SomeProc
(@WherePart VARCHAR(7000)
AS

DECLARE @SQL VARCHAR(7000)

SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT recno, employer, durationfrom, durationto, '
SELECT @SQL = @SQL + 'expirationdate, county, title '
SELECT @SQL = @SQL + 'FROM dbo.teachers'

SELECT @SQL = @SQL + ' ' + @WherePart

EXEC (@SQL)

RETURN

----------------------------

Here is how to execute the stored procedure:


EXEC usp_SomeProc 'where 1=1 and (county = ''Fairfield'' or county = ''Hartford'') ORDER BY employer DESC'

If you do a forum search, you will find the PROs and CONs of dynamic sql plus more examples.

Tara

bciarcia
Starting Member

28 Posts

Posted - 2003-07-29 : 16:29:59

Great.. thank you so much..

bciarcia
Starting Member

28 Posts

Posted - 2003-07-31 : 10:19:36

hello, its me again.. ok.. I am trying to create a temporary table using that same stored procedure, but it doesn't seem to wanna make the table..

SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT recno, employer, durationfrom, durationto, '
SELECT @SQL = @SQL + 'expirationdate, county, title INTO #tempteach '
SELECT @SQL = @SQL + 'FROM teachers'

SELECT @SQL = @SQL + ' ' + @WherePart

when i execute it, it says it there were 59 rows affected, but then when i try to run a query on that table i get Invalid Object.. What am i doing wrong now??

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-31 : 11:08:04

I suggest you use a global temporary table ## instead.

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

bciarcia
Starting Member

28 Posts

Posted - 2003-07-31 : 11:19:24

What I'm trying to do is create a temporary table based on a users search criteria so that they could then do a refined search off of that table. If i did a global temporary table then wouldn't it cause a problem if more than one user do a search at the same time? One of them would get the others results, wouldn't they??

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-01 : 11:30:56

Do you have a login or a userid unique to each person?

Create a permannent table

CREATE TABLE myTABLE (
UserId varhcar(8)
, Return Reults colum(s)
)

Put the intial results in there

And just whittle away at that..

Brett

8-)

SELECT POST=NewId()

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 11:52:36

no.. i am just using a single login.. its for our intranet. didn't see a need to do that..

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 12:00:54

i can't seem to get it to create the temp table.. here is my code..

SP:

alter PROC Get_teachers
@WherePart VARCHAR(7000),
@tblvr varchar(30)
AS

DECLARE @SQL VARCHAR(7000),

@tblvr2 varchar(30)

set @tblvr2 = '##tempteach' + @tblvr
SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT recno, employer, durationfrom, durationto, '
SELECT @SQL = @SQL + 'expirationdate, county, title INTO ' + @tblvr2
SELECT @SQL = @SQL + 'FROM dbo.teachers'

SELECT @SQL = @SQL + ' ' + @WherePart

EXEC (@SQL)

RETURN

this is what I am executing to test it..

EXEC Get_teachers 'where 1=1 and (county = ''Fairfield'' or county = ''Hartford'') ORDER BY employer DESC','bciarcia'

this is the error:

Line 1: Incorrect syntax near 'dbo'.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-01 : 13:16:41

alter PROC Get_teachers
@WherePart VARCHAR(7000),
@tblvr varchar(30)
AS

DECLARE @SQL VARCHAR(7000),

@tblvr2 varchar(30)

set @tblvr2 = '##tempteach' + @tblvr
SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT recno, employer, durationfrom, durationto, '
SELECT @SQL = @SQL + 'expirationdate, county, title INTO ' + @tblvr2
SELECT @SQL = @SQL + ' FROM dbo.teachers' <- You need a space before FROM

SELECT @SQL = @SQL + ' ' + @WherePart

EXEC (@SQL)

RETURN

Owais

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 13:23:26

If you can't figure out why you are getting an error, you should PRINT out @SQL rather than trying to EXECUTE it.

PRINT @SQL

Tara

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 14:13:44

Dang!!!

How the heck did I miss that one.. Thank you all for your help. It works great..

One last question. If they decide to create a new filter, it will give them an error saying the temporary table already exists. Should i create a drop table within the procedure or within my asp page?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 15:09:35

If you create an object in a stored procedure and no longer need it, you should always drop it at the end of the stored procedure.

Did I understand your question correctly?

Tara

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 15:47:08

not entirely.. once the user creates that temporary table, they will have a choice;

1. do a search based on that temporary table

or

2. run a new filter (which would try to recreate that temporary table)

If they choose to do "2", it will throw an error saying they temporary table already exists.

So i guess my question would be, do i need to run another sp checking to see if that table exists and then drop it, or can i put that code in the beginning of the original sp to check for it, drop it (if it exists), then create a new one?

Does that make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 15:51:14

If they choose 2, then you should:


if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SomeTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table SomeTable
GO

CREATE TABLE SomeTable 
(
  Column1 INT NOT NULL,
  Column2 VARCHAR(50) NOT NULL
)GO

Tara

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 16:05:18

Im using global temporary tables.. I can't seem to drop them like that...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 16:08:35

A temporary table gets dropped once the stored procedure is done executing even if you don't specify the DROP statement.

Tara

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 16:09:27

this is what i tried doing.

ALTER PROC Get_teachers
@tblvr varchar(30),
@WherePart VARCHAR(7000)
AS

DECLARE @SQL VARCHAR(7000),

@tblvr2 varchar(30)

set @tblvr2 = '##tempteach' + @tblvr

IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects

WHERE name = @tblvr2 )

DROP table @tblvr2

SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT recno, employer, durationfrom, durationto, '
SELECT @SQL = @SQL + 'expirationdate, county, title INTO '
SELECT @SQL = @SQL + @tblvr2
SELECT @SQL = @SQL + ' FROM dbo.teachers '

SELECT @SQL = @SQL + ' ' + @WherePart

EXEC (@SQL)

RETURN

It doesn't seem to like the drop table part..

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 16:12:29

should i just be creating and dropping permanent tables instead?? or should i be looking at some other method of doing what i need??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-01 : 16:18:31

This part: DROP table @tblvr2
requires dynamic sql. You'll have to build the string here too.

Tara

bciarcia
Starting Member

28 Posts

Posted - 2003-08-01 : 16:26:09

Ughhhh.. I'm about to give up :o)

so you are saying, just add this line

set @tblvr2 = '##tempteach' + @tblvr

above the drop table statement??? I still get incorrect syntax near @tblvr2..

Ya know, I can code asp pages with no problem. i dont know why i am having such a hard time with this..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources