June 22, 2008 at 8:46 pm
this is my code for stored procedure,
ALTER PROCEDURE [dbo].[tray_history]
(
@history as varchar(15),
@userid as varchar(15),
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @strSQL nvarchar(1000)
--Set @strSQL='SELECT * from tbl_history'
Set @strSQL='SELECT * from history_view'
END
if(@history)<>''
Begin
Set @strSQL=@strSQL + ' Where'
Set @strSQL=@strSQL + ' Phone_Number like ''%' + (@history) + '%'''
end
if(@userid)<>'2'
Begin
set @strsql=' SELECT *from history_view Where Send_by = '' &(@userid)'''
end
print @strSQL
Exec sp_executesql @strSQL
and when i try to execute this in my application, am getting the error of "Error converting data type varchar to bigint."
dcd.CommandType = Data.CommandType.StoredProcedure
prm = New SqlParameter("@userid", Data.SqlDbType.VarChar, 15)
dcd.Parameters.Add(prm)
dcd.Parameters("@userid").Value = Session("userlogin")
pls advice on how can i fix this error?thanks a bunch
June 23, 2008 at 3:13 am
June 23, 2008 at 5:33 am
June 23, 2008 at 11:01 am
Set @strSQL =
'SELECT * from history_view WHERE ' Phone_Number like ''%' + (@history) + '%'''
This can only work if the select statement retrieves ONLY ONE record and for that record, ONLY ONE field. Since you are using the LIKE clause with the % wild card, eventually you are bound to retrieve more than one record. If it does not fail now, eventually it will.
June 24, 2008 at 2:45 am
thnks guys...got a pic of it already and i managed to solve the error with the below alteration done to the stored procedure..thnks again!!
ALTER PROCEDURE [dbo].[tray_history]
(
@history as varchar(15),
@userid as bigint
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @strSQL nvarchar(1000)
if(@userid)<> '2' and (@userid)<> '46'
Begin
if(@history)<>''
begin
set @strSQL='SELECT * FROM history_view WHERE Phone_Number LIKE ''%' + (@history) + '%'' AND Send_By = ' + CAST(@userid as nvarchar(50))
end
else
begin
set @strsql='SELECT * from history_view Where Send_by = ' + CAST(@userid as nvarchar(50))
end
end
else
begin
if(@history)<>''
begin
set @strSQL='SELECT * FROM history_view WHERE Phone_Number LIKE ''%' + (@history) + '%'''
end
else
begin
set @strsql='SELECT * from history_view'
end
end
June 25, 2008 at 12:27 am
J (6/23/2008)
Set @strSQL ='SELECT * from history_view WHERE ' Phone_Number like ''%' + (@history) + '%'''
This can only work if the select statement retrieves ONLY ONE record and for that record, ONLY ONE field. Since you are using the LIKE clause with the % wild card, eventually you are bound to retrieve more than one record. If it does not fail now, eventually it will.
He's building a SQL string and then executing it.. it can return x rows/cols without any issue.
June 25, 2008 at 12:30 am
malar_jay (6/24/2008)
thnks guys...got a pic of it already and i managed to solve the error with the below alteration done to the stored procedure..thnks again!!ALTER PROCEDURE [dbo].[tray_history]
(
@history as varchar(15),
@userid as bigint
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @strSQL nvarchar(1000)
if(@userid)<> '2' and (@userid)<> '46'
Begin
if(@history)<>''
begin
set @strSQL='SELECT * FROM history_view WHERE Phone_Number LIKE ''%' + (@history) + '%'' AND Send_By = ' + CAST(@userid as nvarchar(50))
end
else
begin
set @strsql='SELECT * from history_view Where Send_by = ' + CAST(@userid as nvarchar(50))
end
end
else
begin
if(@history)<>''
begin
set @strSQL='SELECT * FROM history_view WHERE Phone_Number LIKE ''%' + (@history) + '%'''
end
else
begin
set @strsql='SELECT * from history_view'
end
end
You know.. you might have overcomplicated this?
It all depends what data type Send_By actually is.
If it's a varchar then your original method was fine.. no CAST needed.. however you do need to surround @user-id with single quotes in the where clause..
If it's a bigint, then as above @userid should also be a bigint, but no CAST or single quotes are required.
Also I see no need, in your restructured code, to use a @strSQL variable. May as well use neat select statements in each if/else/end block?
June 25, 2008 at 8:01 am
My mistake. I stand corrected.
Regards
June 25, 2008 at 8:14 am
Please don't write code like this unless you want to be hacked. this proc is a prime target for a sql injection attack.
for dynamic search conditions, you will benefit from reading this:
http://sommarskog.se/dyn-search.html
---------------------------------------
elsasoft.org
July 16, 2008 at 8:55 pm
ok noted!thnks guys!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply