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

Input Parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-26 : 11:33:24

Osman TASKIRAN writes "i want to create a stored procedure with dynamic parameters.
example

create procedure example
@param1 int
@param2 string
@param3 date
@param4 float

select C1,C2,C3,C4,C5 from TestTable
where C1<>'-'
if @param1<>(0 or '' or null =default) then
(and C2=@param1)
if (@param2<>'') then
(and C3=@param2)
order by C1
go

but this codes doesnt work
please help me."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 12:44:04

Dynamic WHERE clause:

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

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-26 : 13:24:23

check this out, too:

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-26 : 13:34:45

How about...


  SELECT C1,C2,C3,C4,C5 
    FROM TestTable
   WHERE C1<>'-'
     AND C2 = CASE WHEN @param1 NOT IN (0,'',null) THEN @param1 ELSE C2 END
     AND C3 = CASE WHEN C2=@param1 THEN @param2 ELSE C3 END
ORDER BY C1

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 14:07:45

Did you get that to work Brett? I don't think that you can do that in a WHERE.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-26 : 14:15:43

[code]

USE Northwind
GO
DECLARE @x int

SELECT @x = 1000

SELECT * FROM Orders
WHERE OrderId > CASE WHEN @x IS NOT NULL THEN @x ELSE OrderId END

[/code]

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 14:16:46

Cool, never knew you could do that. Thanks!

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources