dynamic parameters - 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 parameters

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2005-06-08 : 13:07:46

I'm trying to call a stored procedure from VB.NET that filters out issues from an issue log. It should look something like this:

SELECT iss.*,ver.vernumber,type.typedesc
FROM issues iss
LEFT JOIN versions ver ON iss.fixedinverid = ver.verid
LEFT JOIN issuetypelut type ON iss.isstype = type.typeid
WHERE iss.projid = @param1
AND ver.verid = @param2
AND type.typeid = @param3

The problem is that I need to be able to filter on multiple versions and types, so for example, the last line would read:

AND type.typeid = @param3 OR type.typeid = @param4

But param4 won't exist if the user only selects one type to filter on. I'm pretty sure I can build the query in VB using conditionals and then pass the entire query, but it seems like there should be an easier way to do it.

TIA, Alex

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 13:14:25

will something like this work for you?


use northwind
declare @b varchar(5), @n varchar(5)
select @b = 'alfki'
select @b, @n

select * from orders
where customerid in (@b,@n)

Go with the flow & have fun! Else fight the flow

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2005-06-08 : 13:21:11

Ooh, that's better. But I'd still build the query in VB because there could be up to 100 parameters and it seems silly to declare that many when very few of them will get used most of the time. Or am I missing something?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 13:46:26

what do you mean by building a query in VB.NET when you said that you're using a stored procedure??

Go with the flow & have fun! Else fight the flow

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2005-06-08 : 14:08:42

I guess it might be pointless to do this, but what I meant was building the query in VB, passing the query as a parameter to the sproc, and then executing the query from the sproc (e.g. EXEC (@query). Is there any difference between that and just executing the query directly from VB?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 14:14:34

?!?!?!??!???
why on earth would you want to do that???

stored procedures are recommended for all access to your db.
but if you want to build the sql in the VB then execute it with sp_executesql with parameters.
look it up in BOL.

oh and there's realy no difference because you can do exec(@sql) from the app too...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources