Create a Query in SP with entered parameters

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)

Create a Query in SP with entered parameters

Author

Topic

turk_programmer
Starting Member

2 Posts

Posted - 2005-03-21 : 02:26:53

hello.
i have a question.
i want create a stored procedure for search.
my stored procedure get several @parameters and return select result.
can i declare a varchar type and create sql select with @parameters conditions into.
then exec that.

for example :
create storedprocedure dbo.test
(
@parameter1 int ,
@parameter2 int
)
AS
declare @val1 varchar
set @val1=' Select * from tbl1 where '
if @parameter1 =1
set @val1=@val1+' field1=true and '

if @parameter2=0
set @val1=@val1+' field2<10'
else if @parameter2=1
set @val1=@val1+' field2>10 '
else
set @val1=@val1+' field3=true '

///
then exec this query

plz tell me how can i do this.
thanks.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-21 : 05:14:26

create procedure dbo.test
(
@parameter1 int,
@parameter2 int
)
AS
declare @val1 varchar(8000)
set @val1 = 'Select * from tbl1 where '
if @parameter1 = 1 set @val1 = @val1 + ' field1 = 1 and '
if @parameter2 = 0 set @val1 = @val1 + ' field2 < 10'
else
if @parameter2 = 1 set @val1 = @val1 + ' field2 > 10'
else set @val1 = @val1 + ' field3 = 1'
exec(@val1)

turk_programmer
Starting Member

2 Posts

Posted - 2005-03-22 : 00:11:32

thank you my friend.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources