Pass string to procedure.getting error

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 2005 Forums

Transact-SQL (2005)

Pass string to procedure.getting error

Author

Topic

profiler
Starting Member

7 Posts

Posted - 2010-01-16 : 11:05:38

hello,

I have a problem. I am getting an error.
here is my procedure.

ALTER PROCEDURE GetEmployee
(
@EmployeeCode nvarchar(500)
)

AS
Declare
@Query varchar(4000),
@Where varchar(4000)

select @Query='',@Where=''
Begin
Set @Query='select * from Employee '
End

Set @Where=' where '
if @EmployeeCode <>'-1'
set @Where=@Where+' EmployeeCode ='+str(@EmployeeCode )+' And'
if(LEN(@Where)>0)
Set @Where = SUBSTRING(@Where, 1, LEN(@Where)-5)

Set @Where=@Where+''
print (@Query+@Where)
Exec (@Query+@Where)

if i pass
exec GetEmployee '11xx'

it is giving me an error.
Conversion failed when converting the nvarchar value '11xx' to data type int.

i have in table employeecode nvarchar(500)

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 11:47:02

Problem is here:


set @Where=@Where+' EmployeeCode ='+str(@EmployeeCode )+' And'

STR() function expects to be given a FLOAT, but you are passing it a STRING.

@EmployeeCode is already nvarchar(500) datatype, so can be used in the string expression "as-is", i.e.


set @Where=@Where+' EmployeeCode ='+ @EmployeeCode +' And'

Having said that I would expected the message to say:

Error converting data type nvarchar to float.

rather than to int, so maybe it is something else.

What are you trying to do in your Procedure? The dynamic SQL you are building looks unnecessary - and looks as though it will defeat the object of using a stored procedure - instead of just having Execute Permissions on the stored procedure the user will actually have to have permissions on the underlying table, and you run the risk of SQL injection too.

profiler
Starting Member

7 Posts

Posted - 2010-01-16 : 11:59:09

@Kristen

Thanx for reply.

if i do like that i am getting this error. Conversion failed when converting the nvarchar value '11ap' to data type int.
so i changed to str()

i have some criterias in my procedure . so i need to work with tsql. its just a sample i have given where i am getting that error.

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 12:33:34

Did you check the output from your

print (@Query+@Where)

line? That will show you that you don't have any quotes around the "11ap" value.

Try running the SQL from your PRINT statement if you get an error, that will give you a better error message. (And comment out the EXEC line until you have got the SQL from your PRINT line working properly).

I still don't see why you are using dynamic SQL for this job though - you are making it much harder than it needs to be!

profiler
Starting Member

7 Posts

Posted - 2010-01-16 : 12:40:53

Hello Kristen,

Thanks.. some body replied me on SqlTeam.com

to work and follow these things.
It has worked fine for me now.

I have many joins and many criteria conditions in my query.

The code which i gave here is just a sample. i quoted only the lines where i got that error.

ALTER PROCEDURE GetEmployee
(
@EmployeeCode nvarchar(500),
@debug char(1) = 'N'
)
AS
set nocount on;

Declare @Query nvarchar(4000), @param_list nvarchar(4000);

select @Query = N'select * from Employee where 1 = 1', @param_list = N'@EmployeeCode nvarchar(500)';

if @EmployeeCode <> '-1'
set @Query = @Query + N' and EmployeeCode = @EmployeeCode )

if @debug in ('y', 'y')
print (@Query);

begin try
exec sp_executesql @Query, @param_list, @EmployeeCode;
end try
begin catch
select error_number() as error_number, error_message() as error_message;
end catch
GO

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 12:52:54

Cool Glad you've got it sorted out.

Still don't think doing this sort of Dynamic SQL from within SQL is a good idea. You'd be better to do the string manipulation from your application language to make a suitable sp_executesql statement.

Or if the issue is that your EmployeeCode is a list, separated with ",", there are MUCH better ways of doing this rather than using dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources