CODEWITHSUNDEEP
sql-server-2005stored-procedures
nischal
nischal

Reputation: 101

stored procedure with wildcard parameters

I've a table 

create table user (userId varchar(8) not null, userName varchar(8) not null)
   insert into user
     select 'NAME1','name1'
     union all
     select 'NAME2', 'name2'
     union all
     select 'NAME3','name3'

I've used stored procedure for wild card parameters as:

create procedure wildcard_name
@userName nchar(8)=  '%'
as
select * from user
where userName like @userName;

exec wildcard_name 'n%';

the exec statement is not giving any result,why?

Upvotes: 2

Views: 5439

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Did you try running it again? I suspect the exec call is part of the body of your procedure now. How about:

ALTER PROCEDURE dbo.wildcard_name
  @userName NVARCHAR(8) = '%'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT userId, userName
    FROM dbo.user
    WHERE userName LIKE @userName;
END
GO -- <-- this is important! It tells SSMS that your procedure has ended!

EXEC dbo.wildcard_name N'n%';

Bunch of other suggestions I would be remiss to not mention:

  • You should always specify the schema prefix when creating and calling objects. So CREATE PROCEDURE dbo.wildcard_name, EXEC dbo.wildcard_name, etc.
  • Hopefully your production code does not use SELECT *.
  • Highly recommend using nvarchar instead of nchar for your parameter.
  • Wrap your procedure body with BEGIN / END and don't be afraid to use indenting to make it much more readable.
  • You'll usually want to use SET NOCOUNT ON; to prevent n row(s) affected messages from interfering with your results.
  • NVARCHAR parameters should have an N prefix (though I'm confused why you're alternating between varchar and nchar in the first place - this is two shifts where I'd expect zero).
  • Depending on the collation (and whether you want the search to be case sensitive), you may need to change your where clause using the COLLATE clause.

EDIT this seems to work just fine for me, so please explain what you are doing differently (and does "did not work" still mean empty result, or something else?):

enter image description here

Upvotes: 3

Related Questions