Parameter Stored Proc Poblem - 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

Transact-SQL (2000)

Parameter Stored Proc Poblem

Author

Topic

harrisw48
Starting Member

34 Posts

Posted - 2005-10-31 : 07:57:22

Im new to SQL and cant understand why the code below doesnt work

Create PROCEDURE Search @Lookfor char
as
select [EXPERIANUK\Harrisw].[NBD MATCH].[Location_Trading Name At Location]
from [EXPERIANUK\Harrisw].[NBD MATCH]
where [EXPERIANUK\Harrisw].[NBD MATCH].[Location_Trading Name At Location]
like '%' + @lookfor + '%'
GO

The wild card doesnt seem to be working, it looks like its bringing everything back.

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-31 : 08:01:09

I can't see anything wrong in this stored procedure. Please tell us what you passing and what you getting in result.

Regards
Sachin Samuel

harrisw48
Starting Member

34 Posts

Posted - 2005-10-31 : 08:07:19

If I try and pass anything it returns everything and ignores the wildcard

I run it by

exec search bu

bu being the wildcard Im looking for

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-31 : 08:26:00

what I can see from your query is that u are trying to append the parameter in the query.

This will work if u execute this as below.

Create PROCEDURE Search @Lookfor char
as
DECLARE @STR VARCHAR(8000)

SET @STR='select [EXPERIANUK\Harrisw].[NBD MATCH].[Location_Trading Name At Location] from [EXPERIANUK\Harrisw].[NBD MATCH] where [EXPERIANUK\Harrisw].[NBD MATCH].[Location_Trading Name At Location] like ''%' + @lookfor + '%'''
GO

As you are appending the parameter in the query, its a dynamic one and for this you will require to execute this using execute statement or sp_Executesql.

Regards
Sachin Samuel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-02 : 02:27:52

you don't need to use sp_executesql yet. This is not a dynamic sql.

Compare your codes with below

create procedure mysearch
@lookfor varchar(10)
as
begin
select name from sysobjects
where name like '%' + @lookfor + '%'
and type = 'U'
end
go

exec mysearch 'sys'

[KH]

Kristen
Test

22859 Posts

Posted - 2005-11-02 : 03:28:21

[CODE]
Create PROCEDURE Search @Lookfor char
[/CODE]
You haven't specified a size for the @Lookfor parameter - it will default to one!

Assuming it is of variable length then a parameter of datatype varchar() parameter would be better than one of char()

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources