| Author |
Topic |
|
delpi767
Starting Member
11 Posts |
 Posted - 2009-10-25 : 12:55:12
|
| alter procedure getLname@lname char (26)as select * from customers where lname like @lname + '%'always returns zero records because lname is padded out to 26 characters.So, I started experimenting with trying to "Rtrim" @lname and couldn't figure out how to do so.I'm wondering whit is the proper systax for modifying a parameter once it is passed to a stored procedure?For example, how do I trim the trailing spaces from @lname before using it in my select statement?I do have this working using @lname varchar (26) but I'm still interested in how I would modify a parameter.Thanks,MacRegards,-dmd- |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-25 : 14:31:00
|
rtrim(@lname) gives you @lname without trailing spaces.ltrim(@lname) gives you @lname without leading spaces.ltrim(rtrim(@lname)) gives you @lname without leading and trailing spaces.But I am not sure if that was your question?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-25 : 16:19:48
|
| Declare @lname as varchar instead of char. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-25 : 17:00:51
|
quote:
Originally posted by robvolk
Declare @lname as varchar instead of char.
Yes, he did it!
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-25 : 17:03:10
|
 I need to read more closely. |
|
|
|
delpi767
Starting Member
11 Posts |
Posted - 2009-10-25 : 20:18:41
|
| Thanks everyone but I must have been too ambiguous with my question. Let me try again.The question"Is it possible to modify a parameter inside an sproc? I pass the parameter @lname to an sprocwhen it is passed @lname = "Fisher____" (some number of spaces on the end)I want to modify @lname so that it equals "Fisher" (right trimmed)Is this possible? If so, how is it done?Regards,-dmd- |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-25 : 23:36:09
|
I don't think that's your problem, because trailing spaces will be ignored. But to answer your question directly...WHERE LastName = RTRIM(@LastName) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-26 : 02:29:16
|
quote:
Originally posted by webfred
rtrim(@lname) gives you @lname without trailing spaces.ltrim(@lname) gives you @lname without leading spaces.ltrim(rtrim(@lname)) gives you @lname without leading and trailing spaces.But I am not sure if that was your question?
No, you're never too old to Yak'n'Roll if you're too young to die.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-26 : 09:52:12
|
right, but I still don't think that's his problem-- All of these return the record --declare @LastName char(26)set @LastName = 'Fisher'declare @t table (LastName char(26))insert @t values ('Fisher ')select * from @t where LastName = @LastName-------------------------------------------------------declare @LastName varchar(26)set @LastName = 'Fisher'declare @t table (LastName char(26))insert @t values ('Fisher ')select * from @t where LastName = @LastName-------------------------------------------------------declare @LastName varchar(26)set @LastName = 'Fisher'declare @t table (LastName char(26))insert @t values ('Fisher')select * from @t where LastName = @LastName-------------------------------------------------------You can modify the param in a Stored Proc just like any other variable -- but it will kill the query plan in most cases. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-10-26 : 19:04:12
|
| If the parameter is defined as CHAR(26) - no matter what you do with LTRIM/RTRIM, the parameter is going to be padded to fill the CHAR(26) definition.The only way to do this is to convert the variable to a VARCHAR - which will automatically remove the padded spaces. Something like:WHERE LastName Like CAST(@LastName AS VARCHAR(26)) + '%' |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-27 : 02:14:12
|
No that's not true.Do this to see:declare @yak char(26)set @yak='Fred'select @yak + 'the end'select rtrim(@yak) + 'the end'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-27 : 07:12:41
|
| That expression is not defined as char, so the rtrim works as expected. You still have the same problem as Jeff stated:declare @yak char(26)set @yak='Fred'select @yak + 'the end'select rtrim(@yak) + 'the end'set @yak=rtrim(@yak) -- trim off the spacesselect @yak + 'the end' -- they still appear on char variable |
|
|
|
|
using "like" in a stored procedure