sql stored proc does not process input variables correctly

Question

sql stored proc does not process input variables correctly

svenk7

SSC Rookie

Points: 31
More actions
September 25, 2009 at 9:53 am

#98006

Hi, newbie here. Having a weird problem with sql stored proc. I have this generic proc I wrote to intake a fieldname and a table name and process it for invalid data. problem is at runtime it does the opposite. Its never happened before and I am wondering if its a problem in T-SQL or my code. Here is my code. Any help would be appreciated.
CREATE procedure [dbo].[prc_de_yn_generic_ced] @field_name varchar(30),
@table_name varchar(50)
as
declare @countint
declare @d_code varchar(500)
SET @d_code = (Select dcode from t1 where field_name = @field_name)
If @table_name = 'tw_1'
begin
set @count = (select count(*) from tw_1)
IF @count != 0
BEGIN
Insert into tw_error1
select*, @d_code
from tw_1
where @field_name is NOT NULL
AND ltrim(rtrim(@field_name)) <> ''
AND ltrim(rtrim(@field_name)) not in ('Y', 'N')
END
my prob is when I execute this proc it actually does the opposite of the conditions specified above. Any thoughts?
thanks.
sv.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

svenk7 (9/25/2009)
Insert into tw_error1 select*, @d_code from tw_1 where @field_name is NOT NULL AND ltrim(rtrim(@field_name)) ''
AND ltrim(rtrim(@field_name)) not in ('Y', 'N')
my prob is when I execute this proc it actually does the opposite of the conditions specified above. Any thoughts?

Hi sv,

I assume that it's the above code that isn't working for you. The problem is that it is doing what you told it to do, not what you really want it to do.

The @field_name is a parameter. It's value isn't null, it's not an empty string, and it's not Y/N. Therefore, you are getting every record in the table. Because you're not filtering on a field in the table. If you change it's value to be NULL, an empty string, or Y or N, then you should get NO records returned, because the where clause then fails.

However, you are on the right track. What you need to do is to put the value of @field_name into your query. To do that, you need to do some dynamic sql.

This should get you along the track.

declare @sqlCmd varchar(1000)

set @sqlCmd = '

Insert into tw_error1

select*, @d_code

from tw_1

where @field_name is NOT NULL

AND ltrim(rtrim(' + QuoteName(@field_name, char(39)) + ')) ''''

AND ltrim(rtrim(' + QuoteName(@field_name, char(39)) + ')) not in (''Y'', ''N'')'

print @sqlCmd

execute (@sqlCmd)

Note that in your existing code, every where that you had a single-quote character, it had to be doubled up. Also, my personal preference is to put the variable in single-quotes by use of the QuoteName function, where char(39) is the ascii code for the single-quote character.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

svenk7 SSC Rookie Points: 31 More actions · Answer 2

Thanks much. I tried it after tweaking it a bit. pulled the @d_code and @field_name out of the quotes and ran it. It still does the same thing. i.e. instead of returning the rows which met the criteria it returned the rows that didnt meet the criteria and had actually valid data in it.

Here is the updated code:

declare @derc_code varchar(500)

declare @sqlCmd varchar(1000)

SET @d_code = (Select D_code from t_D_codes_typ where field_name = @field_name)

If @table_name = 'tw1'

begin

set @sqlCmd = '

Insert into tw_error

select* ,' + @d_code + ' from tw1

where ltrim(rtrim(' + QuoteName(@field_name, char(39))+ ')) IS NOT NULL

AND ltrim(rtrim(' + QuoteName(@field_name, char(39)) + ')) not in (''Y'', ''N'')

AND ltrim(rtrim(' + QuoteName(@field_name, char(39)) + ')) '''''

print @sqlCmd

execute (@sqlCmd)

end

Not sure why.

thanks.

WayneS SSC Guru Points: 95461 More actions · Answer 3

Well, I think I figured it out... I goofed.

Replace all of those "QuoteName(@field_name, char(39))" pieces of code with just @field_name.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

svenk7 SSC Rookie Points: 31 More actions · Answer 4

Thanks, works now. I guess the trick was to enclose them in a sql statement.

will remember that. Does that work as well for date and time checks too?

Thanks

sv.

WayneS SSC Guru Points: 95461 More actions · Answer 5

Yes. Just remember to initially use the print statement to examine what is actually being generated; then you can just copy that and make sure it works right.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

svenk7 SSC Rookie Points: 31 More actions · Answer 6

Thanks much. I have another issue. I have some dates stored as varchar(50) in a work table which I need to first parse out the text data from it and then convert the dates to datetime fields to insert in another table.

But I keep getting the error: "Conversion failed when converting the varchar value '09-29-2009' to data type int."

Here is the code I use:

"CASE dt1

when 'NA' then cast ('01-01-1900' as datetime)

when (isdate(dt1)) then dt1

when dt1 then convert(datetime,ltrim(rtrim(prc_typ_dt)))

END"

Any help is greatly appreciated.

Thanks

sv.