dynamic query - 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)

dynamic query

Author

Topic

jzeil
Starting Member

4 Posts

Posted - 2002-09-06 : 16:09:48

Im working on my first stored procedure and need some guidance.

I would like to have a stored procedure where i pass in the WHERE
clause will be passed in as a parameter to the procedure.

The procedure im building is based on:
http://www.aspfaqs.com/webtech/062899-1.shtml

Basicly it comes down to... I dont know how to append a string passed in as a param to the query.

How would one go about this? If there is an example on the web please pass it on.

Jack

izaltsman
A custom title

1139 Posts

Posted - 2002-09-06 : 16:52:30

This might help:

http://sqlteam.com/item.asp?ItemID=4619

And you might even be able to avoid dynamic SQL all together:

http://sqlteam.com/item.asp?ItemID=2077

jzeil
Starting Member

4 Posts

Posted - 2002-09-06 : 17:23:51

These show how to dynamically change the values you are using in your query... or how to dynamically add chunks of a where clause in (which could do the trick for me).

But what i was more looking for was:
Is there a way to pass the where clause into a stored procedure? ie I would have already constructed it in my client code and when I call the stored procedure I just pass in the completed where clause.

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-06 : 17:28:16

You would use the Dynamic SQL examples to append the where clause that you're passing in. To pass them in, just define a large enough varchar parameter in your stored procedure. This article [url]http://www.sqlteam.com/item.asp?ItemID=2755[/url] talks about the basics of parameters in stored procedures, if you're not familiar with them.

jzeil
Starting Member

4 Posts

Posted - 2002-09-06 : 21:26:50

I dont think im explaining what I want to do well
These examples show how you could pass in a value and use it in a query. ie
Where name = @nameParm

what i want to do is pass in
"where name = 'john'"

ahould just have the store procedure do some thing like
select * from user @whereclause

my where clause is pretty hairy and I dont want a store procedure that has like 15 optional parameters. I just want to build the whole string for the where clause and just give it to the stored procedure.

Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-06 : 23:05:24

Try this.

USE Pubs
GO

DECLARE @StrSQL varchar(1000),
@StrSelect varchar(1000),
@StrWhereClause varchar(1000)

SET @StrSelect = 'SELECT * FROM Employee'
SET @StrWhereClause = ' WHERE lname = ''Cramer'''

SET @StrSQL = @StrSelect + @StrWhereClause

SELECT @strSQL

EXEC (@StrSQL)

Using sp_executesql is more efficient than using EXECUTE but this will work. You can find more info about sp_executesql and EXECUTE in SQL Server books online.

Edited by - ehorn on 09/07/2002 21:27:40

Edited by - ehorn on 09/07/2002 21:30:11

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-10 : 18:40:15

quote:

I dont think im explaining what I want to do well
These examples show how you could pass in a value and use it in a query. ie
Where name = @nameParm

what i want to do is pass in
"where name = 'john'"

Whether you are passing in a single value, or passing in the entire WHERE clause, it doesn't matter. You're still passing in a single parameter, but it'll just need to be a larger varchar for the whole WHERE clause. Either way, you still need to use Dynamic SQL to build the statement that will get executed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources