How to make this stored procedure

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)

How to make this stored procedure

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-30 : 03:50:53

Hello,

I have a stored procedure

Create Procedure MyTest
@MyCon nvarchar(100)
As
Begin
select * from temptab + @MyCon
End

In myCon values may come like

where name='Manya'

or another value like

where id='123'

ie, I have to create a where clause according to the @Mycon value

is it possible?

Thanks
Ceema

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-30 : 03:55:01

using dynamic sql that is possible

but it is better to just pass the variable to be used in the where clause rather than making the filter dynamic, this will not only give better execution plans but will be safer with regards to sql injection
if the filter is really dynamic like in a datagrid or something, i suggest you just return the recordset and filter through in your apps, so you'll get a snapshot of your search data

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-30 : 04:12:21

or

You can use this method
http://www.sqlteam.com/item.asp?ItemID=2077

Madhivanan

Failing to plan is Planning to fail

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-30 : 05:29:24

Hello,

I have tried the link, but the same thing is not workng for me. Command is executing successfully, but not returing any result.

My stored procedure goes like

create procedure sp_Test1
@mWhere as nvarchar(100)
as
declare @Sql as nvarchar(100)
begin
set @Sql='select * from student'
Set @Sql= @SQL + ' where '
Set @SQL= @SQL + @mWhere
end

exec sp_test1 'senglishname like a%'

Please help me to find out whats wrong with this.

Thanks
Ceema

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-30 : 05:32:32

try this


create procedure sp_Test1
@mWhere as nvarchar(100)
as
declare @Sql as nvarchar(100)
begin
Select @Sql = 'select * from student Where ' + @mWhere
Exec(@Sql)
end

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-30 : 05:40:18

also execute the sp this way.

exec sp_test1 'senglishname like ''a%'''

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-30 : 06:21:36

Hello all,

Thank you so much, I tried it an dit's working fine.

Thank you so much

Ceema

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources